SQL Syntax in PHP Laravel Query Builder PostgreSQLvs MySQL

Question

I'm using Laravel 5.2 and have a sql query using the query builder functions and have broken a query when switching from a MySQL database to PostgreSQL. The query is meant to get all locations within a certain number of miles of a latitude and longitude.

    $locations = Location::select(
        DB::raw("*,
            ( 3959 * acos( cos( radians(?) ) *
                cos( radians( lat) )
                * cos( radians( long ) - radians(?)
                ) + sin( radians(?) ) *
                sin( radians( lat ) ) )
            ) AS distance"))
        ->having("distance", "<", $distance)
        ->orderBy("distance")
        ->setBindings([$latitude, $longitude, $latitude])
        ->get();

Once switching to the PostgreSQL data source, I get this error:

QLSTATE[42703]: Undefined column: 7 ERROR: column "distance" does not exist
LINE 7: ) AS distance from "locations" having "distance" < $4 o...
^ (SQL: select *,
( 3959 * acos( cos( radians(37.7959362) ) *
cos( radians( lat ) )
* cos( radians( long ) - radians(-122.4000032)
) + sin( radians(37.7959362) ) *
sin( radians( lat ) ) )
) AS distance from "locations" having "distance" < 15 order by "distance" asc)

What would be the best way to fix this problem. I know it's a difference in the SQL syntax between MySQL and Postgres that is created by Laravel but I'm tearing my hair out trying to figure out how to fix this error.

Any help would be greatly appreciated!

patricus · Accepted Answer · 2016-06-25 01:43:28Z

1

The issue is your having clause. PostgreSQL does not allow using a derived column (or column alias) in the having clause, whereas MySQL does (as long as you have MySQL Extensions to Group By enabled, which is enabled by default).

One option to get around this restriction is to use a derived table, so that the field is available for the having clause. Something like below (warning: completely untested):

$locations = Location::from(DB::raw("(select *,
        ( 3959 * acos( cos( radians(?) ) *
            cos( radians( lat) )
            * cos( radians( long ) - radians(?)
            ) + sin( radians(?) ) *
            sin( radians( lat ) ) )
        ) AS distance from locations) as t"))
    ->having("distance", "<", $distance)
    ->orderBy("distance")
    ->setBindings([$latitude, $longitude, $latitude])
    ->get();

answered Jun 25, 2016 at 1:43

patricus

62.9k15 gold badges157 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike L Over a year ago

Thanks @patricus! I was able to get to the solution using your suggestion to use a derived table. I had to add a group by clause that looked like this: ->groupBy(DB::raw('id, title, address_1 ...')).

Collectives™ on Stack Overflow

SQL Syntax in PHP Laravel Query Builder PostgreSQLvs MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related