Laravel 7 mysql union query

Question

I am building a Laravel 7 app and trying to create a complex query using UNION which I think is what I want to do.

Here is what I want to do: I want to query the users table where the column roleid = 0 , I then want to query the client_profiles table where the userid column equals the value of the id column from the users table.

I then want to query the users table (again) and get the value of the name column where the id column equals the value from the client_profiles table column account_rep_id

Not sure if a join would be better and if so how to write that query or if a UNION would work .

My issue is the below query gives me an error that says:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL 
syntax; check the manual that corresponds to your MySQL server version for the right 
syntax to use near 'union (select `users`.`name`, `users`.`system_account_status` from 
`users` where' at line 1 (SQL: (select `users`.`name` from `users` where 
`client_profiles`.`account_rep` = users.id) union ((select `client_profiles`.`account_rep` 
from `client_profiles` where `client_profiles`.`userid` = users.id) union (select 
`users`.`name`, `users`.`system_account_status` from `users` where `roleid` = 0)))

Here is my query:

    $firstdata = DB::table('users')
        ->select('users.name','users.system_account_status')
        ->where('roleid', '=', 0);

    $seconddata = DB::table('client_profiles')
        ->select('client_profiles.account_rep')
        ->where('client_profiles.userid', '=', 'users.id')
        ->union($firstdata);
    
    $thirddata = DB::table('users')
        ->select('users.name')
        ->where('client_profiles.account_rep', '=', 'users.id')
        ->union($seconddata)
        ->get();
    print_r($thirddata);

score 0 · Answer 1 · answered Sep 04 '20 at 11:55

Logically your query isn't look good because you have 2 columns from first query $firstdata and single column from other queries. Union quer(ies) should have same no. columns.

For your syntax error there are extra ()s added for $firstdata and $seconddata

(select query 3)
union
( <-----
    (select query 2)
    union
    (select query 1)
) <-----

Above should be

(select query 3)
union
(select query 2)
union
(select query 1)

I guess you can get rid of syntax error by using multiple union() clause with final query

$thirddata = DB::table('users')
    ->select('users.name')
    ->where('client_profiles.account_rep', '=', 'users.id')
    ->union($seconddata)
    ->union($firstdata)
    ->get();

Laravel 7 mysql union query

1 Answers1