Django query gives "SELECT DISTINCT ON expressions must match initial ORDER BY expressions" error even specified the same order

Question

VirtualMachine.objects.order_by('pool__customer').distinct('pool__customer')

Been thru this ticket already

also tried asking ChatGPT (lol), the answer doesn't have .all() but gave the same error, wouldn't think it made a difference anyway cos .order_by('pool__customer') should overwrite it already

Can you explain *what* you want to do (not *how* you want to do it) and share the relevant models? — Willem Van Onsem, Mar 16 '23 at 22:27
@WillemVanOnsem thanks I didn't know going thru FK tables would have implications thought that if I only specify the same field in order_by would work. But I got my answer now. — James Lin, Mar 16 '23 at 22:31
It might help to fix the problem in another way. Here for each customer you seem to return *a* virtual machine (although what is not specified and thus can differ between two queries). — Willem Van Onsem, Mar 16 '23 at 22:32
@WillemVanOnsem yeah that would work as well haha but that is a N+1 problem — James Lin, Mar 16 '23 at 22:34
not with `Customer.objects.prefetch_related('virtualmachine_set')` or something similar, then it will make one query for the `Customer`s, and a second for all virtual machines for these customers in a single query. — Willem Van Onsem, Mar 16 '23 at 22:36
yeah my problem is I need to filter the virtualmachine.updated_at in a range — James Lin, Mar 16 '23 at 22:37

score 2 · Answer 1 · edited Mar 17 '23 at 11:43

Based on your comment, you want, for each Customer, the VirtualMachines that are in a certain timerange.

You can do this with a Prefetch object ^[Django-doc]:

from django.db.models import Prefetch

Customer.objects.prefetch_related(
    Prefetch(
        'virtualmachine_set',
        VirtualMachine.objects.filter(
            datetime_field__range=('2022-11-25', '2023-3-25')
        ),
    )
)

This will fetch only VirtualMachines where a field (here datetime_field is within a certain range).

score 0 · Answer 2 · answered Mar 16 '23 at 22:29

ah... once I printed the raw query then I realised it generated different order by

SELECT DISTINCT ON ("customerpool"."customer_id")
"virtualmachine"."id",
"virtualmachine"."name",
"virtualmachine"."uuid",
...
FROM   "truebill_virtualmachine"
       LEFT OUTER JOIN "customerpool"
                    ON ( "virtualmachine"."pool_id" =
                         "customerpool"."pool" )
       LEFT OUTER JOIN "customer"
                    ON ( "customerpool"."customer_id" =
                         "customer"."code" )
ORDER  BY "customer"."code" ASC

so once I changed the query to this and it worked.

VirtualMachine.objects.distinct('pool__customer').order_by('pool__customer_id')

yeah the `Customer` model also has default ordering, but i didn't realise it was talking about this model — James Lin, Mar 16 '23 at 22:33

score 0 · Answer 3 · answered Mar 17 '23 at 10:33

0

if you want to get distinct value from pool__customer field in VirtualMachine model, you can use

VirtualMachine.objects.values_list('pool__customer', flat=True).distinct()

You can read more in Django values_list vs values

answered Mar 17 '23 at 10:33

cahya faisal reza

11
3

Django query gives "SELECT DISTINCT ON expressions must match initial ORDER BY expressions" error even specified the same order

3 Answers3