3 ms·

>"However, the way you write a lot of these things in SQL is substantially more verbose and harder to compose." Could you give an example of something that's h

>"However, the way you write a lot of these things in SQL is substantially more verbose and harder to compose." 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.

MatthewWilkes 10y ago
Queries across joins are the simple example. The ORM takes care of the join bit for you so you can write: Book.objects.filter(author__address__country='GB') Rather than having to manage the joins with the author and address tables yourself.
- ZenoArrow 10y ago
  With that example, do you have control over how the records are joined? 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.
  - jsmeaton 10y ago
    Django will generate a LEFT JOIN for nullable keys, and an INNER JOIN for non nullable keys. The lack of control over joins and conditions is a weakness of the ORM, but Django generally does the right thing. Of course, you can do an `__isnull` on the relation to create a pseudo inner join from the generated LEFT JOIN, but admit it's not the same.
rowanseymour 10y ago
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.
- ZenoArrow 10y ago
  It's also simple to prefetch in SQL. For example, if you wanted to populate a temp table with the results of a select query in SQL Server, this can be a one line change: http://stackoverflow.com/a/12323794 http://stackoverflow.com/a/12323794
StavrosK 10y ago
I like this: 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)
- ZenoArrow 10y ago
  This is simple in SQL. There are multiple ways of doing it. For example, using a GROUP BY... SELECT published_posts, COUNT(published_posts) FROM Table1 WHERE publish_date > '2017-02-20' GROUP BY published_posts
  - StavrosK 10y ago
    The above query generates SQL in the end. My point was that SQL is not composable, whereas the above is.

MatthewWilkes 10y ago

Queries across joins are the simple example. The ORM takes care of the join bit for you so you can write: Book.objects.filter(author__address__country='GB') Rather than having to manage the joins with the author and address tables yourself.

ZenoArrow 10y ago
With that example, do you have control over how the records are joined? 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.
- jsmeaton 10y ago
  Django will generate a LEFT JOIN for nullable keys, and an INNER JOIN for non nullable keys. The lack of control over joins and conditions is a weakness of the ORM, but Django generally does the right thing. Of course, you can do an `__isnull` on the relation to create a pseudo inner join from the generated LEFT JOIN, but admit it's not the same.

rowanseymour 10y ago

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.

ZenoArrow 10y ago
It's also simple to prefetch in SQL. For example, if you wanted to populate a temp table with the results of a select query in SQL Server, this can be a one line change: http://stackoverflow.com/a/12323794 http://stackoverflow.com/a/12323794

StavrosK 10y ago

I like this: 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)

ZenoArrow 10y ago
This is simple in SQL. There are multiple ways of doing it. For example, using a GROUP BY... SELECT published_posts, COUNT(published_posts) FROM Table1 WHERE publish_date > '2017-02-20' GROUP BY published_posts
- StavrosK 10y ago
  The above query generates SQL in the end. My point was that SQL is not composable, whereas the above is.