CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecordruby-on-rails-4
CuriousMind
CuriousMind

Reputation: 34175

How to write this ActiveRecord Query using Join instead of subquery in Rails 4

Consider the following:

class User < ActiveRecord::Base
  has_many :events
end

class Event < ActiveRecord::Base
  belongs_to :user #this user is the event owner
  has_many :members
end

class Members < ActiveRecord::Base
  belongs_to :user
  belongs_to :event
end

Now, I need to list all the members for which current_user is the owner. so I have come up with this:

@members = Member.where event_id: current_user.events

which produces the following query:

SELECT "members".* FROM "members" WHERE "members"."event_id" IN (SELECT "events"."id" FROM "events" WHERE "events"."user_id" = 1)

This works as expected but uses subqueries instead of JOIN. Does anyone know a better way to write this same query?

Upvotes: 1

Views: 387

Answers (4)

infused
infused

Reputation: 24367

Add a has_many :through association to your User model:

class User < ActiveRecord::Base
  has_many :events
  has_many :members, :through => :events
end

Now you can query for all a user's members through the members association:

user.members

The SQL generated will look something like:

SELECT "members".* FROM "members" INNER JOIN "events" ON "members"."id" = "events"."member_id" WHERE "events"."user_id" = 1

Upvotes: 2

Tibastral
Tibastral

Reputation: 582

You could do something like :

Member.joins(:event).where(events: {user_id: current_user.id})

Upvotes: 0

Paritosh Piplewar
Paritosh Piplewar

Reputation: 8132

I guess this will work. 

Member.joins(:event).where("events.user_id = ?" , current_user.id)

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659317

Transformed to JOIN syntax (with table aliases to make it shorter and easier to read):

SELECT m.*
FROM   events e
JOIN   members m ON m.event_id = e.id
WHERE  e.user_id = $1

Upvotes: 0

Related Questions