Forum: Ruby on Rails need help on writing a [simple?] query

Announcement (2017-05-07): is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see and for other Rails- und Ruby-related community platforms.
Fd4ee9ae435369ab446489607701f518?d=identicon&s=25 vince (Guest)
on 2007-05-14 21:51
(Received via mailing list)
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_users(course_id, user_id, user_type)

user_types are "instructor_confirmed", "student", or

thanks for any help!
21f7ed21f11a809050594c82eab11d67?d=identicon&s=25 Robert Walker (Guest)
on 2007-05-14 23:41
(Received via mailing list)
> courses(title)
> users(username)
> courses_users(course_id, user_id, user_type)

Firstly, this looks more like a :has_many_through rather than a

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

Let's take a look at the SQL approach:

SELECT DISTINCT, courses.title FROM courses JOIN
courses_users ON 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

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.
Bd2d12f85f54f93d3c9e2f558a593024?d=identicon&s=25 Mark Thomas (markthomas)
on 2007-05-14 23:45
(Received via mailing list)
On May 14, 3:48 pm, vince <> 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

- Mark.
Fd4ee9ae435369ab446489607701f518?d=identicon&s=25 vince (Guest)
on 2007-05-15 01:02
(Received via mailing list)
i changed the tables to look like this:

participants(course_id, user_id, status)

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
A7d47a23d9ccb0860fe052b3141c91f7?d=identicon&s=25 Eleatic (Guest)
on 2007-05-15 05:05
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 <<

joe = Person.find_by_name("Joe")
joes_courses =

railsology = Course.find_by_title("Railsology")
railsology_students = railsology.people.find_by_role("student")
railsology_prof = railsology.people.find_by_role("instructor_confirmed")
A7d47a23d9ccb0860fe052b3141c91f7?d=identicon&s=25 Eleatic (Guest)
on 2007-05-15 05:06
Eleatic wrote:

> courses(title) has_many :users, :through => :schedules

I mean "has many :people"
This topic is locked and can not be replied to.