What ORMs have taught me: just learn SQL (2014)

Post by fishtoaster » Sun, Jun 26, 2016, 8:50 PM UTC

maxxxxx wrote: post #3

I think ORMs are a great tool to get something off the ground quickly. Like with most tools you will hit a point where they make things more difficult and then it's probably time to switch to SQL only or mix SQL with ORM especially for performance critical queries. In most applications I have seen the ORM provided a lot of value but there were cases where it needed to be augmented with raw SQL. I never understand why…

  users = User.where(has_foo: true).limit(10)

  rows = connection.exec_query("SELECT * FROM users WHERE has_foo=true LIMIT 10")
  users = rows.map { |row| User.build(row) }

Post by krapp » Sun, Jun 26, 2016, 9:07 PM UTC

PavlovsCat wrote: post #16

sirn wrote: post #5

Earlier quoted context omitted.

But how do you do: SELECT posts.*, (SELECT COUNT(1) FROM comments WHERE post_id = posts.id) AS comments_count FROM posts; In ActiveRecord, without 1+N queries, or caching comments_count in a column somewhere? Admittedly, that was not the best example. The last time I need something more intertwined than a simple COUNT in subquery, the answer was "give up and just use Arel." But at this point it is no longer quite Act…

What's more, even with just bare prepared statements.. how do I use dynamically built SQL queries and prepared statements together? And please don't just say "don't", at least not without telling me how to achieve what I need the proper way :) For example, let's say you have a query that gets search results, and depending on whether the visitor is logged in or not you also may want to know whether a given search resu…

    function getResultsForUser($DB, $user_id)
    {
      $query="search for user :user_id, SQL pertaining to favourites";   
      $stmt=$DB->prepare($query);
      $stmt->execute(["user_id"=>$user_id]);
      return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    function getResultsForVisitor($DB)
    {
        $query="blah";
        $stmt=$DB->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

Post by [deleted] » Sun, Jun 26, 2016, 9:09 PM UTC

bunderbunder wrote: post #12

tfigment wrote: post #6

I written a fair number of C# LOB apps and use LINQ quite a bit with mysql. I don't even want to talk about Java and some of its ORMs as its too painful to think about. I agree with the sentiment of the post but in compiled languages I really want an ORM to simplify unpacking result sets. LINQ is great when it works but joins sort of suck as well as calling in-built sql functions and it can some times generate highly…

On C#, Dapper's useful, but not perfect, for letting you write your own queries and then making it easy to unpack the result sets. Unfortunately, it relies on property setters for doing the unpacking, so it doesn't interact super well with your code if you like to avoid unnecessary mutability. The only publicly-available lightweight ORM I know of that does a good job with that is the SQL type provider in F#.Data. Tha…

Post by zepolen » Sun, Jun 26, 2016, 9:09 PM UTC

fishtoaster wrote: post #31

maxxxxx wrote: post #3

I think ORMs are a great tool to get something off the ground quickly. Like with most tools you will hit a point where they make things more difficult and then it's probably time to switch to SQL only or mix SQL with ORM especially for performance critical queries. In most applications I have seen the ORM provided a lot of value but there were cases where it needed to be augmented with raw SQL. I never understand why…

I completely agree. 90% of the queries in my app are no more complex than selecting from a table with a simple condition. I definitely find users = User.where(has_foo: true).limit(10) to be a lot more readable than rows = connection.exec_query("SELECT * FROM users WHERE has_foo=true LIMIT 10") users = rows.map { |row| User.build(row) } (And that's an example with no user-provided input) Likewise, any app of sufficien…

Post by beachy » Sun, Jun 26, 2016, 9:11 PM UTC