Axeia

Prevent laravel from inserting parenthesis around union queries?

Posted 3 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.

Laracasts Mascot

Hi, Have We Met Yet?

Did you know that, in addition to the forum, Laracasts includes well over 1000 lessons on modern web development? All for the price of one lunch out per month.

Sign Me Up

Channels

Reply to

Use Markdown with GitHub-flavored code blocks.