need help on writing a [simple?] query

rubyonrails-talk
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

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".