# Problem displaying joined table search results **URL:** \ **Category:** Rails\ **Created:** [April 14, 2008, 12:16pm UTC](https://www.ruby-forum.com/t/problem-displaying-joined-table-search-results/134710 "2008-04-14T12:16:04Z")\ **Posts on this page:** 1\ **Page:** 1
**Author:** ![brentsoderberg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/8edcca/32.png) [@brentsoderberg](https://www.ruby-forum.com/u/brentsoderberg)\ **Post date:** [April 14, 2008, 12:16pm UTC](https://www.ruby-forum.com/t/problem-displaying-joined-table-search-results/134710/1 "2008-04-14T12:16:04Z")
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…