CODEWITHSUNDEEP
ruby-on-railsactiverecord
Rocky
Rocky

Reputation: 57

Translate SQL to Rails queries

How can I write this SQL statement the 'Rails way'?

SELECT users.*, count(invitations.id) AS invitations_count 
FROM users
LEFT OUTER JOIN invitations ON invitations.sender_id = users.id
GROUP BY users.id
HAVING count(invitations.sender_id) < 5 AND users.email_address NOTNULL
ORDER BY invitations_count

Should I be using squeel gem for these kinds of queries?

Upvotes: 0

Views: 457

Answers (2)

Kenny Grant
Kenny Grant

Reputation: 9623

This is available if you want to just transfer the SQL:

User.find_by_sql("...")

However, if you follow rails conventions with your naming (invitations.user_id), and store the invitation count on users (and update it when you add invitations) rather than doing a join to get it each time, you could do this:

On users:

scope :emailable, where('users.email_address IS NOT NULL')
scope :low_invitations, where('users.invitation_count < 5')

Then to query users with under 5 invites and an email address, ordered by no of invites:

@users = User.emailable.low_invitations.order('invitation_count asc')

Then access the invitations for a user with something like:

@user.invitations
@user.invitations.count 
etc

For the above, you would have to add an invitation_count col to users, change sender_id to user_id, and add some scopes to the user model. You could also probably use joins to get a count without having a denormalised invitation_count.

If you are going to use rails it's far easier to go with these conventions than against them, and you might find it worthwhile setting up a small experimental app and playing with relations, plus reading the associations guide:

http://guides.rubyonrails.org/association_basics.html

Upvotes: 1

Zepplock
Zepplock

Reputation: 29135

You need to define proper models for Invitations and Users, then connect them via n*1 or n*n relationships (via belongs_to, has_many, etc). Then you'll be able to write code that will generate this or similar query "under the hood".

When using Rails you have to stop thinking SQL and start thinking Models, Views, Controllers.

Upvotes: 0

Related Questions