How to make a left join distinct selection (DB: mysql)?

Question

Django

Initial task:
there is a group of counterparties, there are users. Each user has his own sorting of counterparties.

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

You need to get all counterparties sorted by position. If there is no position, then getting the counterparty is all the same. In general sql:

SELECT * FROM accounting_supplier as s 
LEFT JOIN  ( SELECT * FROM accounting_supplierposition WHERE user_id = 1 ) as sp 
on sp.supplier_id = s.id;

Found a similar question on stackoverflow:
https://stackoverflow.com/questions/38060232/djang...

Did the same but the distinct doesn't work. Tried in raw:

objects.raw("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])

But for some reason sorting does not work. If I make a query directly to the database, everything is ok. In Dzhang, the same sorting is obtained for all users.

Answer 1

objects.raw("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.id])

You substitute the user's repr, but you need his id.