1

How to convert this SQL to Django ORM

SELECT 
  `table1`.*,
  `tabl2`.*
FROM `table1`
INNER JOIN `table2` ON (`table1`.`table2_id` = `table2`.`id`)
INNER JOIN
(SELECT
   table2_id,
   MAX(date) AS max_date
FROM table1
WHERE date <= CURRENT_DATE
GROUP BY table2_id) grouped
  ON table1.table2_id = grouped.table2_id
     AND table1.date = grouped.max_date
WHERE `table1`.`table2_id` IN (73, 74)
ORDER BY `table1`.`date` ASC

I tried with annotate, but unsuccessful

Dauren Chapaev
  • 93
  • 2
  • 9

1 Answers1

1

Simply, you need to have the models defined for the tables i.e. 'table1' and 'table2' - the point of using ORM is define your tables as models (once for all) and make queries with ease.

If you have schema defined, translate it into Django models (all the fields). And, if you already have the DB setup, use the inspectdb command to auto-generate the models from a DB.

You can generate models for specific DB tables - inspectdb.

inspectdb

django-admin inspectdb [table [table ...]]

Introspects the database tables in the database pointed-to by the NAME setting and outputs a Django model module (a models.py file) to standard output. You may choose what tables to inspect by passing their names as arguments.

Use this if you have a legacy database with which you’d like to use Django. The script will inspect the database and create a model for each table within it.

As you might expect, the created models will have an attribute for every field in the table. Note that inspectdb has a few special cases in its field-name output:

  • If inspectdb cannot map a column’s type to a model field type, it’ll use TextField and will insert the Python comment 'This field type is a guess.' next to the field in the generated model.

  • If the database column name is a Python reserved word (such as 'pass', 'class' or 'for'), inspectdb will append '_field' to the attribute name. For example, if a table has a column 'for', the generated model will have a field 'for_field', with the db_column attribute set to 'for'. inspectdb will insert the Python comment 'Field renamed because it was a Python reserved word.' next to the field.

But you must manually verify the auto-generated models:

This feature is meant as a shortcut, not as definitive model generation. After you run it, you’ll want to look over the generated models yourself to make customizations. In particular, you’ll need to rearrange models’ order, so that models that refer to other models are ordered properly.

Nabeel Ahmed
  • 18,328
  • 4
  • 58
  • 63