Could you give an example of something that's hard to compose with SQL? I'm sure ORMs have their advantages, I just want to understand how much benefit I could get from using one.
Could you give an example of something that's hard to compose with SQL? I'm sure ORMs have their advantages, I just want to understand how much benefit I could get from using one.
Book.objects.filter(author__address__country='GB')
Rather than having to manage the joins with the author and address tables yourself.For example, in SQL if I wanted to return records where an records could be found in both tables, I would use an inner join, whereas if I wanted to return all records from 'author' and any related information from 'address' (and return NULL if a suitable address entry couldn't be found) I could use a left join. Does the ORM you have in mind give you that flexibility?
It's probably worth mentioning that some SQL tools will suggest fields that can be joined on, if this is a concern.
published_posts = Post.objects.filter(publish_date__gte=now())
post_count = published_posts.count()
You can't just append a COUNT to an SQL query, you'd have to write two queries, or one query that returned both. The above code produces two different queries, so it's much more composable.I use it for tacking on extra filtering terms as well:
if category:
published_posts = published_posts.filter(category=category)SELECT published_posts, COUNT(published_posts) FROM Table1 WHERE publish_date > '2017-02-20' GROUP BY published_posts
Post.objects.prefetch_related('author__company')
Often I want to avoid joins on big tables but still need to prefetch related fields on the objects I'm fetching. Example above fetches all the authors for the returned post, and then fetches all their companies. That's two additional fetches I don't have to write.