CODEWITHSUNDEEP
laravelpostgresql
Marcelo Oliveira
Marcelo Oliveira

Reputation: 3

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

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)"

Upvotes: 0

Views: 279

Answers (1)

Moshe Katz
Moshe Katz

Reputation: 16891

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.

Upvotes: 0

Related Questions