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
class Customer
has_many :purchases
has_many :products, :through => :purchases
class Purchase
belongs_to :product
belongs_to :customer
class Product
has_many :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
222 ToyA
333 ToyB
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
criteria.
@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)
matches.
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?.
SELECT …
FROM customers
LEFT OUTER JOIN purchases ON purchases.customer_id = customers.id
LEFT OUTER JOIN purchases products_customers_join ON (customers.id
= products_customers_join.customer_id)
LEFT OUTER JOIN products ON (products.id =
products_customers_join.product_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…