Perform specific SQL query on Laravel

0
votes

I have an application that manages shops, users and roles.

Each user could be an owner, supervisor, employee or admin.

The way to know if a user has admin, supervisor or employee permissions is through the role_user table that connects the users, the shops and the roles (the admin role sets NULL value in shop_id field on the role_shop table). The owner, instead, is obtained by the user_id field on the shops table.

I would like to perform a function on my user model that gets all the roles that a user has.

The following query works but I don't know how to perform it on Eloquent's Query Builder

SELECT priority, role, shop
FROM (
    (
        SELECT 1.5 AS priority, "owner" AS role, shops.id AS shop
        FROM shops
        WHERE shops.user_id = 1
    )

    UNION

    (
        SELECT roles.id AS priority, roles.name AS role, role_shop.shop_id AS shop
        FROM role_shop
        JOIN roles ON (roles.id = role_shop.role_id)        
        WHERE role_shop.user_id = 1
    )

) AS result
ORDER BY priority

Thanks for your help folks!

2
sqllaraveleloquentunionquery-builder

2 Answers

1
votes

Since you have two selects here, I will suggest to make two queries first then join the two queries to get the union result.

$query1 = Shop::select(
    DB::RAW('"1.5" AS "priority"'),
    DB::RAW('"owner" AS "role"'),
    DB::RAW('id AS "shop"')
  )
  ->where('user_id', 1)
  ->orderBy('priority');

$query2 = RoleShop::join('roles AS roles', 'role_shop.role_id', '=', 'roles.id')
  ->select(
      DB::RAW('roles.id AS "priority"'),
      DB::RAW('roles.name AS "role"'),
      DB::RAW('role_shop.shop_id AS "shop"'),
  )
  ->where('role_shop.user_id', 1)
  ->orderBy('priority');

$result = $query1->union($query2)->get();

It may not be 100% correct but feel free to tweak it to get what you want.

Reference

0
votes

Use join method and group the result by user_id

$roles = DB::table('roles')
->where('user_id', '=', 1)
->join('shops', 'shops.user_id', '=', 'roles.user_id')
//Add other tabalse as join then
->select('roles.*')
->groupby('roles.user_id')
->get();

Ps: Sorry my code is not complete, I'm with mobile, you can find samples in laravel docs