Complex associations

[Trish]

I am working on a project that has some complex table associations,
and I am having a hard time declaring this association in a Model, and
fear it can't be done.

Here is an example of my issue...

class StoreType < ActiveRecord::Base; end
class Store < ActiveRecord::Base; end
class Department < ActiveRecord::Base; end
class Product < ActiveRecord::Base; end

A StoreType has many stores and a Store has many Department. However,
both Store and Department have many products. An product has the
columns store_id and department_id which are mutually exclusive. This
is because a Product may belong directly to a Department, or it may be
a 'global' product that belongs directly to the Store.

What I'd like to do is have an association in the StoreType model that
would give me all products for that StoreType.

Currently, I have set up the following associtations on StoreType:

class StoreType < ActiveRecord::Base
has_many :stores
has_many :departments, :through=>:stores

has_many :store_products, :through=>:stores, :source=>roducts, :uniq
=> true

has_many :department_products, :through=>:departments, :source=>roducts, :uniq
=> true
end

This is using the Nested Has Many Through plugin to achieve the nested
association (department_products).

However, I'd like to have a generic 'products' association for
StoreType that pulls a combination of the two product associations.
I'd like to do this through an association instead of just through a
function because I want to gain the dynamic methods created by
has_many, specifically the "collection.find(...)" method so I can add
more conditions to the collection.

Is there a way to do this?

Thanks in advance!
Trish

 

[Brian Candler]

Is there a way to do this?

Named or anonymous scopes perhaps. See these posts:
http://blog.ethanvizitei.com/2009/05/joins-and-namedscopes-in-activerecord.html
http://webjazz.blogspot.com/2008/06/anonymous-scope-unknown-cousin-of-named.html

So I'm guessing something like this (completely untested):

class Store
def products
Product.scopedjoins => :department,
:conditions=>["products.store_id=? or (products.department_id =
departments.id and departments.store_id=?)", id, id])
end
end

And the reverse:

class Product
def store
if store_id
Store.find(store_id)
elsif department_id
Store.find_firstjoins => :department,
:conditions=>["stores.id = departments.store_id and
departments.id = ?", department_id])
end
end
end

I'm not quite sure I've understood the model though. A particular
product can only be sold in one department or one store? If another
store wants to stock it as well, do you need another Product row?

 

[Trish]

Thanks for the input. I will give these a shot. Sorry about the
example... it was just my way of trying to simplify what we have going
on in our application. StoreType, Store, Department, and Product are
just models I "invented" to get my problem across

After digging into this further, it appears that the *real* issue I'm
trying to solve is how to get the store_products and
department_products in one SQL statement and be able to sort on an
Store attribute (i.e. Store Name). This is for an existing project
that used to do this with pure SQL, and had a lot of hard-coding for
sort orders.

The end result is for a report that has multi-sortable columns that
rely on MySQL ORDER BY to get the proper orders.

Also... sorry about the double post (to those who may have
noticed ) It did not appear that my original posted, so it's not
that I was being impatient, it's that I was blind

Thanks!
Trish

Is there a way to do this?

Named or anonymous scopes perhaps. See these posts:http://blog.ethanvizit= ei.com/2009/05/joins-and-namedscopes-in-activer...http://webjazz.blogspot.c=
om/2008/06/anonymous-scope-unknown-cousin-of...

So I'm guessing something like this (completely untested):

class Store
=A0 def products
=A0 =A0 Product.scopedjoins =3D> :department,
=A0 =A0 =A0 :conditions=3D>["products.store_id=3D? or (products.departmen= t_id =3D
departments.id and departments.store_id=3D?)", id, id])
=A0 end
end

And the reverse:

class Product
=A0 def store
=A0 =A0 if store_id
=A0 =A0 =A0 Store.find(store_id)
=A0 =A0 elsif department_id
=A0 =A0 =A0 Store.find_firstjoins =3D> :department,
=A0 =A0 =A0 =A0 :conditions=3D>["stores.id =3D departments.store_id and
departments.id =3D ?", department_id])
=A0 =A0 end
=A0 end
end

I'm not quite sure I've understood the model though. A particular
product can only be sold in one department or one store? If another
store wants to stock it as well, do you need another Product row?