# Unknown column 'quotes.organisation\_id' in 'where clause': SELECT \* FROM \`quotes\` WHERE (\`quotes\`.or

**URL:** <https://www.ruby-forum.com/t/unknown-column-quotes-organisation-id-in-where-clause-select-from-quotes-where-quotes-or/178133>\
**Category:** Rails\
**Created:** [October 29, 2009, 4:14pm UTC](https://www.ruby-forum.com/t/unknown-column-quotes-organisation-id-in-where-clause-select-from-quotes-where-quotes-or/178133 "2009-10-29T16:14:01Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![inertia](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/d07c76/32.png) [@inertia](https://www.ruby-forum.com/u/inertia)\
**Post date:** [October 29, 2009, 4:14pm UTC](https://www.ruby-forum.com/t/unknown-column-quotes-organisation-id-in-where-clause-select-from-quotes-where-quotes-or/178133/1 "2009-10-29T16:14:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![inertia](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/d07c76/32.png) [@inertia](https://www.ruby-forum.com/u/inertia)\
**Post date:** [October 29, 2009, 4:24pm UTC](https://www.ruby-forum.com/t/unknown-column-quotes-organisation-id-in-where-clause-select-from-quotes-where-quotes-or/178133/2 "2009-10-29T16:24:08Z")

</div>

Anthony M. 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](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![inertia](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/d07c76/32.png) [@inertia](https://www.ruby-forum.com/u/inertia)\
**Post date:** [October 29, 2009, 4:50pm UTC](https://www.ruby-forum.com/t/unknown-column-quotes-organisation-id-in-where-clause-select-from-quotes-where-quotes-or/178133/3 "2009-10-29T16:50:55Z")

</div>

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)

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

---

<div class="post-metadata">

**Author:** ![inertia](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/d07c76/32.png) [@inertia](https://www.ruby-forum.com/u/inertia)\
**Post date:** [October 29, 2009, 5:06pm UTC](https://www.ruby-forum.com/t/unknown-column-quotes-organisation-id-in-where-clause-select-from-quotes-where-quotes-or/178133/4 "2009-10-29T17:06:43Z")

</div>

Marnen Laibow-Koser wrote:

> Anthony M. 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

---

<div class="post-metadata">

**Author:** ![inertia](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/d07c76/32.png) [@inertia](https://www.ruby-forum.com/u/inertia)\
**Post date:** [October 29, 2009, 5:01pm UTC](https://www.ruby-forum.com/t/unknown-column-quotes-organisation-id-in-where-clause-select-from-quotes-where-quotes-or/178133/5 "2009-10-29T17:01:21Z")

</div>

Anthony M. 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](http://www.marnen.org)  
removed\_email\_address@domain.invalid