MySQL & Eloquent Query

Question

I have two tables:

Users - id, name, email, designation, type

Requests - id, product, user

In ‘Request’ table field user is a foreign key from ‘Users’ table.

Now I want count of requests each user has made where users.type is 3 or 6 along with users data.

Output data should have these fields users.id, users.name, users.email, users.designation, users.type, count.

Please help me writing MYSQL query and Eloquent Query (If Possible). Please check Tables Data for more understanding.

Answer 1

SQL is quite simple:

SELECT u.id, u.name, u.email, u.designation, u.type, count(r.id)
FROM users AS u
JOIN Request AS r ON r.user_id = u.id
WHERE u.type IN (3,6)
GROUP BY u.id, u.name, u.email, u.designation, u.type

To get this with Eloquent your User model would require a relation to Request. I assume requests:

$usersWithRequestCount = \App\User::withCount('requests')->whereIn('types',[3,6])->get()