Forum: Ruby on Rails SQL 2 table(or model) query

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Mike (Guest)
on 2007-03-12 00:34
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!
Mike (Guest)
on 2007-03-12 00:47
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])
Conrad T. (Guest)
on 2007-03-12 00:52
(Received via mailing list)
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
Mike (Guest)
on 2007-03-12 00:55
Great - thanks!
Conrad T. (Guest)
on 2007-03-12 00:57
(Received via mailing list)
Hi Mike, your join table should be named:

events_users

-Conrad
Mike (Guest)
on 2007-03-12 14:29
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
Mike (Guest)
on 2007-03-12 15:38
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
William (Bill) Froelich (Guest)
on 2007-03-15 15:28
(Received via mailing list)
Mike,

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

--Bill
Mick (Guest)
on 2007-03-19 17:50
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
This topic is locked and can not be replied to.