我想在高级连接子句中使用多个表

Question

I need some help on my query. I use multiple tables with advanced join clauses but it shows invalid count and both has same value:

$parents = DB::table('users')
            ->select('users.id','users.full_name', 'users.email', 'users.avatar', 'users.signup_date', (DB::raw('count(children.id) as children_no')), (DB::raw('count(invitations.id) as invitations_no')))
            ->leftJoin('children', function ($join) {
                $join->on('users.id', '=', 'children.userid')
                ->where('children.is_deleted', '=', 0);
            })
            ->leftJoin('invitations', function ($join) {
                $join->on('users.id', '=', 'invitations.user_id')
                ->where('invitations.is_deleted', '=', 0);
            })
            ->where('users.is_admin', '=', 0)
            ->groupBy('users.id')
            ->get();

Answer 1

I think you can solve this problem with basic relationships in Laravel + soft deletes on the invitations and children table.

This will make your query less complex and you have the Laravel benefits.

<?php

$users = User::where('is_admin', false)
    ->has(['invitations', 'children'])
    ->withCount(['invitations', 'children'])
    ->get();

This wil select all none admins, with invitations and children. Make sure you have soft deletes setup on children and invitations. The withCount will add a count for the related relations.