136 ms·
But how do you do: SELECT posts.*, (SELECT COUNT(1) FROM comments WHERE post_id = posts.id) AS comments_count FROM posts; In ActiveRecord,
by sirn 10y ago
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 ActiveRecord, but rather a SQL without strings.
This is my biggest gripe against the Active Record pattern in general, as it ties its model too tightly to the underlying database. It is convenient for a simple CRUD tasks, which may fit about 90% of use case, but that's not the only thing the database is capable of.
- samwillis 10y agoI don't know about active record but with Django it's simply: Post.objects.all().annotate(Count('comments')) Which produces (simplified, Django would actually explicitly select each column): SELECT posts.*, COUNT(comments.id) AS comments_count FROM posts LEFT OUTER JOIN comments ON (posts.id = comments.post_id) GROUP BY posts.id Nice, easy and without 1+N queries.
- sirn 10y agoThat is actually very nice and elegant!
- fennecfoxen 10y agoI've actually never used ActiveRecord myself, but when an ORM breaks down and it becomes hard to make certain queries I've had good luck with other ORMs by creating a view in the database and telling the ORM that it's a table. Obviously you can't insert into arbitrary views, but most are happy to give you an object back, with appropriate relationships to the actual database-writable objects. I'm surprised there isn't more explicit support for this in more ORMs (things like not having 'save' methods on the ORMed classes).
- PavlovsCat 10y agoWhat'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 result happens to be a favourited item. The way I understand it, I would have to do for example: $query_text = 'blah'; if (user is logged in) { $query_text .= 'SQL pertaining to favourites'; } $stmt = $dbh->prepare($query_text); if (user is logged in) { $stmt->bindParam(':user_id', $user_id); } Or am I missing something?
- prodigal_erik 10y agoQueries with and without user_id probably have different plans (e.g., table scan vs. index lookup) and need to be prepared separately. I've never seen an ORM that takes prepared statements and query planning seriously, generally they only emit trivial queries.
- PavlovsCat 10y agoSince the queries only get re-used per connection IIRC, that's moot for my case (personal derpy CMS) anyway, since they all just get used once per request, anything loopish is admin-related and fast enough anyway. What really bugged me though was the idea of having to repeat the decision logic that determines what the query ends up being twice... though I just realized that putting name/value pairs into an array while building the query, and then using that when the query is built to bind them to it, is probably fine.
- krapp 10y agoJust use separate statements. You should already know before that point whether or not the request is coming from a logged in user, so to me even the if statements are redundant - have functions or methods that just take parameters for and return the result set of a single SQL query, and figure out which function to call and how valid the results are elsewhere: 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); } Yes, it does mean more code, but that code will be much cleaner, more readable, more easily transportable (by not depending on a third party library) and less bug-prone.
- fbonetti 10y agoThat's really easy. Post.select("posts.*, count(comments.*) as comments_count").joins(:comments)
- sirn 10y agoThanks for the answer! Unfortunately, what I needed to do back then were more complex than that (say, I need to perform query on top of subquery, e.g. "SELECT ... FROM (SELECT ...) AS a HAVING ... GROUP BY ..." sort of thing) which seems to be something ActiveRecord wasn't designed for. In the end, I solve the problem by using Arel with an ActiveModel and unwrap all the data manually.
- jimktrains2 10y agoWouldn't you need to do a group by in your query as well?
- fbonetti 10y agoYes
- jeltz 10y agoThat is not the same query at all. You need to do a left join and a group by for it to become the same query. Wihout a left join posts without comments wont show up in the result.
- fbonetti 10y agoYou're totally right. My example is an inner join. I will admit that doing outer joins in ActiveRecord is quite painful. Here's a more realistic example, which is not nearly as pretty: Post.select("posts.*, COUNT(comments.*) AS comments_count").joins("LEFT JOIN comments ON comments.post_id = posts.id").group(:comments)