How to write join conditions in django query

Question

I want to know how to write a join condition in django query for the following SQL query:

SELECT
    s.name,
    l.name,
    a.name,
    a.asset_code,
    c.name,
    ad.name,
    a.model_no,
    a.serial_no
FROM
    asset_mgmt_asset_assignment_employee ae,
    asset_mgmt_asset a,
    asset_mgmt_department ad,
    asset_mgmt_employee e,
    asset_mgmt_sublocation s,
    asset_mgmt_location l,
    asset_mgmt_asset_category c
WHERE
    ae.asset_id = a.id AND
    ae.department_id = ad.id AND
    ae.employee_id = e.id AND
    ad.location_id = l.id AND
    l.Sublocation_id = s.id AND
    c.id = a.asset_category_id AND
    s.id = 2;

How can we mix all these tables in a django query?

Answer 1

This query does not translate straightforwardly into Django. Django queries produce objects, not tuples. You need to think in terms of objects and their relationships instead of tuples and relations.

Of course, that's assuming an ORM is appropriate for your use case in the first place. If you find yourself writing lots of queries like this, it might not be. ORMs are best if you want to keep all of your business logic in high-level application code and treat the database as a persistence layer for objects. They aren't so good at expressing arbitrary queries such as this one. Personally, I don't really like ORMs. I would prefer to keep most relational logic in views, and occasionally query those views from application code (with very simple queries). But this is getting into subjective opinion. ORMs do have value and can be useful if you want to follow an MVC-like framework, while a more relational solution will generally produce less layered code.