This query works well in Mysql but in Postgres it is giving error

Question

This query works fine when I was using Mysql, now that we've migrated to Postgres, it's giving an error. Where is the problem?

public function scopeClosestTo(\Illuminate\Database\Eloquent\Builder $query, array $coord = null)
{
    if ($coord && isset($coord['longitude'], $coord['latitude'])) {
        return $query->select([
            '*',
            'distance' => DB::table( 'offers as offers_temp' )->selectRaw(
                'ST_Distance_Sphere(point(`longitude`, `latitude`), point(?, ?)) AS distance',
                [$coord['longitude'], $coord['latitude']]
            )->whereColumn('offers_temp.id', 'offers.id')
        ])
            ->withCount(['favoriteOffers'])
            ->where('published', '=', true)
            ->where('active', '=', true)
            ->whereNotNull('longitude')
            ->whereNotNull('latitude')
            ->whereDate('expires_at', '>', \Carbon\Carbon::now())
            ->orWhereNull('expires_at')
            ->orderBy('distance');
    }

    return $query;
}

Error:

"SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near ","\nLINE 1: ...ct , (select ST_Distance_Sphere(point(longitude, latitud...\n ^ (SQL: select *, (select ST_Distance_Sphere(point(longitude, latitude`), point(-43.3722344, -22.7867144)) AS distance from "offers" as "offers_temp" where "offers_temp"."id" = "offers"."id") as "distance", (select count() from "favorite_offers" where "offers"."id" = "favorite_offers"."offer_id" and "favorite_offers"."deleted_at" is null) as "favorite_offers_count" from "offers" where (("published" = 1 and "active" = 1 and "longitude" is not null and "latitude" is not null and "expires_at"::date > 2022-03-28 or "expires_at" is null) and "longitude" is not null and "latitude" is not null and exists (select * from "offer_categories" inner join "offers_offer_categories" on "offer_categories"."id" = "offers_offer_categories"."offer_category_id" where "offers"."id" = "offers_offer_categories"."offer_id" and "offers_offer_categories"."offer_category_id" in (1) and "offer_categories"."deleted_at" is null) and "to_companies" = 0 and "published" = 1 and "active" = 1 and "expires_at"::date > 2022-03-28 or "expires_at" is null) and "offers"."deleted_at" is null order by "distance" asc limit 15 offset 0)"

What is \Carbon\Carbon supposed to be? Add answer as update to question. — Adrian Klaver
– Adrian Klaver, Commented Mar 28, 2022 at 23:08

Moshe Katz · Accepted Answer · 2022-03-28 23:22:36Z

0

Your query uses backticks to escape column names, which works in MySQL. However, PostgreSQL uses double quotes to escape column names.

Change

point(`longitude`, `latitude`)

To

point("longitude", "latitude")

However, the words longitude and latitude are not reserved words in postgres, so there should be no reason you need to quote them.

See this article on the PostgreSQL wiki for more about moving from MySQL to PostgreSQL.

answered Mar 28, 2022 at 23:22

Moshe Katz

16.5k7 gold badges71 silver badges127 bronze badges

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

Collectives™ on Stack Overflow

This query works well in Mysql but in Postgres it is giving error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related