Quantcast

Building correct queryset

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Building correct queryset

Горобец Дмитрий
Hello. I have these two models. I have to show 4 or less products on a page for each brand ordered by score. Please, help construct correct queryset.


class Brand(models.Model):
title = models.CharField(
verbose_name='Название бренда',
max_length=64,
unique=True,
db_index=True,
error_messages={
'unique': 'Бренд с таким именем уже существует.'
}
)

slug = AutoSlugField(
verbose_name='Адрес страницы бренда',
populate_from='title',
editable=True,
unique=True,
slugify=custom_slugify,
db_index=True
)

owner = models.OneToOneField(
to=settings.AUTH_USER_MODEL,
verbose_name='Владелец',
on_delete=models.PROTECT,
)

    score = models.DecimalField(
verbose_name='Рейтинг бренда',
blank=True,
null=True,
max_digits=19,
decimal_places=18,
)


class Product(models.Model):
title = models.CharField(
verbose_name='Название изделия',
max_length=255,
db_index=True,
)

slug = AutoSlugField(
verbose_name='Адрес страницы изделия',
populate_from='title',
editable=False,
unique_with='brand',
slugify=custom_slugify,
db_index=True,
)

brand = models.ForeignKey(
to=Brand,
verbose_name='Бренд',
related_name='products',
on_delete=models.CASCADE,
)

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/9ec086f9-c91f-4a8e-b474-9b1d89d1ef2f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Building correct queryset

Andrew Beales
brand = Brand.objects.get(title='mybrand')
products
= Product.objects.filter(brand=brand).order_by('-score')[:4]

...gets you the top 4 products in order for the brand

On Friday, May 19, 2017 at 10:32:49 AM UTC+1, Горобец Дмитрий wrote:
Hello. I have these two models. I have to show 4 or less products on a page for each brand ordered by score. Please, help construct correct queryset.


class Brand(models.Model):
title = models.CharField(
verbose_name='Название бренда',
max_length=64,
unique=True,
db_index=True,
error_messages={
'unique': 'Бренд с таким именем уже существует.'
}
)

slug = AutoSlugField(
verbose_name='Адрес страницы бренда',
populate_from='title',
editable=True,
unique=True,
slugify=custom_slugify,
db_index=True
)

owner = models.OneToOneField(
to=settings.AUTH_USER_MODEL,
verbose_name='Владелец',
on_delete=models.PROTECT,
)

    score = models.DecimalField(
verbose_name='Рейтинг бренда',
blank=True,
null=True,
max_digits=19,
decimal_places=18,
)


class Product(models.Model):
title = models.CharField(
verbose_name='Название изделия',
max_length=255,
db_index=True,
)

slug = AutoSlugField(
verbose_name='Адрес страницы изделия',
populate_from='title',
editable=False,
unique_with='brand',
slugify=custom_slugify,
db_index=True,
)

brand = models.ForeignKey(
to=Brand,
verbose_name='Бренд',
related_name='products',
on_delete=models.CASCADE,
)

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/c8d4f47e-10b8-4ddf-95f5-23f8c4d05cf7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Building correct queryset

Горобец Дмитрий
Andrew, your solution works great for one brand. But I have more than one.

суббота, 20 мая 2017 г., 0:20:18 UTC+5 пользователь Andrew Beales написал:
brand = Brand.objects.get(title='mybrand')
products
= Product.objects.filter(brand=brand).order_by('-score')[:4]

...gets you the top 4 products in order for the brand

On Friday, May 19, 2017 at 10:32:49 AM UTC+1, Горобец Дмитрий wrote:
Hello. I have these two models. I have to show 4 or less products on a page for each brand ordered by score. Please, help construct correct queryset.


class Brand(models.Model):
title = models.CharField(
verbose_name='Название бренда',
max_length=64,
unique=True,
db_index=True,
error_messages={
'unique': 'Бренд с таким именем уже существует.'
}
)

slug = AutoSlugField(
verbose_name='Адрес страницы бренда',
populate_from='title',
editable=True,
unique=True,
slugify=custom_slugify,
db_index=True
)

owner = models.OneToOneField(
to=settings.AUTH_USER_MODEL,
verbose_name='Владелец',
on_delete=models.PROTECT,
)

    score = models.DecimalField(
verbose_name='Рейтинг бренда',
blank=True,
null=True,
max_digits=19,
decimal_places=18,
)


class Product(models.Model):
title = models.CharField(
verbose_name='Название изделия',
max_length=255,
db_index=True,
)

slug = AutoSlugField(
verbose_name='Адрес страницы изделия',
populate_from='title',
editable=False,
unique_with='brand',
slugify=custom_slugify,
db_index=True,
)

brand = models.ForeignKey(
to=Brand,
verbose_name='Бренд',
related_name='products',
on_delete=models.CASCADE,
)

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/8d486975-7f0d-40e1-82e3-64bc5259b518%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Building correct queryset

Todor Velichkov
In reply to this post by Горобец Дмитрий
Hello, Дмитрий,
you can try this one, but w/o further optimizations it may be a very slow query.

qs = Product.objects.filter(
   
#Where score is greater or equal
   
#to the 4th max score from its group
    score__gte
=Subquery(
       
(Product.objects
           
.filter(brand=OuterRef('brand'))
           
.values('score')
           
.order_by('-score')[3:4]
       
)
   
)
).order_by('-score')




 

