Hi eveyone.
This is for a shop. The problem is retrieving the products for a given
category.
Categories can have many products and products can belong to many
categories.
Categories are stored in a tree structure, with parent_id, lft and
rght fields.
I have a table called categories_products which has the fields
category_id and product_id to match the products to their categories.
In the categories model:
var $actsAs = array('Tree');
var $hasAndBelongsToMany = array('Product');
In the products model:
var $hasAndBelongsToMany = array('Category');
Saving, editing all that works great, the problem is retrieving the
products for a category:
When the user clicks on a category, I want to display all the products
of it and all its sub categories.
I also want to paginate the results.
so, as an example:
- Home (lft = 1, rght = 8)
- - Kitchen (lft = 2, rght = 7)
- - - Knives (lft = 3, rght = 6)
- - - Forks (lft = 4, rght = 5)
- - Bathroom (lft = 8, rght = 13)
- - - Soap (lft = 9, rght = 12)
- - - Toilet cleaner (lft = 10, rght = 11)
- - Gifts (lft = 14, rght = 19)
- - - Aromatherapy (lft = 15, rght = 18)
- - - Flowers (lft = 16, rght = 17)
- Garden (lft = 20, rght = 25)
- - tools (lft = 21, rght = 24)
- - mowers (lft = 22, rght = 23)
- Office (lft = 26, rght = 31)
- - pens (lft = 27, rght = 30)
- - printers (lft = 28, rght = 29)
So the product "Minty Soap" belongs to 2 gategories Home>Bathroom>Soap
and Home>Gifts>Aromatherapy
If we click on "Home", it should display all the products under home
and its sub categories, including "Minty Soap" - But it should only
display Minty soap once, not twice.
The below code returns the right products, but the problem is that it
returns the same product twice if it belongs to 2 matched categories.
I only want each product to be displayed once.
$joins = array();
$joins[0] = array(
'table' => 'categories_products',
'alias' => 'CategoriesProduct',
'type' => 'inner',
'conditions'=> array('CategoriesProduct.product_id
= Product.id')
);
$joins[1] = array(
'table' => 'categories',
'alias' => 'Category',
'type' => 'inner',
'conditions'=> array(
'Category.id =
CategoriesProduct.category_id',
)
);
// if the lft value of the category of the product is
between the lft and rght values of the parent category (the one the
user clicked on) then this product should be displayed
$joins[1]['conditions']['Category.lft BETWEEN ? AND ?'] =
array($cat['Category']['lft'],$cat['Category']['rght']);
$this->paginate = array(
'limit' => 6,
'order' => array(
'Product.price' => 'asc',
),
'joins' => $joins
);
$products = $this->paginate('Product');
Can anybody help me with this please? Thanks very much
Phil
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
