Axeia

Prevent laravel from inserting parenthesis around union queries?

Posted 5 months ago by Axeia

Hello,

I'm using laravel 5.7.5 and running into a problem with my query which seemingly is due to Laravel inserting parenthesis around my unions. My code is as follows:

                $users = User::select(\DB::raw('
                users.name AS username, 
                trainers.name AS trainername,
                slug AS trainernameslug,
                TRUNCATE(latitude, 0) AS latitude, 
                TRUNCATE(longitude, 0) AS longitude, 
                share_location,
                eggfriends_count,
                eggfriend_cap,
                team,
                ROUND(( 6371 * acos(cos(radians('.$latitude.')) * cos(radians(ROUND(latitude, 2))) 
                * cos( radians(ROUND(longitude,2)) - radians('.$longitude.')) + sin(radians('.$latitude.')) 
                * sin( radians(ROUND(latitude,2))))), 2) AS distance'))
                ->join('trainers', 'users.id', '=', 'trainers.user_id')
                ->join('eggfriends_count', 'trainers.name', '=', 'eggfriends_count.name')
                ->whereNotIn('trainers.name', function($qReachedCap){
                    $dbPendingOrFriends = \DB::table('eggfriends')
                    ->select('accepter AS name')
                    ->where('requester', Auth::user()->active_trainer) // ?
                    ->where('accepted', '!=', \DB::raw(0)); // ? if it's null or true.

                    $dbOwnAccounts = \DB::table('trainers')
                    ->select('trainers.name')
                    ->where('user_id', \DB::raw(Auth::user()->id)); // = ?
                    
                    $qReachedCap->select('eggfriends_count.name')
                    ->from('eggfriends_count')
                    ->join('trainers', 'trainers.name', '=', 'eggfriends_count.name')
                    ->where('eggfriends_count', '>=', \DB::raw('eggfriend_cap'))
                    ->union($dbPendingOrFriends)
                    ->union($dbOwnAccounts);
                })
                ->orderBy('distance', 'DESC')
                //->toSql();
                //dd(str_replace("\n", ' ', $users));
                ->paginate(10);

Which results in the following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union (select `accepter` as `name` from `eggfriends` where `requester` = ? and `' at line 1 (SQL: select count(*) as aggregate from `users` inner join `trainers` on `users`.`id` = `trainers`.`user_id` inner join `eggfriends_count` on `trainers`.`name` = `eggfriends_count`.`name` where `trainers`.`name` not in ((select `eggfriends_count`.`name` from `eggfriends_count` inner join `trainers` on `trainers`.`name` = `eggfriends_count`.`name` where `eggfriends_count` >= eggfriend_cap) union (select `accepter` as `name` from `eggfriends` where `requester` = JBomb2411 and `accepted` != 0) union (select `trainers`.`name` from `trainers` where `user_id` = 1)))

commenting out the paginate and uncommenting the toSql / dd I get the following query (formatted for readability/better error line reporting):

SELECT users.name AS username,
       trainers.name AS trainername,
       slug AS trainernameslug,
       TRUNCATE(latitude, 0) AS latitude,
       TRUNCATE(longitude, 0) AS longitude,
       share_location,
       eggfriends_count,
       eggfriend_cap,
       team,
       ROUND((6371 * acos(cos(radians(53.45595)) * cos(radians(ROUND(latitude, 2))) * cos(radians(ROUND(longitude, 2)) - radians(-2.15939)) + sin(radians(53.45595)) * sin(radians(ROUND(latitude, 2))))), 2) AS distance
FROM `users`
INNER JOIN `trainers` ON `users`.`id` = `trainers`.`user_id`
INNER JOIN `eggfriends_count` ON `trainers`.`name` = `eggfriends_count`.`name`
WHERE `trainers`.`name` NOT IN (
                                  (SELECT `eggfriends_count`.`name`
                                   FROM `eggfriends_count`
                                   INNER JOIN `trainers` ON `trainers`.`name` = `eggfriends_count`.`name`
                                   WHERE `eggfriends_count` >= eggfriend_cap)
                                UNION
                                  (SELECT `accepter` AS `name`
                                   FROM `eggfriends`
                                   WHERE `requester` = ?
                                     AND `accepted` != 0)
                                UNION
                                  (SELECT `trainers`.`name`
                                   FROM `trainers`
                                   WHERE `user_id` = 1))
ORDER BY `distance` DESC

After replacing the questionmark after requester = ? with a proper value phpmyadmin spits out the following error when running this query: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION (SELECT accepter AS name ' at line 19. Removing all the parenthesis around the selects inside the NOT IN resolves the problem and the query completes. So my question is how do I keep laravel from inserting parenthesis around union queries?

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.