Searching three (or more) related tables; methods - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Searching three (or more) related tables; methods (/showthread.php?tid=31681) |
Searching three (or more) related tables; methods - El Forum - 06-28-2010 [eluser]packetfox[/eluser] Hello, I have a products, a tags and a vendor table. One Product may have one Vendor and many Tags associated to it. I am trying to implement a proper search method, where the Idea is the User specifies a Keyword(s) into a Field, and after Form submission is presented with a list of Matches from the products table. My Tables: products -id -vendor_id -field1 -field2 -field3 product_tags -id -product_id -tag vendors -id -vendor_name What would be the correct way to let the user search for a keyword, and the System searches the product, the vendor and the tags table? Given the Tables are related via the product_id, How would a proper Active Record Query using Join look like that is performant? Or would it be better to basically make more than 1 query like in this pseudo code: 1. array1 = select id from products where field1 like %keyword%; 2. array2 = select product_id from product_tags where tag like %keyword%; 3. array3 = select product_id from vendors where vendor_name like %keyword% (and products.vendor_id = vendors.id); 4. array4 = merge array1 , array2 and array3, so we end up with a list of product id's 5. select * from products where product_id in array4 6. present matching products to user I've been searching the Forums and found a few suggestions; though I am looking for a performant and robust way i can apply even when searching more than two related tables, without using third party indexing libraries from zend or similar. Also, i would love to be able to use Active Record if at all possible. Many thanks & best regards, D Searching three (or more) related tables; methods - El Forum - 06-28-2010 [eluser]pickupman[/eluser] I believe you would want to join the products & vendors table to your tag table. Since you have many records for tags you could use: Code: $this->db->select('t.*, p.*, v.vendor_name'); |