Searching three (or more) related tables; methods

[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

[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');
$this->db->join('products p','p.id = t.product_id');
$this->db->join('vendors v' ,'v.id = p.vendor_id');
$this->db->like('p.field1', $keyword);
$this->db->or_like('p.field2', $keyword);
$this->db->or_like('p.field3', $keyword);
$this->db->or_like('t.tag', $keyword);
$this->db->or_like('v.vendor_name', $keyword);
$this->db->get('product_tags t');