Join Table On Self using Doctrine (DQL)

Question

I'm attempting to join a table on itself using doctrine's DQL. The scenario is:

I have a table of product attribute values at is linked to a products table via a reference table. The product attribute values are to serve as filters on the products, therefore, the products attribute values table needs to be joined back onto itself to filter on more than one attribute.

My method is as follows:

public static function getProductsByCategoryCode($productCategoryCode, $currencyCode, Zend_Session_Namespace $filters = null)
{
    $products = Doctrine_Query::create()
        ->from('Products p')
        ->leftJoin('p.ProductCategories pc')
        ->leftJoin('p.ProductPrices pp')
        ->leftJoin('pp.Currencies c')
        ->where('pc.product_category_code = '. $productCategoryCode)
        ->addWhere('c.currency_code = "'. $currencyCode . '"');

    if($filters !== null)
    {   
        $i = 0;

        foreach($filters as $index => $value)
        {
            $alias = 'pav_' . (string)$i;

            $products
                ->leftJoin('p.ProductAttributeValues ' . $alias)
                ->addWhere($alias . '.product_attribute_code = "'. $index . '"')
                ->addWhere($alias . '.value = "'. $value . '"');

            $i++;
        }
    }

    die($products->getSql());

    return $products->execute();
}

The problem is that the resulting SQL obtained from the getSql() call has duplicate alias' for the product attribute value links table (MySQL error: #1066 - Not unique table/alias: 'p6'). Presumably, this is due to the reference relationship (product attribute value links) between products and product attribute values being re-used.

The generated SQL is:

SELECT 
p.product_code AS p__product_code, 
p.name AS p__name, 
p.description AS p__description, 
p2.product_category_code AS p2__product_category_code, 
p2.name AS p2__name, 
p2.category_route AS p2__category_route, 
p2.parent_product_category_code AS p2__parent_product_category_code, 
p2.lft AS p2__lft, 
p2.rgt AS p2__rgt, 
p2.level AS p2__level, 
p4.currency_code AS p4__currency_code, 
p4.product_code AS p4__product_code, 
p4.net_value AS p4__net_value, 
c.currency_code AS c__currency_code, 
p5.product_attribute_value_id AS p5__product_attribute_value_id, 
p5.product_attribute_code AS p5__product_attribute_code, 
p5.value AS p5__value, 
p7.product_attribute_value_id AS p7__product_attribute_value_id,
p7.product_attribute_code AS p7__product_attribute_code, 
p7.value AS p7__value 
FROM 
products p 
LEFT JOIN 
product_category_links p3 ON p.product_code = p3.product_code 
LEFT JOIN 
product_categories p2 ON p2.product_category_code = p3.product_category_code 
LEFT JOIN 
product_prices p4 ON p.product_code = p4.product_code 
LEFT JOIN 
currencies c ON p4.currency_code = c.currency_code 
LEFT JOIN 
product_attribute_value_links p6 ON p.product_code = p6.product_code 
LEFT JOIN 
product_attribute_values p5 ON 
p5.product_attribute_value_id = p6.product_attribute_value_id 
LEFT JOIN 
product_attribute_value_links p6 ON p.product_code = p6.product_code 
LEFT JOIN 
product_attribute_values p7 ON 
p7.product_attribute_value_id = p6.product_attribute_value_id 
WHERE 
p2.product_category_code = 10100 
AND 
c.currency_code = "GBP" 
AND 
p5.product_attribute_code = "BRAND" 
AND 
p5.value = "Advanced Hair Gear" 
AND 
p7.product_attribute_code = "SIZE" AND p7.value = "238 ml"

As you can see, P6 appears as an alias twice.

Does anybody know the correct method for achieving this using DQL?

Many thanks in advance.

Answer 1

what does the final SQL statement look like?

Answer 2

I don't know if there is a simpler solution, but you should be able to just specify the joins yourself:

        ->leftJoin('p.ProductAttributeValuesLinks ' . $alias1)
        ->leftJoin($alias1 '.ProductAttributeValue ' . $alias2)

if you set up something like the following on your link table:

  relations:
    Product:
      class: Products
      foreignType: many
    ProductAttributeValue:
      class: ProductAttributeValues
      foreignType: many