sineld Hocam oncelikle cevapiniz icin tesekkurler.
$users_list=User::with('results','user_profile','city')
->withSum('results','total_results')
->withCount([
'results as success' => function (Builder $query) {
$query->where('results.result','>','0')->where('results.executed','=',1);
},
])
->withCount([
'results as error' => function (Builder $query) {
$query->where('results.result','=','0')->where('results.executed','=',1);
},
])
->withCount([
'results as executed' => function (Builder $query) {
$query->where('results.executed','=',1);
},
])
->whereNotNull('users.username')
->paginate(50);
Yapmak istedigim eskl hali ise boyle -
$users_list = DB::table('users')
->leftJoin('results', 'users.id', '=', 'results.user_id')
->leftJoin('user_profile','users.user_profile','=','user_profile.id')
->leftJoin('cities','users.city_id','=','cities.id')
->select('users.*','results.created_date as create_date','user_profile.title as user_profile_name','cities.title as city')
->addSelect(DB::raw('SUM(results.total_results) as total'))
->addSelect(DB::raw('SUM(CASE WHEN results.result >0 AND results.executed=1 THEN results.executed ELSE null END) as success'))
->addSelect(DB::raw('SUM(CASE WHEN results.result =0 AND results.executed=1 THEN results.executed ELSE null END) as error'))
->addSelect(DB::raw('SUM(CASE WHEN results.executed=1 THEN results.executed ELSE null END) as executed'))
->whereNotNull('users.username')
->orderBy('results.created_date','desc')
->groupBy('users.id')
->paginate(50);
Sonuc ayni ama debugbarda daha fazla query gozukuyor ilk halinde