How to select user with more conditions

Question

I have a database model, which stores user in groups and for each group are other rights. The structure is shown on image.

I want to make a SELECT query, which return user(s) which have one or more rights.

Im using medoo

public function selUserByRight($rightID)
    {

        return $this->db->select(
            "Users",
            [
                "[>]GroupRight" => ["FK_Group" => "FK_Group"],
                "[>]Rights" => ["GroupRight.FK_Right" => "ID"]
            ],
            [
                "Users.ID","Users.Name","Rights.ID(RightID)","Rights.Right"
            ],
            [
                "AND" =>
                    [
                        "Rights.ID" => $rightID
                    ]
            ]
        );
    }

Usage:

$result = $db->selUserByRight(1,2,3);

Raw Query:

SELECT "Users"."ID","Users"."Name","Rights"."ID" 
AS "RightID","Rights"."Right" 
FROM "Users" 
LEFT JOIN "GroupRight" ON "Users"."FK_Group" = "GroupRight"."FK_Group" 
LEFT JOIN "Right" ON "GroupRight"."FK_Right" = "Rights"."ID"
WHERE "Rights"."ID = 1

The problem is, that query returns all users, but i want to return only user1 (because only this user have all wanter rights).

Is there any solution?

Answer 1

I think that you can try the following:

SELECT dd.ID, dd.Name, dd.FK_Group, _aa.total_rights
FROM (
    SELECT aa.ID, COUNT(*) AS total_rights
    FROM Users AS aa
    INNER JOIN Groups AS bb
    ON aa.FK_Group = bb.ID
    INNER JOIN GroupRight AS cc
    ON bb.ID = cc.FK_Group
    GROUP BY aa.ID
    HAVING COUNT(*) > 0
) AS _aa
INNER JOIN Users AS dd
ON dd.ID = _aa.ID
ORDER BY _aa.total_rights;