I have the following query for my users table. I have an additional pivot table for permissions and need to integrate this into my query.
$users = DB::table('users')
->join('locations', 'locations.id', '=', 'users.location_id')
->where('active', 1)
->get(array('users.fullname', 'users.email', 'permissions.granted'))
The pivot table consists of
user_persmission (table)
user_id, permission_id
permissions (table) id title
Simply, I need my array to have an additional field to indicate if this user has any permission set. It does not have to show the value, but only set 'granted' to 1 if a row exists.
Note: It is possible for a user to have no permissions as this is not mandatory, and equally they can have more than one permission.
I was thinking of a left outer join but very unsure how to build this into my existing query as it is a many-to-many relationship.
