[eluser]goddestroyer[/eluser]
Wanwizard...
I'm a huge fan of the datamapper, used it in every project I can for years. I am encountering my first issue that cannot be easily solved be referencing any of the documentation, and i know the solution is just in reach and must be so easy....
I would REALLY APPRECIATE some help with this. Bear with me here, this is going to be a long post, but the real issue here is a simple one, I just have to set up the background info...
summary description:
I know how to include_related join fields, but i simply need a way to alias the same join table twice.
I have a Product model defined as:
Code:
var $has_many = array(..., 'productspecification', ... );
and ProductSpecification model defined as:
Code:
var $has_one = array(...,'product','specification','specificationsection');
The task is to query for a set of products that have two specific relations to ProductSpecifications:
(1) WHERE ProductSpecification.specification = A
AND ProductSpecification.specificationsection = B
AND ProductSpecification.value = X
AND
(2) ProductSpecification.specification = A2
AND ProductSpecification.specificationsection = B
AND ProductSpecification.value = Y
the current code that got me closest is:
Code:
$p->include_related('productspecification', 'value', 'clear_aperture_mm');
$p->group_start();
$p->where_related_productspecification('specification_id', 19);
$p->where_related_productspecification('specificationsection_id', 3);
$p->where_in_related_productspecification('value', $args['clear_aperture_mm']);
$p->group_end();
$p->include_related('productspecification', 'value', 'magnification_high');
$p->group_start();
$p->where_related_productspecification('specification_id', 4);
$p->where_related_productspecification('specificationsection_id', 3);
$p->where_in_related_productspecification('value', $args['magnification_high']);
$p->group_end();
This produces a raw sql query like the following:
Code:
SELECT `products`.*, `productspecifications`.`value` AS clear_aperture_mm_value,
`productspecifications`.`value` AS magnification_high_value
FROM (`products`)
LEFT OUTER JOIN
`productspecifications` productspecifications
ON `products`.`id` = `productspecifications`.`product_id`
LEFT OUTER JOIN `classifications_products`
classifications_products ON `products`.`id` = `classifications_products`.`product_id`
WHERE
(
`productspecifications`.`specification_id` = 19
AND
`productspecifications`.`specificationsection_id` = 3
AND `productspecifications`.`value` IN (40)
)
AND (
`productspecifications`.`specification_id` = 4
AND
`productspecifications`.`specificationsection_id` = 3
AND `productspecifications`.`value` IN (0)
)
Except this will never return results because its asking for both a row that has `productspecifications`.`specification_id` = 19 and at the same time `productspecifications`.`specification_id` = 4.
If i was writing raw sql, this is exactly what i would need:
Code:
SELECT `products`.*, productspecifications1.`value` AS clear_aperture_mm_value,
productspecifications2.`value` AS magnification_high_value
FROM (`products`)
LEFT OUTER JOIN
`productspecifications` productspecifications1
ON `products`.`id` = `productspecifications`.`product_id`
LEFT OUTER JOIN
`productspecifications` productspecifications2
ON `products`.`id` = `productspecifications`.`product_id`
LEFT OUTER JOIN `classifications_products`
classifications_products ON `products`.`id` = `classifications_products`.`product_id`
WHERE
(
productspecifications1.`specification_id` = 19
AND
productspecifications1.`specificationsection_id` = 3
AND productspecifications1.`value` IN (40)
)
AND (
productspecifications2.`specification_id` = 4
AND
productspecifications2.`specificationsection_id` = 3
AND productspecifications2.`value` IN (0)
)
As you can see the
only difference is i need each product specification comparison to be its own seperate join, with its own alias. I've read every bit of documentation and spent a good deal of time googling this and i simply can't solve it yet. of course, its for a very large corporate client with deadlines encroaching...
the productspecifications table has columns of: id, product_id, specification_id, specificationsection_id, value, all integers.
please help!
thanks
