Laravel explicit joins

modules granted to a facility.md

Raw SQL

SELECT
    modules.name AS module,
    (CASE WHEN facility_module.facility_id IS NULL 
        THEN 0
        ELSE 1
    END) AS granted
FROM
    modules
LEFT JOIN facility_module ON modules.name = facility_module.module_name
    AND facility_module.facility_id = 'a3d500f9-b2f7-49ed-bfa8-df40a41e3aa9';

Explicit Join

$modules = (function() {
    $query = \App\Models\Module::query();
    $query->leftJoin('facility_module', function($join) {
        $join->on('facility_module.module_name', '=', 'modules.name');
        $join->where('facility_module.facility_id', '=', 'a3d500f9-b2f7-49ed-bfa8-df40a41e3aa9');
    });
    $query->select('modules.name', 'facility_module.facility_id');
    $modules = $query->get();

    return $modules->map(function ($module) {
        return [
            'name' => $module->name,
            'granted' => ! is_null($module->facility_id),
        ];
    });
})();

Eager Loading

$modules = (function() {
    $query = \App\Models\Module::query();
    $modules = $query->get();

    return $modules->map(function ($module) {
        return [
            'name' => $module->name,
            'granted' => $module->facilities->contains('id', 'a3d500f9-b2f7-49ed-bfa8-df40a41e3aa9')
        ];
    });
})();

permissions granted to a role through facility modules.md

Raw SQL

SELECT
    permissions.id,
    permissions.name,
    permissions.module_name,
    modules.category AS module_category,
    (CASE WHEN role_permission.role_id IS NULL 
        THEN 0
        ELSE 1
    END) AS granted
FROM
    permissions
INNER JOIN modules ON permissions.module_name = modules.name
LEFT JOIN role_permission ON permissions.id = role_permission.permission_id 
    AND role_permission.role_id = '6337741f-31a9-4b3e-a4a0-67bac6a4c0a8'
WHERE 
    permissions.module_name IN (
        SELECT
            module_name
        FROM
            facility_module
        WHERE
            facility_id = 'a95187fa-aa8d-44c4-bf00-79ce250ecfcc'
    );

Explicit Join

$permissions = (function() {
    $query = \App\Models\Permission::query();
    
    $query->join('modules', 'permissions.module_name', '=', 'modules.name');
    
    $query->leftJoin('role_permission', function($join) {
        $join->on('permissions.id', '=', 'role_permission.permission_id');
        $join->where('role_permission.role_id', '=', '6337741f-31a9-4b3e-a4a0-67bac6a4c0a8');
    });
    
    $query->select([
        'permissions.id', 
        'permissions.name',
        'modules.category AS module_category',
        'permissions.module_name',
        'role_permission.role_id',
    ]);

    $query->whereIn('module_name', function($query) {
        $query->from('facility_module')
            ->select('module_name')
            ->where('facility_id', 'a95187fa-aa8d-44c4-bf00-79ce250ecfcc');
    });

    $permissions = $query->get();

    // return collect($permissions)->groupBy(['module_category', 'module_name']);

    $permissions = $permissions->map(function ($permission) {
        return [
            'id' => $permission->id,
            'name' => $permission->name,
            'module' => [
                'category' => $permission->module_category,
                'name' => $permission->module_name,
            ],
            'granted' => ! is_null($permission->role_id),
        ];
    });

    // return collect($permissions)->groupBy(['module.category', 'module.name']);
    
    return $permissions;
})();

Eager Loading

$permissions = (function() {
    $modules = \DB::table('facility_module')
        ->select('module_name')
        ->where('facility_id', '7c574461-cdb5-4182-b0ce-30f3fbe13226')
        ->pluck('module_name')
        ->toArray();

    $query = \App\Models\Permission::query();

    $query->whereHas('module.facilities', function($query) use($modules) {
        $query->whereIn('module_name', $modules);
    });

    $permissions = $query->get();

    return $permissions->map(function ($permission) {
        return [
            'id' => $permission->id,
            'name' => $permission->name,
            'module' => [
                'category' => $permission->module->category,
                'name' => $permission->module_name,
            ],
            'granted' => $permission->roles->contains('id', 'b00e2693-2319-454c-a5b1-756635a6abc8'),
        ];
    });
})();