08-03-2011, 03:46 PM
[eluser]heldrida[/eluser]
Hi,
for the following scheme:
---------------------------------------------
table -> Campaigns
id | name | alias | start_date | end_date
---------------------------------------------
table -> Campaigns_products
id | campaign_id | product_id
---------------------------------------------
table -> Products
id | reference | name | description
---------------------------------------------
table -> Categories
id | name | parent_category_id
---------------------------------------------
table -> Products_categories
id | product_id | category_id
---------------------------------------------
- A product can have many categories;
- A product can have many Campaigns;
My question is, I dont know how to get a product, that can be in many
categories, in a specific campaign:
- The product must be in catA or catB or catC or catD;
- AND it must be of campaignX;
Here's some code http://pastie.org/2316484
I can do this by writting SQL, but I would like to understand how I can accomplish
this trough Datamapper.
On $prod->where_related($cpgn)->get(), I'm getting a product that is campainX, that is not part of the selected category.
Also, is there a way to see the query it generated ? What I do often is to place typos to get the error msgs with the query. From the last querys I saw, I guess I could solve this if I had the "AND_WHERE" or something :T
Thanks a lot for looking!
Hi,
for the following scheme:
---------------------------------------------
table -> Campaigns
id | name | alias | start_date | end_date
---------------------------------------------
table -> Campaigns_products
id | campaign_id | product_id
---------------------------------------------
table -> Products
id | reference | name | description
---------------------------------------------
table -> Categories
id | name | parent_category_id
---------------------------------------------
table -> Products_categories
id | product_id | category_id
---------------------------------------------
- A product can have many categories;
- A product can have many Campaigns;
My question is, I dont know how to get a product, that can be in many
categories, in a specific campaign:
- The product must be in catA or catB or catC or catD;
- AND it must be of campaignX;
Here's some code http://pastie.org/2316484
I can do this by writting SQL, but I would like to understand how I can accomplish
this trough Datamapper.
Code:
The lines 59 to 70
foreach ( $cat As $k => $v )
{
if ( $i == 0 )
{
$prod->where_related($v);
}
else
{
$prod->or_where_related($v);
}
$i++;
}
The lines 80 to 88
$cpgn = new Campaign();
$cpgn->where('id', $cpgn_id)->get();
if ( ! $cpgn->exists() )
{
return FALSE;
}
$prod->where_related($cpgn)->get();
On $prod->where_related($cpgn)->get(), I'm getting a product that is campainX, that is not part of the selected category.
Also, is there a way to see the query it generated ? What I do often is to place typos to get the error msgs with the query. From the last querys I saw, I guess I could solve this if I had the "AND_WHERE" or something :T
Code:
the following query works fine:
SELECT * FROM `products`
INNER JOIN `products_categories`
ON `products_categories`.`product_id` = `products_categories`.`category_id`
INNER JOIN `campaigns_products`
ON `campaigns_products`.`product_id` = `products`.`id`
INNER JOIN `campaigns`
ON `campaigns`.`id` = `campaigns_products`.`campaign_id`
WHERE (
`products_categories`.`category_id` = 1
OR `products_categories`.`category_id` = 3
OR `products_categories`.`category_id` = 4
OR `products_categories`.`category_id` = 5
)
AND `campaigns`.`id` = 1
Thanks a lot for looking!