Rails - Feb 2008 - 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!
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.


On Fri, Feb 29, 2008 at 12:23 PM, Brent Miller <
rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:
>
> 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!
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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:
> 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.
> 
> 
> On Fri, Feb 29, 2008 at 12:23 PM, Brent Miller <
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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 '' +
      ''ORDER BY p.first_name''


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

On Fri, Feb 29, 2008 at 12:52 PM, Brent Miller <
rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:
>
> 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:
> > 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.
> >
> >
> > On Fri, Feb 29, 2008 at 12:23 PM, Brent Miller <
>
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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?
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.
:)

On Fri, Feb 29, 2008 at 1:36 PM, Brent Miller <
rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:
>
> 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?
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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. :(

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. :)
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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. :)
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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---