Rails - Oct 2009 - Unknown column 'quotes.organisation_id' in 'where clause': SELECT * FROM `quotes` WHERE (`quotes`.organisation_id = 1036)

Hi,

    I have a small app that needs to keep track of quotes, and
organisations. An organisation can either be the unit who finally
receives the goods (the end_user) or and intermediary (customer) (and in
some cases both)

    An Organisation will (hopefully :) ) have many quotes; and a quote
can belong to an organisation, either as a customer or as an end_user or
both.

     So the quotes table has fields customer_id:integer, and
end_user_id:integer.

    The Organisation class has "has_many :quotes" and the Quotes class
has "belongs_to :customer, :class_name =>
''Organisation''" and
"belongs_to :end_user, :class_name =>
''Organisation''".

    I now want to get all quotes for a given organisation. If I do
Organisation.find(1).quotes I get:

Unknown column ''quotes.organisation_id'' in ''where
clause'': SELECT * FROM
`quotes` WHERE (`quotes`.organisation_id = 1)

    So I end up doing:
    @quotes = []
    @org = Organisation.find(1)
    Quote.find(:all, :conditions=>["customer_id = ? OR end_user_id =
?",
@org.id, @org.id]).each {|q| @quotes << q}

    Which seems very un-Ruby.

    What''s the best way to go about this?

Thanks

Anthony

Anthony Metcalf wrote:
> Hi,
> 
>     I have a small app that needs to keep track of quotes, and
> organisations. An organisation can either be the unit who finally
> receives the goods (the end_user) or and intermediary (customer) (and in
> some cases both)
> 
>     An Organisation will (hopefully :) ) have many quotes; and a quote
> can belong to an organisation, either as a customer or as an end_user or
> both.
> 
>      So the quotes table has fields customer_id:integer, and
> end_user_id:integer.
> 
>     The Organisation class has "has_many :quotes" and the Quotes
class
> has "belongs_to :customer, :class_name =>
''Organisation''" and
> "belongs_to :end_user, :class_name =>
''Organisation''".
> 
>     I now want to get all quotes for a given organisation. If I do
> Organisation.find(1).quotes I get:
> 
> Unknown column ''quotes.organisation_id'' in
''where clause'': SELECT * FROM
> `quotes` WHERE (`quotes`.organisation_id = 1)
> 
>     So I end up doing:
>     @quotes = []
>     @org = Organisation.find(1)
>     Quote.find(:all, :conditions=>["customer_id = ? OR end_user_id
= ?",
> @org.id, @org.id]).each {|q| @quotes << q}
> 
>     Which seems very un-Ruby.
> 
>     What''s the best way to go about this?
Fix your Organisation associations.  Because Organisation has_many 
:quotes, Organisation#quotes will try to look up quotes.organisation_id 
(it doesn''t look in the Quotes class).  So you need something like
class Organisation
  has_many :quotes, :foreign_key => ''customer_id''
  has_many :quotes, :foreign_key => ''end_user_id''
end

But I''d argue that this design is a bit smelly.  I can offer some 
suggestions for improvement, but before I do, I''d like to know one 
thing: you say a Quote can belong to an Organisation either as a 
customer or end_user or both.  If both, can the two associations be to 
different Organisations?
> 
> Thanks
> 
> Anthony
Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

Marnen Laibow-Koser wrote:
> Anthony Metcalf wrote:
>   
>>     
>
> Fix your Organisation associations.  Because Organisation has_many 
> :quotes, Organisation#quotes will try to look up quotes.organisation_id 
> (it doesn''t look in the Quotes class).  So you need something like
> class Organisation
>   has_many :quotes, :foreign_key => ''customer_id''
>   has_many :quotes, :foreign_key => ''end_user_id''
> end
>   
Thanks Marnen, that''s the bit I was missing.

I now don''t get an error, but I also only get a query for:

SELECT * FROM `quotes` WHERE (`quotes`.end_user_id = 22)

as though rails only sees the second of the has_many statements.
> But I''d argue that this design is a bit smelly.  I can offer some 
> suggestions for improvement, but before I do, I''d like to know one
> thing: you say a Quote can belong to an Organisation either as a 
> customer or end_user or both.  If both, can the two associations be to 
> different Organisations?
>   
Any advice is appreciated! To answer your question, they both need to be
populated, so in the case where they are the same (i.e we sell direct to
the customer) we make them the same, and when we sell through a third
party, they are different.

Anthony

Anthony Metcalf wrote:
> Marnen Laibow-Koser wrote:
>> end
>>   
> 
> Thanks Marnen, that''s the bit I was missing.
> 
> I now don''t get an error, but I also only get a query for:
> 
> SELECT * FROM `quotes` WHERE (`quotes`.end_user_id = 22)
That may well be.  It may be necessary to use :as in the has_many as if 
the association were polymorphic.
> 
> as though rails only sees the second of the has_many statements.
> 
>> But I''d argue that this design is a bit smelly.  I can offer
some
>> suggestions for improvement, but before I do, I''d like to know
one
>> thing: you say a Quote can belong to an Organisation either as a 
>> customer or end_user or both.  If both, can the two associations be to 
>> different Organisations?
>>   
> Any advice is appreciated! To answer your question, they both need to be
> populated, so in the case where they are the same (i.e we sell direct to
> the customer) we make them the same, and when we sell through a third
> party, they are different.
OK.  Let me think some more about an appropriate design, then.  I''m 
fairly sure that introducing a join table would be helpful, but I need 
to think further about how to model the associations in a way that
isn''t
too generic to be meaningful.
> 
> Anthony
Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

Marnen Laibow-Koser wrote:
> Anthony Metcalf wrote:
>   
>
> That may well be.  It may be necessary to use :as in the has_many as if 
> the association were polymorphic.
>
>   
I''ll have a play.
>
> OK.  Let me think some more about an appropriate design, then. 
I''m
> fairly sure that introducing a join table would be helpful, but I need 
> to think further about how to model the associations in a way that
isn''t
> too generic to be meaningful.
>
>   
Thanks Marnen, that is much appreciated.

Anthony