Hacker News Reader

Comment by mozumder | Hacker News Reader

Subquery expressions looks like it would be just easier to write SQL, instead of going through the Django ORM. This is where SQL starts to show its expressive strength over ORMs.

View on HN

You're right, except for when you need to support multiple database backends. For projects it's usually not a concern, but for libraries it certainly is. The ORM has been slowly adding more and more complex types (Expressions) for more complex use cases, which can be mostly ignored for regular CRUD apps.

Aren't SQL subqueries pretty much standard across all databases anyways? Why would writing them in Django's ORM be more compatible?

Field names, table names, quotes or not - a few examples of things that can differ. The syntax of subqueries themselves are standard AFAIK.

Building subqueries inside Django gives the rest of Django access to them for things like aggregation or filters.

If you already know SQL but don't know any Django, then yes. But if you already know all the other Django model functionality and it's just a matter of learning the new Subquery syntax, then it's probably easier to do in the ORM.

There is definitely a benefit to querying everything the same way if you can, so that way it's easy to grep where different tables are being accessed. Cuts down on bugs, security issues, etc.

I learnt SQL before learning any ORM but the Django ORM just 'fits my brain' better. Especially when you factor in the fact that it handles 90% of common cases with a very simple syntax. In fact - due to lazy evaluation you can combine simple ORM queries and quite often you end up with subqueries behind the scenes. The Aggregate/Annotate syntax is a wonderful thing and I often find myself surprised writing queries that have some of that "Just Do What I Mean" magic.

Admittedly I rarely have to deal with the need for highly performant database code. But one could argue that performance problems are often better handled via caching or denormalisation (which really boil down to the same thing) than by clever work on the db side.

It is my favorite ORM out there. Maybe a little too easy to do accidental joins, but you can do that in SQL too.

Absolutely. The amount of "magic" Django does slowly became a real detractor for me.

The way I define queries by using a custom language in the form of keyword arguments makes me wonder why I don't just learn SQL instead?

I would much rather do something like:

  models.Foo.objects.filter(sql=my_sql_query_str)

Instead of something like:

  models.Foo.objects.filter(name='bar', parent__siblings__count__lt=3)

And no special abstraction on SQL in the form of special classes. Just a string of SQL that I compose with Python string formatting. SQL isn't scary. I don't see the need to try and hide it.

https://docs.djangoproject.com/en/1.10/topics/db/sql/#django...

you can already do this. the django ORM has a convenient and expressive API for a lot of very common types of queries, but lets you easily drop back into raw SQL when you have a high complexity query that you want to write by hand.

Raw requires a primary key to be returned so it can rebuild the model object for you. You could also use django.db.connection.cursor() directly.

> Where I assemble a SQL query string using string formatting and vanilla Python.

You can just add various filter conditions to a dictionary, and then unpack them into the filter as kwargs. That way you can still build up the dictionary using if/else blocks or however you would normally build up a SQL query string.

SQL isn't scary, but for simple queries the Django ORM usually a lot more legible if you take the time to learn the DSL.

> Just a string of SQL that I compose with Python string formatting

sounds like an SQL injection waiting to happen

Hah!! So so true.

I posted elsewhere on this thread, but take a look at JinjaSQL - https://github.com/hashedin/jinjasql. Let's you generate SQL from a string template without worrying about SQL injection.

Yep. I'm wondering if a framework would ever adopt something like Records [1].

1: https://github.com/kennethreitz/records (same author as Requests)

> The way I define queries by using a custom language in the form of keyword arguments makes me wonder why I don't just learn SQL instead?

You have to learn SQL to use the Django ORM anyways; it's required knowledge in order to be able to wield the higher level abstraction effectively. However, the way you write a lot of these things in SQL is substantially more verbose and harder to compose.

>"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.

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.

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.

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.

    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.

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

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)

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

The above query generates SQL in the end. My point was that SQL is not composable, whereas the above is.

Yea, that's true. This is the most important part most programmers forget about. You need to know SQL to be able to fully use any ORM. It doesn't mean that you have to write SQL when using an ORM.

Try giving pyramid + sqlalchemy a shot. Way less magic, everything feels very pythonic and its a very mature stack that can scale really well.

You may be interested in the `.extra(...)` call on a QuerySet in Django. It allows you to specify hand-written SQL for various clauses, including `WHERE`.

https://docs.djangoproject.com/en/1.10/ref/models/querysets/...

There's also a method (I forget the name, as I've never used it) where you write the full SQL and just tell Django which models' rows it's supposed to be returning, and it will build model instances from the returned SQL. Pretty nifty.

It is the raw() function: https://docs.djangoproject.com/en/1.10/topics/db/sql/#passin...

Take a look at JinjaSQL - https://github.com/hashedin/jinjasql. Beyond a certain complexity, Django ORM gets in the way. Constructing SQL by hand is painful and can lead to SQL injection. And that's where JinjaSQL shines.

You write your query as a string template, and then jinjasql interpolates the variables and provides the bind parameters. Makes it easy to maintain complex queries.

http://docs.sqlalchemy.org/en/latest/core/

I'm not sure I agree. For crossing the boundary from the database to Python objects, I think the ORM is far more expressive than SQL. Sure the query might not be as nice, but the query is only half of the process, there's all the serialisation/deserialisation, injection protection, assignment to Python objects, possible lazy query interfaces, etc.

Reply on news.ycombinator.com