Need help on writing a [simple?] query

Rails
1

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!

2

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.

3

On May 14, 3:48 pm, vince [email protected] 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.
4

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 :slight_smile: 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”.

5

Eleatic wrote:

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

I mean “has many :people”

6

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