CODEWITHSUNDEEP
mysqlsqlruby-on-railsactiverecord
Dennis Hackethal
Dennis Hackethal

Reputation: 14295

Rails & Mysql - Optimize and rephrase query

Thanks to this answer to a previous question of mine and this article I found a way to rephrase this query:

SELECT * FROM participants
GROUP BY conversation_id
HAVING sum(user_id not in (1,3)) = 0

to this Rails style query:

Participant.find(:all, group: "conversation_id HAVING sum(user_id not in (1,3)) = 0")

I am new to Rails, but it seems to me that this is old syntax - what bothers me the most is that I use a lot of sql in there, instead of doing it all in Rails syntax.

How can I rephrase the query above even more so that it is only written in Rails syntax, possibly without any need for quotation marks? I need to make sure that this will run with different databases.

Upvotes: 1

Views: 103

Answers (1)

gcastro
gcastro

Reputation: 6276

The Rails 3 way would be:

Participant.group("conversation_id").having("sum(user_id not in (1,3)) = 0")

You won't be able to do away with strings in the having clause. What's your concern with strings? The query above should be fairly portable.

Upvotes: 1

Related Questions