# How to write this raw sql subquery as native django query?

**URL:** <https://forum.djangoproject.com/t/how-to-write-this-raw-sql-subquery-as-native-django-query/18355>\
**Category:** Using the ORM\
**Created:** [January 22, 2023, 7:28pm UTC](https://forum.djangoproject.com/t/how-to-write-this-raw-sql-subquery-as-native-django-query/18355 "2023-01-22T19:28:09Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

**Author:** ![cehceh](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/cehceh/32/3415_2.png) [@cehceh](https://forum.djangoproject.com/u/cehceh)\
**Post date:** [January 22, 2023, 11:34pm UTC](https://forum.djangoproject.com/t/how-to-write-this-raw-sql-subquery-as-native-django-query/18355/2 "2023-01-22T23:34:51Z")

</div>

I have worked on SQL for 3 years when I used .Net technology, And I liked SQL command, Really it’s magnificent. But I gave me chance to penetrate ORM you will get more robust and secure query by it.

> [@rabarberski](#):
>
> ```auto
> SELECT *
> FROM Loans
> INNER JOIN (
> SELECT LibAccountId as LAI, Title AS T,StartDate as SD, RemoteId AS RI, MAX(EndDate) AS max_enddate
> From Loans
> GROUP BY LibAccountId, Title, StartDate, RemoteId
> ) last_by_enddate ON
> Loans.LibAccountId = LAI
> AND Loans.Title = T
> AND Loans.StartDate = SD
> AND (Loans.RemoteId= RI OR (Loans.RemoteId IS NULL AND RI IS NULL))
> AND Loans.EndDate = last_by_enddate.max_enddate
> ORDER BY Loans.EndDate DESC
> """
> )
> 
> ```

I think this query is not difficult to be a result of ORM,  
The **Left Outer Join** is more difficult but I can make it by ORM.  
I have no ORM query for your SQL query now.  
But If you think well, and give yourself another chance to try ORM you will have benefits.  
You can use(filter, values, values\_list, select\_related, preftch\_related, annotate and aggregate with order\_by and distinct) then you get what you want .  
Try to print the ORM query to the console by (orm\_qs.query) to see the SQL equivalent,  
Give us your models and you will get what you want …  
Also when using ORM you can use(Case, When and etc) of SQL commands  
Take a look at

- [Complex SQL through Django ORM](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099)
- [Django ORM Query](https://stackoverflow.com/a/38238639/7740018)
- [Complex SQL query and Django ORM](https://stackoverflow.com/a/10674629/7740018)
- [Advanced Django ORM Queries](https://juhanajauhiainen.com/posts/advanced-django-queries)
- [Making queries | Docs](https://docs.djangoproject.com/en/3.2/topics/db/queries/)

---

_[View the full topic](https://forum.djangoproject.com/t/how-to-write-this-raw-sql-subquery-as-native-django-query/18355)._