I have two tables namely users and activities which has many to many relationships in my Laravel app. So, I have a pivot table activity_users with user_id and activity_id. I know that I can simply fetch all the users enrolled in an activity with a many to many relationship in my model.

But I want to get only those users from the pivot table which are enrolled in only one activity. I have been trying to do this for many days and I am still stuck. I have created a work around to filter the values based on activities_count after fetching from the database, but I wanted a more efficient solution.

Table users

Table activities

Table activity_users

From this sample data what I want is: if I query for unique users in Activity 1, it should return only User B (user_id = 2) as he/she is not enrolled in other activities. And if I query for unique users in Activity 2, it should return null because it has only one user i.e. User A and he/she is already enrolled in Activity 1.

Fetching distinct values doesn't work as it just removes duplicates. I have already set to not allow duplicate combination of values in pivot table.

CodePudding user response：

How about use Having? You can find user with only one Activity like:

SELECT user_id FROM activity_users
GROUP BY user_id
HAVING count(user_id) = 1

Like Honk der Hase said in comments

CodePudding user response：

Providing some code would help, if only to show more information on your relationships between models. But at a guess I think something like this should work:

$users = User::with("activities")
    ->withCount("activities")
    ->having("activities_count", 1)
    ->whereHas("activities", fn ($q) => $q->where("activities.id", $activity_id));

We eager load the activities relationship and get the count as an alias, so we can catch only those with a single activity. Then we filter the relationship to search for the provided activity id.

This assumes you've set up your User and Activity models with a proper many-to-many relationship.

For completeness, the resulting typically (for Laravel) verbose SQL is:

SELECT `users`.*, (
    SELECT COUNT(*)
    FROM `activities`
    INNER JOIN `activity_user` ON `activities`.`id` = `activity_user`.`activity_id`
    WHERE `users`.`id` = `activity_user`.`user_id`
) AS `activities_count`
FROM `users` WHERE EXISTS (
    SELECT * from `activities`
    INNER JOIN `activity_user` ON `activities`.`id` = `activity_user`.`activity_id`
    WHERE `users`.`id` = `activity_user`.`user_id`
        AND `activities`.`id` = ?
)
HAVING `activities_count` = ?