On Jun 30, 2010, at 3:28 PM, Aurimas Likas wrote:
> I want to ask Im sure a really simple question (at laest for most of you).
> Here is the situation:
>
> I have 3 DB tables: products, tags and products_tags. products_tags has 2
> columns product_id and tag_id (for many-to-many relationship). So the
> question is: how can I select products which have tag1 AND tag2 and tag3 and
> .... tagN? I need a best way in terms of performance. The only way I can
> think of now is to create a query with self joins. The query joins number
> would equal tags number - 1. Is there a better way? Maybe I miss here
> something fundamental (think). My project products need a way to be filtered
> by various tags...
>
> Thanks in advance.
>
> --
> Aurimas L.
I had to do something similar a few years ago. Here is my code, except articles
= products, and keywords = tags:
$pKeywords = array("any", "keywords", "array"); //keyword array sent into
function
$kwSelect = $articles->select ();
$kwSelect->setIntegrityCheck ( false );
$kwSelect->from ( array ('ak' => 'articles_keywords' ), array ('ak.article_id'
) );
$kwSelect->joinInner ( array ('k' => 'keywords' ), 'k.id = ak.keyword_id',
array () );
foreach ( $pKeywords as $kw ) {
$kwSelect->orWhere ( 'k.slug = ?', $kw );
}
$kwSelect->group ( array ('ak.article_id' ) );
if($this->_kwIntersection == true)
$kwSelect->having ( 'COUNT(ak.article_id)=' . count ( $pKeywords ) );
$select->where ( 'a.id IN (?)', new Zend_Db_Expr ( $kwSelect ) );
Not sure if this is the best way to handle your scenario, but it seemed to work
for me. In this example, if _kwIntersection is true, then it's an OR operation,
if it's false, then it's an AND operation. Also, there is another field in the
database called "slug". The "slug" is the URL-safe version of the keyword, so
for example, "Mechanical & Aerospace Engineering" slug would be
"mechanical-aerospace-engineering", but your script might not need a slug. Hope
this helps.
-Henry
