How to count and output the number of users recommended by each user at the same time when outputting the list of users?

the tuijian field is your own recommendation code in the table. Otuijian is the recommendation code that you fill in when you register.
when you output the user list, count and show the number of users" recommendations at the same time. I use the following method
$list =

     db("jianlis")
        ->alias("a")
        ->join("jianlis b","a.tuijian=b.otuijian")
        ->field(["a.*,count(b.id) as count"])
        ->group("a.name")
        ->paginate(15);
       

although user information and recommendation statistics can be found, because there are tens of thousands of items of data, it is too slow to query the data, is there a more convenient sql statement, or the model method of tp5?

Mar.22,2021

there is something wrong with building the table, can't you add one more referral field when you set up the table?
for example, rec_number INT (11) NOT NULL DEFAULT 0 COMMENT 'recommended number',

A recommends that B is registered. See the registration code of A when you register, and then find A-> setInt ('rec_number',1,5) according to A's registration code;

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b2c5ec-2bb14.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b2c5ec-2bb14.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?