Can't make Django raw sql working correctly

Question

I need to make inner join query like this:

SELECT * FROM accounting_supplier as s 
    LEFT JOIN  ( 
    SELECT position, supplier_id FROM accounting_supplierposition WHERE user_id = %s 
    ) as sp on sp.supplier_id = s.id 
    ORDER BY sp.position", [request.user]

Models look like:

class Supplier(models.Model):
    name = models.CharField(max_length=512)
    short_name = models.CharField(max_length=100, unique=True)

class SupplierPosition(models.Model):
    supplier = models.ForeignKey(Supplier, on_delete=models.CASCADE)
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    position = models.PositiveIntegerField()

I've tried different approaches but no one gives the result I want. There is a way described here: Django Left Outer Join but distinct does not work with MySQL, so I decided to use RAW. But it seems that ordering doesn't work correctly. If I copy/paste sql from my code to mysql command line it works fine but in Django it does make selection but no order.

Any suggestions? Thank you.

Please describe your original task/need. Sample data and desired result would also be helpful. — Ivan Starostin, Apr 13 '20 at 20:32
@IvanStarostin, thank you. Guy from habr helped to figure it out already. I used representation of user but not id. — Andrey Petrov, Apr 14 '20 at 07:08

Andrey Petrov · Accepted Answer · 2020-04-14T07:08:59.763

0

Just a small mistake: not [request.user] but [request.user.id]. I used representation of user but not id

edited Apr 14 '20 at 07:08

answered Apr 14 '20 at 07:02

Andrey Petrov

11
3

Can't make Django raw sql working correctly

1 Answers1