MySQL-How to build such a select query in thinkphp Query language?

Ask how to use thinkphp query language to do the following query:

Table A (classification table)
ID Name
1 L
2 LL
3 lll

Table B (Table of Contents)
ID CID XName
1 2 x
2 1 XX
3 2 XXX
4 1 xxxxx
5 1 xxxxxxx

select a.id,a.name,(select count(id) as counts from b where b.cid=a.id) from a//查询分类下的项目数，没有的显示0，及分类id，名称

return:
ID name counts
1 L 3
2 LL 2
3 LLL 0

Reply content:

Ask how to use thinkphp query language to do the following query:

Table A (classification table)
ID Name
1 L
2 LL
3 lll

Table B (Table of Contents)
ID CID XName
1 2 x
2 1 XX
3 2 XXX
4 1 xxxxx
5 1 xxxxxxx

select a.id,a.name,(select count(id) as counts from b where b.cid=a.id) from a//查询分类下的项目数，没有的显示0，及分类id，名称

return:
ID name counts
1 L 3
2 LL 2
3 LLL 0

$Model = M (' A ');
$Model->field (' A.id,a.name,count (a.id) ')->join (' left join B on a. id = B. Cid ') Group (a.id)->select ();

Using a virtual model for multi-table queries

$table [' table '] = Alias
$table [' a '] = a;//a table
$thble [' b '] = b;//b table
$this->table ($table)->where (' b.cid = a.ID ')->getfield (' A.id,a.name,count (b.id) as counts ');

SELECT a.id, a.name, COUNT(b.id) AS row_number FROM a LEFT JOIN b ON a.id = b.cid GROUP BY a.id;

Focus on:GROUP by a.ID

