4 ms·

What ORM doesn't handle joins, order by and group by? Most should even support subqueries and case clauses.

specialist 8y ago
Also, using HQL (or equiv) defeats the purpose of using an ORM. Just use SQL. --- Imagine you're troubleshooting your query, mapping. It's got some modest joins. So you capture the emitted SQL. Then you fuss with that SQL to make it something reasonable. Once that SQL is working, you then wrestle with the ORM to try to coerce it to re-emit your desired SQL. This is also called reverse engineering, pushing rope, fighting the 800lb angry gorilla sitting between you and your work. Eventually you'll figure out you should just use SQL, whatever dialect your backend supports.
- ris 8y ago
  Um... how many ORMs do you have experience with? I suggest you look at the mentioned sqlalchemy core's ability to map more or less directly to SQL with reduced syntactic ugliness.
  - spacemanmatt 8y ago
    There is a fruitful discussion that can be had on helpful ORMs vs harmful ORMs, and it usually ends up describing a category of marshalling helpers that don't quite form a regular ORM as the ideal situation. My take-home on the whole thing is, you need something that makes working with relations in your code easy but you don't need something attempting a ridiculous abstraction like "relations are objects."
    - deleted 8y ago
      [deleted]
  - specialist 8y ago
    Just so I understand you: You're saying you've never captured the generated SQL, debugged and tweaked it, and then backported that SQL to your ORM obfuscation layer?
- imtringued 8y ago
  The purpose of an ORM is to map database query results to objects. You still have to write the queries yourself. I will agree that HQL has flaws but not the same ones that you admit. It is string based which means it is cumbersome to use, dynamic column filtering is not possible, entering parameters requires 3x dupliation of the. variable name and it isn't type checked at compile time. But frameworks like grails (which is built on top of hibernate and criteria) or LINQ have solved these problems. My biggest performance bottlenecks are in batch insert/update performance for which most ORMs already generate optimal queries that don't involve database specific features like "unnest(col1, col2)". But even that could be solved trivially by exposing a simple batch insert/update api to have both the convenience of an ORM and the performance of db specific optimisations.

Also, using HQL (or equiv) defeats the purpose of using an ORM. Just use SQL. --- Imagine you're troubleshooting your query, mapping. It's got some modest joins. So you capture the emitted SQL. Then you fuss with that SQL to make it something reasonable. Once that SQL is working, you then wrestle with the ORM to try to coerce it to re-emit your desired SQL. This is also called reverse engineering, pushing rope, fighting the 800lb angry gorilla sitting between you and your work. Eventually you'll figure out you should just use SQL, whatever dialect your backend supports.

ris 8y ago
Um... how many ORMs do you have experience with? I suggest you look at the mentioned sqlalchemy core's ability to map more or less directly to SQL with reduced syntactic ugliness.
- spacemanmatt 8y ago
  There is a fruitful discussion that can be had on helpful ORMs vs harmful ORMs, and it usually ends up describing a category of marshalling helpers that don't quite form a regular ORM as the ideal situation. My take-home on the whole thing is, you need something that makes working with relations in your code easy but you don't need something attempting a ridiculous abstraction like "relations are objects."
  - deleted 8y ago
    [deleted]
- specialist 8y ago
  Just so I understand you: You're saying you've never captured the generated SQL, debugged and tweaked it, and then backported that SQL to your ORM obfuscation layer?
imtringued 8y ago
The purpose of an ORM is to map database query results to objects. You still have to write the queries yourself. I will agree that HQL has flaws but not the same ones that you admit. It is string based which means it is cumbersome to use, dynamic column filtering is not possible, entering parameters requires 3x dupliation of the. variable name and it isn't type checked at compile time. But frameworks like grails (which is built on top of hibernate and criteria) or LINQ have solved these problems. My biggest performance bottlenecks are in batch insert/update performance for which most ORMs already generate optimal queries that don't involve database specific features like "unnest(col1, col2)". But even that could be solved trivially by exposing a simple batch insert/update api to have both the convenience of an ORM and the performance of db specific optimisations.