5 ms·

So, in my example, you might use the ORM, perform three inserts (one for each table) in a transaction. If any fail, rollback. However, in the select, where'd y

So, in my example, you might use the ORM, perform three inserts (one for each table) in a transaction. If any fail, rollback. However, in the select, where'd you'd have to join all three tables, you might use SQL, yes? I'm just curious how people are using ORMs vs SQL, so sorry for the questions.

aidos 11y ago
Not the parent, but when I'm using SA (sqlalchemy) I tend to just use the ORM for everything. If I needed something to be super performant I might get closer to the raw SQL – though generally I find development so much faster when SA handles everything for me. If you're using a different ORM then things are probably going to be different. I've worked with a few in various languages and SA is the only one I've found that allows me to express any complex query directly via the ORM. I've heard there are some edge cases where you need to drop down a layer but I haven't run into them yet.
collyw 11y ago
I would use the ORM for basic queries, where the conditions were not too complex - its easier than join syntax. If you need anything like subqueries, or extra conditions on the join caluse it gets tricky (in the django ORM at least), and SQL is easier. Right now I am working with a suboptimal database design, where I need two nested subqueries to get the most recent event in a related row. I changed them to joins then to (indexed) temporary tables, to try and improve MySQl's performance. It worked to a degree. I doubt that would be possible in an ORM as it is working at the databse level rather than a hgher abstraction.
collyw 11y ago
I would use the ORM for basic queries, where the conditions were not too complex - its easier than join syntax. If you need anything like subqueries, or extra conditions on the join caluse it gets tricky (in the django ORM at least), and SQL is easier. Right now I am working with a suboptimal database design, where I need two nested subqueries to get the most recent event in a related row. I changed them to joins then to (indexed) temporary tables, to try and improve MySQl's performance. It worked to a degree. I doubt that would be possible in an ORM as it is working at the databse level rather than a hgher abstraction.

aidos 11y ago

Not the parent, but when I'm using SA (sqlalchemy) I tend to just use the ORM for everything. If I needed something to be super performant I might get closer to the raw SQL – though generally I find development so much faster when SA handles everything for me. If you're using a different ORM then things are probably going to be different. I've worked with a few in various languages and SA is the only one I've found that allows me to express any complex query directly via the ORM. I've heard there are some edge cases where you need to drop down a layer but I haven't run into them yet.

collyw 11y ago

I would use the ORM for basic queries, where the conditions were not too complex - its easier than join syntax. If you need anything like subqueries, or extra conditions on the join caluse it gets tricky (in the django ORM at least), and SQL is easier. Right now I am working with a suboptimal database design, where I need two nested subqueries to get the most recent event in a related row. I changed them to joins then to (indexed) temporary tables, to try and improve MySQl's performance. It worked to a degree. I doubt that would be possible in an ORM as it is working at the databse level rather than a hgher abstraction.

collyw 11y ago