Complex associations

rubyonrails-talk
1

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=>:products, :uniq => true

has_many :department_products, :through=>:departments, :source=>:products, :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

2

class Store < ActiveRecord::Base

has_many: store_type

has_many: product_type, :through => department

class Department < ActiveRecord::Base

belongs_to: store

belongs_to: product

class Product < ActiveRecord::Base

has_many :departments

has_many :stores, :through => department

end

I think you need to remove the StoreType as a model…

3

Thank you for your response. However, I am refactoring an existing application, and am trying to remove a bunch of pure SQL that was put in the code. I believe the best way to do this is with a table association. One of the big features I want to use out of this is retrieving the records in the proper order (I'd prefer not to sort with Ruby after the fact since that would force me to rewrite a bunch of code for the sorting of the report).

However, as this is an existing application (similar in setup to the example I gave), I cannot remove the model, as there is a table already associated with it, and the ids are being used.

Another option that would work is to use a 'joins' statement in my finder method so I can sort products based on Store attributes with something like this: FROM products LEFT JOIN departments on departements.id = products.department_id LEFT JOIN stores on stores.id = products.store_id OR stores.id = departments.store_id

However, I am also using an :include option for several associations, and it seems that joins and include are mutually exclusive. I'd really had to have to write out all of the associations.

Any thoughts?

Thanks! Trish

4

Thank you for your response. However, I am refactoring an existing

application, and am trying to remove a bunch of pure SQL that was put

in the code. I believe the best way to do this is with a table

association. Why do you believe this?

One of the big features I want to use out of this is

retrieving the records in the proper order (I’d prefer not to sort

with Ruby after the fact since that would force me to rewrite a bunch

of code for the sorting of the report). What is the proper order?

However, as this is an existing application (similar in setup to the

example I gave), I cannot remove the model, as there is a table

already associated with it, and the ids are being used. Gotcha.

Another option that would work is to use a ‘joins’ statement in my

finder method so I can sort products based on Store attributes with

something like this:

FROM products

LEFT JOIN departments on departements.id = products.department_id

LEFT JOIN stores on stores.id = products.store_id OR stores.id =

departments.store_id why not SELECT view INNER JOIN associations WHERE = AND = OR =.

However, I am also using an :include option for several associations,

and it seems that joins and include are mutually exclusive. I’d

really had to have to write out all of the associations. I’ll respond after I unravel what that means in my head.

Any thoughts? Those are my thoughts. I’m newish and find attempting to help in turn helps me to learn. I hope you don’t mind.

Thanks! You are welcome

Trish Angel

5

See below...

Thanks! Trish

> Thank you for your response. However, I am refactoring an existing > application, and am trying to remove a bunch of pure SQL that was put > in the code. I believe the best way to do this is with a table > association.

Why do you believe this?

This seems to be the best way to retrieve the needed data in the proper order. My closest attempt gets me two sets of data (for department_products and store_products), and then I do a "find" on their ids, allowing me to do the :order statement as needed.

> One of the big features I want to use out of this is > retrieving the records in the proper order (I'd prefer not to sort > with Ruby after the fact since that would force me to rewrite a bunch > of code for the sorting of the report).

What is the proper order?

This report needs to have (and currently has) multi-column sort capabilities

However, as this is an existing application (similar in setup to the> example I gave), I cannot remove the model, as there is a table > already associated with it, and the ids are being used.

Gotcha.

> Another option that would work is to use a 'joins' statement in my > finder method so I can sort products based on Store attributes with > something like this: > FROM products > LEFT JOIN departments on departements.id = products.department_id > LEFT JOIN stores on stores.id = products.store_id OR stores.id = > departments.store_id

why not SELECT view INNER JOIN associations WHERE = AND = OR =.

forgive my SQL... that is just an attempt to explain what I need. SQL is definitely not my strong suit.

> However, I am also using an :include option for several associations, > and it seems that joins and include are mutually exclusive. I'd > really had to have to write out all of the associations.

I'll respond after I unravel what that means in my head.

I suppose what this all comes down to, is a way to achieve the necessary SQL through ActiveRecord so I can properly determine the table.column_names to sort on. As I mentioned earlier, I am able to get the needed product items with my doing a find on the sum of ids... so I guess this is less of an association issue, and more of a joins issue ActiveRecord.find so I can get my proper sorting order..

> Any thoughts?

Those are my thoughts. I'm newish and find attempting to help in turn helps me to learn. I hope you don't mind.

Any advise helps! I appreciate your taking the time to think about this.

6

See below…

Thanks!

Trish

Thank you for your response. However, I am refactoring an existing

application, and am trying to remove a bunch of pure SQL that was put

in the code. I believe the best way to do this is with a table

association.

Why do you believe this?

This seems to be the best way to retrieve the needed data in the

proper order. My closest attempt gets me two sets of data (for

department_products and store_products), and then I do a “find” on

their ids, allowing me to do the :order statement as needed.

I’m just not sure why it would be the best. Is it scalable? If you introduce another factor it sounds messy.

One of the big features I want to use out of this is

retrieving the records in the proper order (I’d prefer not to sort

with Ruby after the fact since that would force me to rewrite a bunch

of code for the sorting of the report).

What is the proper order?

This report needs to have (and currently has) multi-column sort

capabilities

Well, are you exposing data with an API? Don’t bake an obscure order into the response. Allow the front end component to do sorting and filtering. Just get the data dump and have the data grid view do that work for you. I think if you return the query as json or xml you can have any view do parse it, right?

However, as this is an existing application (similar in setup to the> example I gave), I cannot remove the model, as there is a table

already associated with it, and the ids are being used.

Gotcha.

Another option that would work is to use a ‘joins’ statement in my

finder method so I can sort products based on Store attributes with

something like this:

FROM products

LEFT JOIN departments on departements.id = products.department_id

LEFT JOIN stores on stores.id = products.store_id OR stores.id =

departments.store_id

why not SELECT view INNER JOIN associations WHERE = AND = OR =.

forgive my SQL… that is just an attempt to explain what I need. SQL

is definitely not my strong suit.

However, I am also using an :include option for several associations,

and it seems that joins and include are mutually exclusive. I’d

really had to have to write out all of the associations.

I’ll respond after I unravel what that means in my head.

I suppose what this all comes down to, is a way to achieve the

necessary SQL through ActiveRecord so I can properly determine the

table.column_names to sort on. As I mentioned earlier, I am able to

get the needed product items with my doing a find on the sum of ids…

so I guess this is less of an association issue, and more of a joins

issue ActiveRecord.find so I can get my proper sorting order…

Okay, I kinda get what your saying. That is why I introduced the SQL view. It’s a good way of returning data from multiple tables that aren’t quite normalized. Which is interesting for me to look into. I’m not sure how the SQL view translates over into AR.

Any thoughts?

Those are my thoughts. I’m newish and find attempting to help in turn helps

me to learn. I hope you don’t mind.

Any advise helps! I appreciate your taking the time to think about

this. No worries. I think we need a diagram. How much time do we have I don’t want to eat up the clock for you :slight_smile:

7

Thank you for your help. As you suggested, I decided to handle the sort on the front end with a jQuery plugin. That resolves my issues with this, and no longer need to worry about creating a complex Active Record call.

Thanks for your help! Trish

8

Sahweet! Hi fives!

I think , in this case, the only thing the model should do is supply the LIMIT.

get(‘5’)

and have the view logic handle pagination, sorting, filtering.

caching the response to accommodate the incremental limits would be an entirely different animal.

Thanks for letting me help!

Some job post on here referenced this as well. Seemed inline with your inquiry…

http://nokogiri.org/README_rdoc.html

This has perked my interest

http://halcyon.rubyforge.org/

Have fun and keep me in the loop.