Joins in Rails

Rails
1

Hi guys, I have a find statement written like so in my event model:

@events = find(:all, :conditions => ["((? BETWEEN start_on AND end_on)
OR (? BETWEEN start_on AND end_on)) AND id != ?",
start_time,end_time,event_id])

but I would like to add to this, to only return the results where the
event id and (an additional) user id appear in another link-table called
subscriptions.

My event model has the following defined at the top:

class Event < ActiveRecord::Base
has_many :subscriptions, :dependent => :destroy
has_many :users, :through => :subscriptions

and likewise the subscription model has the following defined at the
top:

class Subscription < ActiveRecord::Base
belongs_to :events
belongs_to :users

I could probably hard code it into the :conditions in the above find
method call above but this seems wrong - any ideas?

Cheers
Mick

2

I may be missing something but have you tried:

Event.find(:all, :conditions => [ … ], :include => :users)

I am unsure whether this would work with a has_many :through but it is
worth a shot. At the very least you should be able to :include =>
:subscriptions and collect your users from that.

-Shawn

3

Shawn Roske wrote:

I may be missing something but have you tried:

Event.find(:all, :conditions => [ … ], :include => :users)

I am unsure whether this would work with a has_many :through but it is
worth a shot. At the very least you should be able to :include =>
:subscriptions and collect your users from that.

-Shawn

Hmmm, I tried that and it doesnt work. The functionality I am trying to
creat is to display a list of a user’s subscriptions and check against
any other event to see if there are 2 events which clash (time-wise).

But currently it returns all events which clash, regardless of whether
the user has subscribed (i.e. has a record stored in the subscriptions
table).

4

Ok, I see, looking at

:include gives a LEFT OUTER JOIN which you don’t want, you should be
able to use :join to do this, e.g.:

Event.find(:all, :conditions => [ … ], join => “INNER JOIN
subscriptions ON subscriptions.event_id = id”)

There may be a nicer rails-ish way of doing this, but I am unaware of
it.

-Shawn