# Need help on writing a \[simple?\] query

**URL:** <https://www.ruby-forum.com/t/need-help-on-writing-a-simple-query/104159>\
**Category:** Rails\
**Created:** [May 14, 2007, 9:51pm UTC](https://www.ruby-forum.com/t/need-help-on-writing-a-simple-query/104159 "2007-05-14T21:51:00Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![vince](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/b5e925/32.png) [@vince](https://www.ruby-forum.com/u/vince)\
**Post date:** [May 14, 2007, 9:51pm UTC](https://www.ruby-forum.com/t/need-help-on-writing-a-simple-query/104159/1 "2007-05-14T21:51:00Z")

</div>

Hi. Can someone help me get started on writing a query? I’m getting  
tripped up on this. I basically just need to query and display all  
courses that have at least one ‘instructor\_confirmed’. The table  
structure is simple and has a basic habtm relationship.

courses(title)  
users(username)  
courses\_users(course\_id, user\_id, user\_type)

user\_types are “instructor\_confirmed”, “student”, or  
“instructor\_pending”

thanks for any help!

---

<div class="post-metadata">

**Author:** ![vince](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/b5e925/32.png) [@vince](https://www.ruby-forum.com/u/vince)\
**Post date:** [May 14, 2007, 11:41pm UTC](https://www.ruby-forum.com/t/need-help-on-writing-a-simple-query/104159/2 "2007-05-14T23:41:24Z")

</div>

> courses(title)  
> users(username)  
> courses\_users(course\_id, user\_id, user\_type)

Firstly, this looks more like a :has\_many\_through rather than a  
HABTM…

Secondly, this is not as “Simple?” a query as you might expect.  
Although this is a fairly common need.

In SQL you would need to use a JOIN to accomplish this. I’m sure you  
could probably find a design pattern or two for this type of query if  
you search the web for it. I’m sorry I don’t have any links off hand.

The most efficient way is to search against a resource that joins user  
and courses. Only the join table has knowledge of both sides of your  
relationship.

Let’s take a look at the SQL approach:

SELECT DISTINCT courses.id, courses.title FROM courses JOIN  
courses\_users ON courses.id=courses\_users.course\_id WHERE  
courses\_users.user\_type = “instructor\_confirmed”;

This would give you the list of DISTINCT courses. You need the  
DISTINCT to eliminate the duplicate rows that would be returned by the  
JOIN.

In Rails you could just fetch all the courses\_users resources, which  
will be more than you need due to the duplicates, and then iterate  
over the results adding them to an array while ensuring you don’t add  
in the duplicates. Or possibly use Array#uniq to get an array with  
duplicates removed.

I’m sure there’s probably a better way than this, but it should work  
and be relatively efficient.

---

<div class="post-metadata">

**Author:** ![vince](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/b5e925/32.png) [@vince](https://www.ruby-forum.com/u/vince)\
**Post date:** [May 14, 2007, 11:45pm UTC](https://www.ruby-forum.com/t/need-help-on-writing-a-simple-query/104159/3 "2007-05-14T23:45:24Z")

</div>

On May 14, 3:48 pm, vince [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> “instructor\_pending”

Before thinking about your query, you have to think about your model a  
bit more. What you show is not a basic habtm relationship, because you  
have added an additional attribute to your join table. This means you  
should be using a join model and a has\_many :through relationship.  
When you make that change, it will be simple to access your user\_type  
attribute. You could also improve the name of the model, since you can  
use any name you want for the join model. I’d also think about  
renaming user\_type, because that really isn’t the function of that  
attribute.

- Mark.

---

<div class="post-metadata">

**Author:** ![vince](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/b5e925/32.png) [@vince](https://www.ruby-forum.com/u/vince)\
**Post date:** [May 15, 2007, 1:02am UTC](https://www.ruby-forum.com/t/need-help-on-writing-a-simple-query/104159/4 "2007-05-15T01:02:31Z")

</div>

i changed the tables to look like this:

courses(title)  
participants(course\_id, user\_id, status)  
users(username)

with the users and courses being connected with a has\_many :through  
relationship. status is one of the following “instructor\_confirmed”,  
“student”, or “instructor\_pending”. still don’t know how to do the  
query though 🙂 would i have to use SQL commands for this? again,  
i’m trying to query and display all courses that have at least one  
“instructor\_confirmed”.

---

<div class="post-metadata">

**Author:** ![vince](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/b5e925/32.png) [@vince](https://www.ruby-forum.com/u/vince)\
**Post date:** [May 15, 2007, 5:06am UTC](https://www.ruby-forum.com/t/need-help-on-writing-a-simple-query/104159/5 "2007-05-15T05:06:23Z")

</div>

Eleatic wrote:

> courses(title) has\_many :users, :through =\> :schedules

I mean “has many :people”

---

<div class="post-metadata">

**Author:** ![vince](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/b5e925/32.png) [@vince](https://www.ruby-forum.com/u/vince)\
**Post date:** [May 15, 2007, 5:05am UTC](https://www.ruby-forum.com/t/need-help-on-writing-a-simple-query/104159/6 "2007-05-15T05:05:36Z")

</div>

How about this?

courses(title) has\_many :users, :through =\> :schedules  
schedules(course\_id, person\_id, role)  
people(name) has\_many :courses, :through =\> :schedules

This gives you the following:

course\_ids = Course.find(:all, :select =\> :id)  
courses\_with\_instructors = []  
for course in course\_ids  
courses\_with\_instructors \<\<  
course.people.find\_by\_role(“instructor\_confirmed”)  
end

joe = Person.find\_by\_name(“Joe”)  
joes\_courses = joe.courses

railsology = Course.find\_by\_title(“Railsology”)  
railsology\_students = railsology.people.find\_by\_role(“student”)  
railsology\_prof = railsology.people.find\_by\_role(“instructor\_confirmed”)