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

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 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