count with has_many in rails

Question

I've got an Order and Orderdetails

Orderdetails belongs_to Order

Order has_many Orderdetails

I am trying to convert the following query to ActiveRecord count function

select Count(*) 
from orderdetails A, orders B 
where A.prodid='6' and A.orderid= B.id and B.custid='11'

I tried:

@count = Orderdetail.count(:conditions => "prodid = 6 and order.custid = 11")

However, this gives error:

PGError: ERROR:  syntax error at or near "order"
LINE 1: ...unt_all FROM "orderdetails" WHERE (prodid = 6 and order.cust...

Edit I changed to order*s*

but now i get this error:

ActiveRecord::StatementInvalid: PGError: ERROR: missing FROM-clause entry for table "orders" LINE 1: ...unt_all FROM "orderdetails" WHERE (prodid = 6 and orders.cus...

Answer 1

I think you should thoroughly read some docs on how associations work in rails. Try this guide.

You don't need to write any SQL in :conditions to do what you need.

Answer 2

You need to add :joins => :order', because your condition contains element from orders table (that's why you get error missing FROM-clause), try:

@count = Orderdetail.count(:joins => :order, :conditions => "prodid = 6 and orders.custid = 11")

Also it better (safer) to use array in conditions:

@count = Orderdetail.count(:joins => :order, :conditions => ["prodid = ? and orders.custid = ?", 6, 11])