4 ms·

I struggle with lack of experience with SQL by always being told that I should always use an ORM or I would regret it in the future when I would change database

• btilly 6y ago
  If you're writing a CRUD application, an ORM saves a lot of headaches. If you're doing complex reporting queries, an ORM is strictly worse. And yes, I've seen developers, architects, and authors of ORMs that believed otherwise. They are wrong. As an example, very, very few ORMs can make the distinction between SELECT ... FROM foo LEFT JOIN bar ON foo.id = bar.foo_id AND bar.category_id = 5 LEFT JOIN baz ON bar.id = baz.bar_id ... and SELECT ... FROM foo LEFT JOIN bar ON foo.id = bar.foo_id LEFT JOIN baz ON bar.id = baz.bar_id AND bar.category_id = 5 ... (And if you get into things like analytic queries, just forget about it.)
  • jakearmitage 6y ago
    Curious, what is the difference here?
    • deleted 6y ago
      [deleted]
    • gtsteve 6y ago
      I believe they may have intended to write this query with INNER JOINs instead of LEFT JOINs. If so, the result of the query would be identical but the second one would likely have performance problems given the category_id filter is not being applied at the point where bar is joined. A clever database engine might find an optimisation but I wouldn't count on it. This illustrates the subtle problem that ORMs can introduce - all logical tests pass but issues emerge over time. I found that typically every ORM based problem was resolved by rewriting it using SQL. I used to use ORMs extensively, but my philosophy now is that you can make simple queries by using a query generator (i.e. connection.GetByID("MyTable", 100)) and complex queries have to be written by hand. I would not choose to use an ORM again.
      • btilly 6y ago
        No, left joins were intended here. They mean different things. With inner joins the semantics are the same. If the performance is different, the database query optimizer is broken.
        • gtsteve 6y ago
          I see, apologies. However, I'm reasonably certain we'd see poor performance with that query on MySQL 5.7, but I don't think it's got as good an optimiser as Postgres for example.
          • btilly 6y ago
            I can't say that MySQL doesn't perform poorly. But if so, then their optimizer is more broken than I though. Even SQLite figures this one out and does it right.
    • btilly 6y ago
      The difference is whether the condition on bar is a filter is on the join from bar or baz.
    • saltcured 6y ago
      The extra "AND expr" in the join clause makes it more strict, which means the outer join can produce more nulls in the joined tuple. In a sequence like A left join B left join C, moving the extra join filter between the two joins is the difference between getting tuples like (a..., null..., null...) vs (a..., b..., null...).
    • magicalhippo 6y ago
      In the first case, if for a given row bar.category_id <> 5 then there's no match and hence null will be returned for any value from bar. Thus there will be no match in baz either (assuming the id columns are non-null). In the second case, the first join will always return a row from bar for a given foo_id if it exists, but the second join does not return values from baz for rows where bar.category_id <> 5.
• sk5t 6y ago
  Swapping out database platforms is pretty deep into YAGNI territory for most. Unless you know you are selling an on-prem software product to some customers who will demand MSSQL and others who will demand Oracle, or whatever, this "swap databases" justification for scrupulously using an ORM is not well grounded in reality.
• oftenwrong 6y ago
  Switching out the db under a long-in-the-tooth application is going to painful, no matter what. An ORM will not save you.