我有2个称为类别和产品的表,具有这种关系:
category.category_id = products.product_category_id
我想显示2类产品的所有分类!
[
[category 1] =>
[
'category_id' => 1,
'category_title' => 'category_title 1',
[products] => [
'0' => [
'product_category_id' => 1,
'product_id' => 51,
'product_title' => 'product_title 1',
],
'1' => [
'product_category_id' => 1,
'product_id' => 55,
'product_title' => 'product_title 2',
]
]
],
[category 2] =>
[
'category_id' => 2,
'category_title' => 'category_title 2',
[products] => [
'0' => [
'product_category_id' => 2,
'product_id' => 32,
'product_title' => 'product_title 3',
],
'1' => [
'product_category_id' => 2,
'product_id' => 33,
'product_title' => 'product_title 4',
]
]
],
...
]
我Laravel雄辩地说,我可以使用这样的东西:
$categories = Category::with(['products' => function($q) {
$q->limit(2)
}])->get();
但是我没有使用Laravel,我需要纯SQL代码!
我已经试过这段代码:
SELECT
CT.category_id,
PT.product_category_id,
CT.category_title,
PT.product_id,
PT.product_title
FROM
categories CT
LEFT JOIN(
SELECT
*
FROM
products
LIMIT 2
) AS PT
ON
CT.category_id = PT.product_category_id
WHERE
CT.category_lang = 'en'
但是这段代码有问题!看来,MYSQL首先在products表中获得了前两行,然后尝试从类别向那两行进行LEFT JOIN!这会导致产品返回null值(如果我删除LIMIT,效果很好,但我需要LIMIT)
我也测试了此代码:(更新)
SELECT
CT.category_id,
PT.product_category_id,
CT.category_title,
PT.product_id,
PT.product_title
FROM
categories CT
LEFT JOIN(
SELECT
*
FROM
products
WHERE
product_category_id = CT.category_id
LIMIT 5
) AS PT
ON
CT.category_id = PT.product_category_id
WHERE
CT.category_lang = 'en'
但是我收到了这个错误:
1054 – Unknown column ‘CT.category_id’ in ‘where clause’
我无法在子查询中访问CT.category_id.
最好的解决方案是什么?