belipero
1 month ago
252
0
General

Count of two tables considering group by

Posted 1 month ago by belipero

Hi! I'm developing a classic Top 10 table and I'm getting the expected results with the following code:

$top10offerdemandsbyagent = DB::table('offerdemands')
	->leftJoin('users', 'offerdemands.email', '=', 'users.email')
	->select(DB::raw('count(*) as total, users.name, users.avatar'))
	->groupBy('users.name', 'users.avatar')
	->orderBy('total', 'desc')
	->take(10)
	->get()->toArray();

This code is generating the followins SQL statemente:

select
  count(*) as total,
  users.name,
  users.avatar
from
  `offerdemands`
  left join `users` on `offerdemands`.`email` = `users`.`email`
group by
  `users`.`name`,
  `users`.`avatar`
order by
  `total` desc
limit
  10

I need to add an additional table called offerdemandsmatchs which is a child related table 1:M with offerdemands table and count the total records of this child related table and expose that value in selectin order to use it in blade.

Does anyone can help me on this?

Regards

Please sign in or create an account to participate in this conversation.