SQL 2 table(or model) query


#1

I have 3 tables, one called events, one called users, and one called
created_events which contains an event id and a user id (in order to
link a user and an event together) - each one corresponds to its
relevent model.

I would like to run a query to return the created event for a certain
user by passing their ID to the created_events table - and then return
the relevent rows, plus the event data (linked by the event id).

Is there a way to do this in rails - I could do it in SQL using joins
but am a little unsure of the rails approach!


#2

Mike wrote:

I have 3 tables, one called events, one called users, and one called
created_events which contains an event id and a user id (in order to
link a user and an event together) - each one corresponds to its
relevent model.

I would like to run a query to return the created event for a certain
user by passing their ID to the created_events table - and then return
the relevent rows, plus the event data (linked by the event id).

Is there a way to do this in rails - I could do it in SQL using joins
but am a little unsure of the rails approach!

FYI this is how I have done it in SQL:

@created_events = CreatedEvent.find_by_sql("SELECT ce., e. from
created_events ce, events e WHERE (e.id = ce.event_id) AND ce.user_id =
" + params[:id])


#3

Hi, you can do the following to query for the events associated with a
particular user:

class User < ActiveRecord::Base
has_and_belongs_to_many :events
end

class Event < ActiveRecord::Base
has_and_belongs_to_many :users
end

Now, you can do the following to determine what events are associated
with a given user:

user.events

Now, you can do the following to determine what users are associated
with a given event:

event.users

Lastly, I would recommend reading chapter 18 of AWDwRv2 for further
information:

Good luck,

-Conrad


#4

Hi Mike, your join table should be named:

events_users

-Conrad


#5

Great - thanks!


#6

Conrad T. wrote:

Hi Mike, your join table should be named:

events_users

-Conrad

Hi - once I have done all that, should calling “event.users” return all
the users associated with an event (as defined in the events_users
table) as currently I get the error “undefined method `events’ for
EventUser:Class” - I am very new to rails by the way so Im sure this is
a simple error on my part!!

Any ideas?

Cheers

Mike


#7

Mike,

I’m just catching up on my mail lists. Did you get past your problem
here?

–Bill


#8

Hi there, i did thanks!

William (Bill) Froelich wrote:

Mike,

I’m just catching up on my mail lists. Did you get past your problem
here?

–Bill


#9

OK, first sorry for the double post!
I have revised my model thus: I have a link table which stores a user id
and an event id, plus two extra columns “subscribed” and “created” -
users can create and subscribe to events independently.

My model:

class Event < ActiveRecord::Base
belongs_to :user_events
has_many :users, :through => :user_events

class User < ActiveRecord::Base
belongs_to :user_events
has_many :events, :through => :user_events

class UserEvent < ActiveRecord::Base
has_many :events
has_many :users

with database tables “events”, “users” and “user_events” but I am
currently getting the error “uninitialized constant User::UserEvents”
from this call “User.find(params[:id]).events”. Is this poor
pluralisation on my part?

Any help is appreciated!
Cheers Mike