I can't make a complex query on Lara?

Question

Laravel

Good afternoon! Please help me to make such a query with the help of DB (I tried to join everything correctly, but it doesn’t work):
2 tables:
inviteds - referral system table (user_id - invited, referer - inviting)
users - obviously
The task itself is this: you need to count the number of referral invitations by descending (for example, testuser invited 120 referrals, and testuser2 invited 50 referrals), but inviteds.user_id should also have user.active = 1.
Active = 1 means that the user confirmed the mail, that is, both the invitee and the inviter should have users.active= one.
Is it possible to compose this with a request, I did not succeed, I tried - honestly.

Answer 1

in extreme cases, use DB::statement and DB::raw - you write regular SQL there,
but what you need (as I understand it) - in a subquery you count the number of children by parents, then you join with the parent.