On Friday, May 19, 2017 at 12:32:49 PM UTC+3, Горобец Дмитрий wrote:
Hello. I have these two models. I have to show 4 or less products on a page for each brand ordered by score. Please, help construct correct queryset.


class Brand(models.Model):
title = models.CharField(
verbose_name='Название бренда',
max_length=64,
unique=True,
db_index=True,
error_messages={
'unique': 'Бренд с таким именем уже существует.'
}
)

slug = AutoSlugField(
verbose_name='Адрес страницы бренда',
populate_from='title',
editable=True,
unique=True,
slugify=custom_slugify,
db_index=True
)

owner = models.OneToOneField(
to=settings.AUTH_USER_MODEL,
verbose_name='Владелец',
on_delete=models.PROTECT,
)

    score = models.DecimalField(
verbose_name='Рейтинг бренда',
blank=True,
null=True,
max_digits=19,
decimal_places=18,
)


class Product(models.Model):
title = models.CharField(
verbose_name='Название изделия',
max_length=255,
db_index=True,
)

slug = AutoSlugField(
verbose_name='Адрес страницы изделия',
populate_from='title',
editable=False,
unique_with='brand',
slugify=custom_slugify,
db_index=True,
)

brand = models.ForeignKey(
to=Brand,
verbose_name='Бренд',
related_name='products',
on_delete=models.CASCADE,
)

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/16251879-81fd-4f25-83b7-5f6c920e3f8a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Building correct queryset

Горобец Дмитрий
Thank you, Todor. I'll try.

вторник, 23 мая 2017 г., 3:27:00 UTC+5 пользователь Todor Velichkov написал:
Hello, Дмитрий,
you can try this one, but w/o further optimizations it may be a very slow query.

qs = Product.objects.filter(
   
#Where score is greater or equal
   
#to the 4th max score from its group
    score__gte
=Subquery(
       
(Product.objects
           
.filter(brand=OuterRef('brand'))
           
.values('score')
           
.order_by('-score')[3:4]
       
)
   
)
).order_by('-score')




 

On Friday, May 19, 2017 at 12:32:49 PM UTC+3, Горобец Дмитрий wrote:
Hello. I have these two models. I have to show 4 or less products on a page for each brand ordered by score. Please, help construct correct queryset.


class Brand(models.Model):
title = models.CharField(
verbose_name='Название бренда',
max_length=64,
unique=True,
db_index=True,
error_messages={
'unique': 'Бренд с таким именем уже существует.'
}
)

slug = AutoSlugField(
verbose_name='Адрес страницы бренда',
populate_from='title',
editable=True,
unique=True,
slugify=custom_slugify,
db_index=True
)

owner = models.OneToOneField(
to=settings.AUTH_USER_MODEL,
verbose_name='Владелец',
on_delete=models.PROTECT,
)

    score = models.DecimalField(
verbose_name='Рейтинг бренда',
blank=True,
null=True,
max_digits=19,
decimal_places=18,
)


class Product(models.Model):
title = models.CharField(
verbose_name='Название изделия',
max_length=255,
db_index=True,
)

slug = AutoSlugField(
verbose_name='Адрес страницы изделия',
populate_from='title',
editable=False,
unique_with='brand',
slugify=custom_slugify,
db_index=True,
)

brand = models.ForeignKey(
to=Brand,
verbose_name='Бренд',
related_name='products',
on_delete=models.CASCADE,
)

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/adfc3475-6bd9-43a2-9891-ed23d67d3b19%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Building correct queryset

Melvyn Sopacua
In reply to this post by Todor Velichkov

On Monday 22 May 2017 15:26:59 Todor Velichkov wrote:

> Hello, Дмитрий,

> you can try this one, but w/o further optimizations it may be a very

> slow query.

>

> qs = Product.objects.filter(

> #Where score is greater or equal

> #to the 4th max score from its group

> score__gte=Subquery(

> (Product.objects

> .filter(brand=OuterRef('brand'))

> .values('score')

> .order_by('-score')[3:4]

> )

> )

> ).order_by('-score')

 

Yeah, that's how I read it too. But the code says score is on Brand model, not Product. Which is correct?

--

Melvyn Sopacua

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/1888330.h8aJiHRcfu%40devstation.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Building correct queryset

Горобец Дмитрий
Score field is on Brand model.

пятница, 26 мая 2017 г., 3:19:56 UTC+5 пользователь Melvyn Sopacua написал:

On Monday 22 May 2017 15:26:59 Todor Velichkov wrote:

> Hello, Дмитрий,

> you can try this one, but w/o further optimizations it may be a very

> slow query.

>

> qs = Product.objects.filter(

> #Where score is greater or equal

> #to the 4th max score from its group

> score__gte=Subquery(

> (Product.objects

> .filter(brand=OuterRef('brand'))

> .values('score')

> .order_by('-score')[3:4]

> )

> )

> ).order_by('-score')

 

Yeah, that's how I read it too. But the code says score is on Brand model, not Product. Which is correct?

--

Melvyn Sopacua

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/5838210b-f4ea-4338-ab2c-7c6e706acae2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Loading...