我有三张table
categories, product_categories , products
这里是categories hasMany product_categories
和product_categories hasMany products
。我正在尝试获取类别下的最新20个产品。
我在下面写了查询
$categories = \Cake\ORM\TableRegistry::getTableLocator()->get( 'Categories' )->find()
->contain(
[
'ProductCategories.Products' => function($q){
return $q->limit(20);
}
]
)
通过这个查询,我得到了每个ProductCategories的20个产品。我只需要20个产品的类别,而不是每个ProductCategories。我该如何解决这个问题?
@ndm注解后,我尝试了以下库https://github.com/icings/partitionable
在CategoriesTable表中我写了下面的代码
$this
->partitionableBelongsToMany('RecentProducts')
->setClassName('Products')
->setThrough('ProductCategories')
->setLimit(9)
->setSort([
'RecentProducts.created' => 'DESC',
])
;
我的查询如下所示
SELECT
ProductCategories.id AS RecentProducts_CJoin__id,
ProductCategories.name AS RecentProducts_CJoin__name,
ProductCategories.slug AS RecentProducts_CJoin__slug,
ProductCategories.img AS RecentProducts_CJoin__img,
ProductCategories.category_id AS RecentProducts_CJoin__category_id,
ProductCategories.sub_category_id AS RecentProducts_CJoin__sub_category_id,
ProductCategories.created AS RecentProducts_CJoin__created,
ProductCategories.modified AS RecentProducts_CJoin__modified,
RecentProducts.id AS RecentProducts__id,
RecentProducts.name AS RecentProducts__name,
RecentProducts.item_id AS RecentProducts__item_id,
RecentProducts.product_category_id AS RecentProducts__product_category_id,
RecentProducts.crawl_uniquekey AS RecentProducts__crawl_uniquekey,
RecentProducts.website_name AS RecentProducts__website_name,
RecentProducts.original_price AS RecentProducts__original_price,
RecentProducts.discount_amount AS RecentProducts__discount_amount,
RecentProducts.description AS RecentProducts__description,
RecentProducts.img_url AS RecentProducts__img_url,
RecentProducts.total_review AS RecentProducts__total_review,
RecentProducts.review_average AS RecentProducts__review_average,
RecentProducts.price AS RecentProducts__price,
RecentProducts.percentage AS RecentProducts__percentage,
RecentProducts.expiry AS RecentProducts__expiry,
RecentProducts.price_after_discount AS RecentProducts__price_after_discount,
RecentProducts.price_zero_padded AS RecentProducts__price_zero_padded,
RecentProducts.created AS RecentProducts__created,
RecentProducts.modified AS RecentProducts__modified
FROM
products RecentProducts
INNER JOIN product_categories ProductCategories ON ProductCategories.id = (
RecentProducts.product_category_id
)
WHERE
(
ProductCategories.category_id in (1, 2)
AND ProductCategories.id in (
SELECT
__ranked__RecentProducts.id AS id
FROM
(
SELECT
ProductCategories.id AS RecentProducts_CJoin__id,
ProductCategories.name AS RecentProducts_CJoin__name,
ProductCategories.slug AS RecentProducts_CJoin__slug,
ProductCategories.img AS RecentProducts_CJoin__img,
ProductCategories.category_id AS RecentProducts_CJoin__category_id,
ProductCategories.sub_category_id AS RecentProducts_CJoin__sub_category_id,
ProductCategories.created AS RecentProducts_CJoin__created,
ProductCategories.modified AS RecentProducts_CJoin__modified,
ProductCategories.id AS id,
(
ROW_NUMBER() OVER (
PARTITION BY ProductCategories.category_id
ORDER BY
RecentProducts.created DESC
)
) AS __row_number,
RecentProducts.id AS RecentProducts__id,
RecentProducts.name AS RecentProducts__name,
RecentProducts.item_id AS RecentProducts__item_id,
RecentProducts.product_category_id AS RecentProducts__product_category_id,
RecentProducts.crawl_uniquekey AS RecentProducts__crawl_uniquekey,
RecentProducts.website_name AS RecentProducts__website_name,
RecentProducts.original_price AS RecentProducts__original_price,
RecentProducts.discount_amount AS RecentProducts__discount_amount,
RecentProducts.description AS RecentProducts__description,
RecentProducts.img_url AS RecentProducts__img_url,
RecentProducts.total_review AS RecentProducts__total_review,
RecentProducts.review_average AS RecentProducts__review_average,
RecentProducts.price AS RecentProducts__price,
RecentProducts.percentage AS RecentProducts__percentage,
RecentProducts.expiry AS RecentProducts__expiry,
RecentProducts.price_after_discount AS RecentProducts__price_after_discount,
RecentProducts.price_zero_padded AS RecentProducts__price_zero_padded,
RecentProducts.created AS RecentProducts__created,
RecentProducts.modified AS RecentProducts__modified
FROM
products RecentProducts
INNER JOIN product_categories ProductCategories ON ProductCategories.id = (
RecentProducts.product_category_id
)
WHERE
ProductCategories.category_id in (1, 2)
) __ranked__RecentProducts
WHERE
__ranked__RecentProducts.__row_number <= 9
)
)
ORDER BY
RecentProducts.created DESC
我的查询生成器代码
$categories = \Cake\ORM\TableRegistry::getTableLocator()->get( 'Categories' )->find()
->contain(
[
'CategoryKeywords',
'RecentProducts'
]
)
这里我得到了18个项目,但期望是9。
1条答案
按热度按时间uwopmtnx1#
假设有以下关系式
类别有多个产品类别
产品类别有多个产品
产品属于产品类别