6 ms·

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

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.

jsmeaton 10y ago
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.
- mozumder 10y ago
  Aren't SQL subqueries pretty much standard across all databases anyways? Why would writing them in Django's ORM be more compatible?
  - jsmeaton 10y ago
    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.
Alex3917 10y ago
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.
Waterluvian 10y ago
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.
- metaphorm 10y ago
  https://docs.djangoproject.com/en/1.10/topics/db/sql/#django.db.models.Manager.raw 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.
  - jsmeaton 10y ago
    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.
- Alex3917 10y ago
  > 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.
- symlinkk 10y ago
  > Just a string of SQL that I compose with Python string formatting sounds like an SQL injection waiting to happen
ksri 10y ago
Take a look at JinjaSQL - https://github.com/hashedin/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.
- bradmwalker 10y ago
  http://docs.sqlalchemy.org/en/latest/core/ http://docs.sqlalchemy.org/en/latest/core/
andybak 10y ago
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.
- brianwawok 10y ago
  It is my favorite ORM out there. Maybe a little too easy to do accidental joins, but you can do that in SQL too.
danpalmer 10y ago
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.

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.

jsmeaton 10y ago

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.

mozumder 10y ago
Aren't SQL subqueries pretty much standard across all databases anyways? Why would writing them in Django's ORM be more compatible?
- jsmeaton 10y ago
  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.

Alex3917 10y ago

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.

Waterluvian 10y ago

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.

metaphorm 10y ago
https://docs.djangoproject.com/en/1.10/topics/db/sql/#django.db.models.Manager.raw 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.
- jsmeaton 10y ago
  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.
Alex3917 10y ago
> 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.
symlinkk 10y ago
> Just a string of SQL that I compose with Python string formatting sounds like an SQL injection waiting to happen

ksri 10y ago

Take a look at JinjaSQL - https://github.com/hashedin/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.

bradmwalker 10y ago
http://docs.sqlalchemy.org/en/latest/core/ http://docs.sqlalchemy.org/en/latest/core/

andybak 10y ago

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.

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

danpalmer 10y ago

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.