我有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.

最好的解决方案是什么？