# SQL 2 table(or model) query

**URL:** <https://www.ruby-forum.com/t/sql-2-table-or-model-query/88485>\
**Category:** Rails\
**Created:** [March 11, 2007, 11:34pm UTC](https://www.ruby-forum.com/t/sql-2-table-or-model-query/88485 "2007-03-11T23:34:23Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![mike](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/58f4c7/32.png) [@mike](https://www.ruby-forum.com/u/mike)\
**Post date:** [March 11, 2007, 11:34pm UTC](https://www.ruby-forum.com/t/sql-2-table-or-model-query/88485/1 "2007-03-11T23:34:23Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![mike](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/58f4c7/32.png) [@mike](https://www.ruby-forum.com/u/mike)\
**Post date:** [March 11, 2007, 11:47pm UTC](https://www.ruby-forum.com/t/sql-2-table-or-model-query/88485/2 "2007-03-11T23:47:23Z")

</div>

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])

---

<div class="post-metadata">

**Author:** ![mike](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/58f4c7/32.png) [@mike](https://www.ruby-forum.com/u/mike)\
**Post date:** [March 11, 2007, 11:52pm UTC](https://www.ruby-forum.com/t/sql-2-table-or-model-query/88485/3 "2007-03-11T23:52:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mike](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/58f4c7/32.png) [@mike](https://www.ruby-forum.com/u/mike)\
**Post date:** [March 11, 2007, 11:57pm UTC](https://www.ruby-forum.com/t/sql-2-table-or-model-query/88485/4 "2007-03-11T23:57:07Z")

</div>

Hi Mike, your join table should be named:

events\_users

-Conrad

---

<div class="post-metadata">

**Author:** ![mike](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/58f4c7/32.png) [@mike](https://www.ruby-forum.com/u/mike)\
**Post date:** [March 11, 2007, 11:55pm UTC](https://www.ruby-forum.com/t/sql-2-table-or-model-query/88485/5 "2007-03-11T23:55:19Z")

</div>

Great - thanks!

---

<div class="post-metadata">

**Author:** ![mike](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/58f4c7/32.png) [@mike](https://www.ruby-forum.com/u/mike)\
**Post date:** [March 12, 2007, 1:29pm UTC](https://www.ruby-forum.com/t/sql-2-table-or-model-query/88485/6 "2007-03-12T13:29:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mike](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/58f4c7/32.png) [@mike](https://www.ruby-forum.com/u/mike)\
**Post date:** [March 15, 2007, 2:28pm UTC](https://www.ruby-forum.com/t/sql-2-table-or-model-query/88485/7 "2007-03-15T14:28:17Z")

</div>

Mike,

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

–Bill

---

<div class="post-metadata">

**Author:** ![mike](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/58f4c7/32.png) [@mike](https://www.ruby-forum.com/u/mike)\
**Post date:** [March 19, 2007, 4:50pm UTC](https://www.ruby-forum.com/t/sql-2-table-or-model-query/88485/8 "2007-03-19T16:50:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mike](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/58f4c7/32.png) [@mike](https://www.ruby-forum.com/u/mike)\
**Post date:** [March 12, 2007, 2:38pm UTC](https://www.ruby-forum.com/t/sql-2-table-or-model-query/88485/9 "2007-03-12T14:38:23Z")

</div>

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