How do you go handle finding records with criteria that depends on multiple tables? An example: Customer has_many :orders Order has_many :items belongs_to :customer Item belongs_to :order Say I want to retreive all customers that have ordered a given item. The only way I know to do this is find_by_sql. Then I can add a find_by_ordered_items method to Customer and not have to mess with sql again. But then I loose the ability to use eager loads. Is there better way to handle this situation? -- Jack Christensen jackc-/SOt/BrQZzOj3I+7jmQ39gC/G2K4zDHf@public.gmane.org
Jack Christensen <jackc-/SOt/BrQZzMOf2zXYvRtkodd74u8MsAO@public.gmane.org> writes:> Customer > has_many :orders > > Order > has_many :items > belongs_to :customer > > Item > belongs_to :orderIf that''s really your models, I''d like to point out that belongs_to is the model with the foreign key. I don''t think you want an order_id in your items table. I''d think what you''d want is that Order :has_and_belongs_to_many items and vice versa.> Say I want to retreive all customers that have ordered a given item. The > only way I know to do this is find_by_sql.With habtm you could do: customers = item.find(id).orders.collect { |o| o.customer }.uniq That''s untested, but makes me happy in a lispy, functional kind of way.> But then I loose the ability to use eager loads.I''m not smart about eager loading, but I''d guess the above was kinda expensive. It''d be better to just collect the o.customer.id and find the uniq from that. I suspect uniq on an Array of numbers is much faster than uniq on an Array of Customers. -- doug-jGAhs73c5XxeoWH0uzbU5w@public.gmane.org
Doug Alcorn wrote:> Jack Christensen <jackc-/SOt/BrQZzMOf2zXYvRtkodd74u8MsAO@public.gmane.org> writes: > > >>Customer >> has_many :orders >> >>Order >> has_many :items >> belongs_to :customer >> >>Item >> belongs_to :order > > > If that''s really your models, I''d like to point out that belongs_to is > the model with the foreign key. I don''t think you want an order_id in > your items table. I''d think what you''d want is that Order > :has_and_belongs_to_many items and vice versa.I don''t think so. Order items have to belong to only one order. If they are shared between orders it is impossible to change price, description etc. without affecting all the existing orders. And if one order contains 1000 items, you get 1000 rows in your habtm table. My data structure looks like this: Customer has_many :orders Order has_many :order_items belongs_to :customer OrderItem belongs_to :order belongs_to :product attributes quantity, price, description Product: has_many :order_items belongs_to :category attributes price, description, image, text, available_quantity The product attributes can be changed without affecting the price or description of the items of the existing orders.
On Apr 30, 2005, at 9:10 AM, Andreas Schwarz wrote:> I don''t think so. Order items have to belong to only one order. If > they are shared between orders it is impossible to change price, > description etc. without affecting all the existing orders. And if one > order contains 1000 items, you get 1000 rows in your habtm table.How is your OrderItem table any different?> My data structure looks like this: > > Customer > has_many :orders > > Order > has_many :order_items > belongs_to :customer > > OrderItem > belongs_to :order > belongs_to :product > attributes quantity, price, description > > Product: > has_many :order_items > belongs_to :category > attributes price, description, image, text, available_quantity > > The product attributes can be changed without affecting the price or > description of the items of the existing orders.I could be wrong, but I think your use case is *usually* handled by adding attributes the habtm join table, which in this case would be orders_products. The attributes of Product that are changeable per order would be held there. Your creation of an explicit OrderItem table isn''t really needed, AFAICT. Best, Erich
Erich Ocean wrote:> On Apr 30, 2005, at 9:10 AM, Andreas Schwarz wrote: > >> I don''t think so. Order items have to belong to only one order. If >> they are shared between orders it is impossible to change price, >> description etc. without affecting all the existing orders. And if one >> order contains 1000 items, you get 1000 rows in your habtm table. > > > How is your OrderItem table any different?It has a quantity field.>> My data structure looks like this: >> >> Customer >> has_many :orders >> >> Order >> has_many :order_items >> belongs_to :customer >> >> OrderItem >> belongs_to :order >> belongs_to :product >> attributes quantity, price, description >> >> Product: >> has_many :order_items >> belongs_to :category >> attributes price, description, image, text, available_quantity >> >> The product attributes can be changed without affecting the price or >> description of the items of the existing orders. > > > I could be wrong, but I think your use case is *usually* handled by > adding attributes the habtm join table, which in this case would be > orders_products. The attributes of Product that are changeable per order > would be held there. Your creation of an explicit OrderItem table isn''t > really needed, AFAICT.I prefer a seperate model, so that I can also add items without an associated product record.
On Apr 30, 2005, at 9:50 AM, Andreas Schwarz wrote:> > Erich Ocean wrote: >> >> On Apr 30, 2005, at 9:10 AM, Andreas Schwarz wrote: >>> >>> I don''t think so. Order items have to belong to only one order. If >>> they are shared between orders it is impossible to change price, >>> description etc. without affecting all the existing orders. And if >>> one order contains 1000 items, you get 1000 rows in your habtm >>> table. >> >> How is your OrderItem table any different? > > It has a quantity field.I''m not following you here: why couldn''t this be a column in the implicit ( orders_products ) join table?>> I could be wrong, but I think your use case is *usually* handled by >> adding attributes the habtm join table, which in this case would be >> orders_products. The attributes of Product that are changeable per >> order would be held there. Your creation of an explicit OrderItem >> table isn''t really needed, AFAICT. > > I prefer a seperate model, so that I can also add items without an > associated product record.This is easily handled by creating a single Product record that represents "Product Unknown", and using that as your foreign key for all cases where you don''t know have a Product record. Do you have any other reasons for maintaining the join table by hand, instead of using Rails automatic support? Best, Erich
On Apr 30, 2005, at 10:07 AM, Erich Ocean wrote:> On Apr 30, 2005, at 9:50 AM, Andreas Schwarz wrote: >> Erich Ocean wrote: >>> I could be wrong, but I think your use case is *usually* handled >>> by adding attributes the habtm join table, which in this case >>> would be orders_products. The attributes of Product that are >>> changeable per order would be held there. Your creation of an >>> explicit OrderItem table isn''t really needed, AFAICT. >>> >> >> I prefer a seperate model, so that I can also add items without an >> associated product record.OrderItem models a vital aspect of your domain. To give that up -- hiding it in an implicit join table with no class to represent it and no methods to enact its behavior -- severely cripples the expressiveness of your model. You''ll end up scattering OrderItem behavior through Order and Item. It''ll work, but it''s a mess just waiting for that refactoring lightbulb to shine.> This is easily handled by creating a single Product record that > represents "Product Unknown", and using that as your foreign key > for all cases where you don''t know have a Product record.Use NULL.> Do you have any other reasons for maintaining the join table by > hand, instead of using Rails automatic support?A many-to-many association is poorly represented by has_and_belongs_to_many if it is *anything* more than two foreign keys sitting in a join table. If the association has attributes of its own, you''ll likely benefit from extracting the explicit model. It''s clearer and easier to work with in your code. If the association has *any behavior at all*, extracting it from its has_and_belongs_to_many chains is the only humane option. Best, jeremy
Jeremy Kemper wrote:> On Apr 30, 2005, at 10:07 AM, Erich Ocean wrote: > >> On Apr 30, 2005, at 9:50 AM, Andreas Schwarz wrote: >> >>> Erich Ocean wrote: >>> >>>> I could be wrong, but I think your use case is *usually* handled by >>>> adding attributes the habtm join table, which in this case would be >>>> orders_products. The attributes of Product that are changeable per >>>> order would be held there. Your creation of an explicit OrderItem >>>> table isn''t really needed, AFAICT. >>>> >>> >>> I prefer a seperate model, so that I can also add items without an >>> associated product record. > > > OrderItem models a vital aspect of your domain. > > To give that up -- hiding it in an implicit join table with no class to > represent it and no methods to enact its behavior -- severely cripples > the expressiveness of your model.Yes, that''s exactly how I think about it (though I couldn''t have expressed it as well as that).
Joins are a premature optimisation in 99% of all cases i have seen people use them. Finish your application without a single join using AR methods. A db will be bored to tears with less then a thousand odd queries per second as long as you keep to indexes and pks which is what AR does. Furthermore joins often kill good design by moving code which should be in a line item model to the cart model for example. Put joins back in the toolbox where they belong. Get them out when you have performance problems. On 4/29/05, Jack Christensen <jackc-/SOt/BrQZzMOf2zXYvRtkodd74u8MsAO@public.gmane.org> wrote:> How do you go handle finding records with criteria that depends on > multiple tables? > > An example: > > Customer > has_many :orders > > Order > has_many :items > belongs_to :customer > > Item > belongs_to :order > > Say I want to retreive all customers that have ordered a given item. The > only way I know to do this is find_by_sql. Then I can add a > find_by_ordered_items method to Customer and not have to mess with sql > again. But then I loose the ability to use eager loads. Is there better > way to handle this situation? > > -- > Jack Christensen > jackc-/SOt/BrQZzOj3I+7jmQ39gC/G2K4zDHf@public.gmane.org > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Tobi http://www.snowdevil.ca - Snowboards that don''t suck http://www.hieraki.org - Open source book authoring http://blog.leetsoft.com - Technical weblog