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,
    )

[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

[Горобец Дмитрий]

Andrew, your solution works great for one brand. But I have more than one.

суббота, 20 мая 2017 г., 0:20:18 UTC+5 пользователь Andrew Beales написал:

[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')

[Горобец Дмитрий]

Thank you, Todor. I'll try.

вторник, 23 мая 2017 г., 3:27:00 UTC+5 пользователь Todor Velichkov написал:

[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

[Горобец Дмитрий]

Score field is on Brand model.

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