In Laravel Eloquent how could you do two union queries and a select outside the union scope?-CodePudding

Laravel Eloquent example

        $union = User::select([
            DB::raw("id"),
            DB::raw("name")
        ])
            ->rightJoin("roles as t2", function ($join) {
                $join->on("users.role_id", '=', "t2.id");
            });

        $query = User::select(
            [
                  DB::raw("id"),
                  DB::raw("name")
            ]
        )
            ->leftJoin('roles', function ($join){
                $join->on("users.role_id", '=', "roles.id");
            })
            ->union($union);
           $query->select('name');

MySQL query I would like to reproduce using Laravel

select name from
((select id, name
 from `users`
          left join `roles` on `users`.`role_id` = `roles`.`id`)
union
(select id, name
 from `users`
          right join `roles` as `t2` on `users`.`role_id` = `t2`.`id`)
) as smth;

But using $query->select('name'); Just overrides the select from left join union.

P.S. I simplified my real case query(removed group bys and special join conditions, etc) to a point when the example might not make sense for real case use, but the demo still shows the problem - need to have a select statement outside the union scope.

CodePudding user response：

You should try this.

$outSideQuery = DB::raw("({$unionedQuery->toSql()}) as smth")->select('name');

CodePudding user response：

to Add a new select column to the query you can use addSelect method:

 $query->addSelect('name');