Finding parent records without has_many :through association

Hi all,

Here's an interesting problem. I've got a reasonably complex set of
models to represent a college campus housing lottery.

class Dorm
  has_many :rooms
  has_many :room_assignments, :through => :rooms
end

class Room
  belongs_to :dorm
  has_many :room_assignments
end

class RoomAssignment
  belongs_to :room
  has_one :student
end

As students choose rooms, a RoomAssignment gets created for them. I
want a way to pull from the DB all of the rooms belonging to a
particular dorm that don't have any associated room assignments. Is
there a way to do that without pulling all of the rooms, and doing a
@rooms = @rooms.reject{|r| r.room_assignments.length > 0} ?

Thanks!

The easiest way is to drop to sql because it’s faster.

class Dorm < ActiveRecord::Base

your other code

find all the empty rooms in this dorm

def empty_rooms
sql = "select * from rooms left join room_assignments
on rooms.dorm_id = #{self.id} and rooms.id = room_assignments.room_id
where room_assignments.room_id is null "
rooms = Room.find_by_sql(sql)
end

end

This is modified from a reservation system I wrote so I didn’t test this but it should work for you.

Thanks Brian. I've been trying to avoid dropping to SQL, because of
constraints that I didn't put in my initial post. The rooms also have
another associated model -- RoomAttributes -- that I need to pull from
the db at the same time. As far as I know, there's no way to load
associated models when doing find_by_sql.

So I guess there are two avenues I'm asking about:

1) How do you load associated models using find_by_sql?

or,

2) How do you load a model that doesn't have associated (has_many
:through) objects, without resorting to find_by_sql?

Brian Hogan wrote:

Brent:

Don’t fear the SQL. Get your project done. If you need additional attributes, load them into the query.

I don’t understand what you’re asking about loading models without associations. If you don’t define an association between models, you can’t use them.

Look at the API. Use the SQL approach.

has_many :subscribers, :class_name => "Person", :finder_sql =>
      'SELECT DISTINCT people.* ' +
      'FROM people p, post_subscriptions ps ' +

      'WHERE ps.post_id = #{id} AND ps.person_id = [p.id](http://p.id) ' +
      'ORDER BY p.first_name'

The way I do it is via SQL. Someone else may be able to help.

It's not about fearing the SQL. :+)

What I would really like to do is something like this:

Dorm.find(:all, :include => {:rooms => [:room_assignments,
:room_attributes]}, :conditions => 'there is no room_assignment')

Because this is for a housing lottery, where students choose available
rooms, I need to pull rooms that have no records in the defined
association.

If I simply do a find_by_sql, there's no way to have the associated
models (:rooms and :room_assignments) load in the same query. I don't
think I can afford the performance hit of doing this in multiple
queries. Does this make more sense?

Brent:

Did you look at the api like I recommended? Cos it just occurred to me that this would probably work…

Dorm.find(:all, :include => {:rooms => [:room_assignments,
:room_attributes]}, :conditions => ‘room_assignments.room_id is null’)

But I hope you don’t have a lot of dorms. Loading a lot of objects into memory can be a bit time consuming. That’s why I use the sql route - for speed.

p.s. : I’ve done room lottery before. I’m quite familiar with the concept. :slight_smile:

Yeah -- that doesn't work. It's because the story is, yet again, more
complicated than I made it seem. There are tons of RoomAssignments in
the database -- the DB is a legacy database over which I don't have a
lot of control, since it's used by other applications & systems. The
table that holds the room assignments has all of the historical data.
So, I always need a condition that pulls out the current term. So my
conditions would look like this:

:conditions => "room_assignments.room_id is null AND
room_assignments.term = '200801'"

The 200801 refers to the fall of the 2007-08 academic year. That
condition will NEVER be fulfilled. :frowning:

So this brings me back to the 2 ways for solving the problem:

1) Loading the associated models when using find_by_sql, or with a
:finder_sql statement on the association
2) Loading a model that doesn't have associated (has_many :through)
objects that fit an arbitrary criterion, without resorting to
find_by_sql?

I've been stewing at this for several days, and nothing I've tried has
been working. Brian, I totally appreciate the help. :slight_smile:

Brent Miller wrote:

1) Loading the associated models when using find_by_sql, or with a
:finder_sql statement on the association
2) Loading a model that doesn't have associated (has_many :through)
objects that fit an arbitrary criterion, without resorting to
find_by_sql?

I've been stewing at this for several days, and nothing I've tried has
been working. Brian, I totally appreciate the help. :slight_smile:

Brent,

Does this legacy database support views, If so, you can create a view
that strips out the legacy data and forms it to be a first class citizen
for ActiveRecord::Base. I have used views before to be consumed by
rails, the only thing you really have to watch out for is that
migrations will misread your views as tables so you have to find a way
to work around it.

Views have the advantage of cleaning up your server code as well so when
you do get a chance to migrate your db to the new form, it will be a
much smoother transition..

hth

ilan

The legacy DB is Oracle, so it *definitely* supports views. Today while
exchanging messages on this thread I've been playing with views, to make
the table nicer to use with Rails and abstract out the useless data.
I've pretty much come to the conclusion that it's the easiest way to go,
although I'm still curious about the 2 main questions we came up with
above. I'm sure we'll run into these kinds of situations again.

We will never be able to migrate our DB to the new form, since it's very
large, very complex, and very much out of our hands -- it's an
off-the-shelf product that our organization is pretty much wedded to.

In general, though, our best practice is not to use the views to correct
the naming scheme: that would require us to go back and re-examine every
app we write against this DB whenever there's a DB upgrade, in order to
fix the views. The upgrades are more frequent than we'd like, and
always cause some kind of havoc; our policy is to try and minimize that.
In general I've found that rails does pretty well in this situation,
except when complex queries like this one are needed.