Hi, sorry I'm having two problems when trying to display search results
of joining 2 tables. I have "Customers" who make "Purchases." Each
Purchase is for a particular "Product."
My model definitions are thus
has_many :products, :through => :purchases
When returning various seach results on the Customer page, I display the
Customer, all of the Purchases that he/she has made, along with the
Product information for that particular purchase. Each Purchase goes on
a new line, as follows:
Customer.name Purchase.serial_number Product.name
Bob 111 ToyA
Joe 777 ToyA
When performing a search on Purchase data, the Customer object returned
is linked to the correct Purchases, and displays correctly.
@customers = Customer.find(:all, :conditions =>
"purchases.serial_number=333", :include => [:purchases, :products])
So, correctly, only "Bob 333 ToyB" is displayed
However, when performing searches on Product data, the Customer object
returned seems to lose all knowledge of what Purchases matched the
@customers = Customer.find(:all, :conditions => "products.name='ToyB'",
:include => [:purchases, :products])
In this case, Customer Joe disappears which is correct, but ALL of Bob's
Purchases (111, 222, 333) are being returned, even though only one (333)
The generated SQL from the log, doesn't give the right rows, even when
pasted into my mySQL query browser. Does this mean my :includes, or
perhaps my Model "has many" declarations are wrong?.
LEFT OUTER JOIN `purchases` ON purchases.customer_id = customers.id
LEFT OUTER JOIN `purchases` products_customers_join ON (`customers`.`id`
LEFT OUTER JOIN `products` ON (`products`.`id` =
So I gave up using :includes, and decided to hand write 2 normal left
joins, bringing together the 3 tables. In my query browser, it worked
perfectly, only returning the one "Bob 333 ToyB" line.
However, this is the 2nd problem. Trying Customer.find_by_sql "SELECT *
FROM `customers` LEFT JOIN `purchases` on purchases.customer_id =
customers.id LEFT JOIN `products` on purchases.product_id = products.id
WHERE product.name='ToyB'" gives some funky results.
It appears that the returned "Customer" object has its data being
populated from the joined "Product" object, so customer.id = (product's
id), customer.name = "ToyB" etc. Strange, I would think Rails would be
able to distinguish the two...
Anyways, I changed the SELECT to `customers`.*, and this time the
customer's id, name, etc were fine. But it STILL wouldn't return the
proper Purchase data (333 only), instead linking to (111,222, and 333).
Sorry for the long post, but any advice on where I/Rails might be going
wrong? Is there any way I can make sure "customer.purchases" only
returns those which match the search criteria, even if my criteria
includes Product data?
Thanks in advance for any help you can provide...