Query which works in SQL Client but not in Laravel? SQL General error 2031

For a regular query use db facade or getPdo(), I prefer getPdo().

[EDIT at the end of post]

Hi @jlrdw , thanks for your suggestion: Actually, except for construct my query, I'm already using DB facade to query the database with the last command:

$five_nearest_providers_query = DB::query()
->select(['name', 'phone', 'address_town', 'address_street', 'address_postal_code'])
->fromSub($five_nearest_towns, 'nearby')
->join('providers', 'providers.address_postal_code', '=', 'nearby.postal_code')
->distinct();

I tried to use pdo this way:

$five_nearest_providers_query = DB::query()
                                ->select(['name', 'phone', 'address_town', 'address_street', 'address_postal_code'])
                                ->fromSub($five_nearest_towns, 'nearby')
                                ->join('providers', 'providers.address_postal_code', '=', 'nearby.postal_code')
                                ->distinct()
                                ->toSql();
$database = DB::getPdo();
$final_query = $database->query($five_nearest_providers_query);
$result = $final_query->fetch();
dd($result);

But same exception occurs, without the error message:

PDOException (HY000)
SQLSTATE[HY000]: General error: 2031

It's very curious because when I copy/paste directly the result of the toSql() (retrieved by dd($five_nearest_providers_query), the query is working:

$final_query = $database->query("select distinct `name`, `phone`, `address_town`, `address_street`, `address_postal_code` from (select `postal_code` from `towns` order by ST_DISTANCE(`location`, (select `location` from `towns` where `postal_code` = $postal_code limit 1)) asc limit 5 offset 0) as `nearby` inner join `providers` on `providers`.`address_postal_code` = `nearby`.`postal_code`");

[EDIT] I targeted the problem, it came from the passed variable $postal_code in $visitor_town where clause:

->where('postal_code', $postal_code)

which then is submitted to the $five_nearest_towns orderBy:

->orderBY(DB::raw("ST_DISTANCE(`location`, (".$visitor_town->toSql()."))"), 'asc')

So the toSql() returned :

select location` from `towns` where `postal_code` = ? limit 1

The variable wasn't passed > Query crashed.

This is what I did to resolve the problem (final code):

$visitor_town = Town::query()
                ->select(['location'])
                ->whereRaw("postal_code = ".$postal_code) //<<<!!! Passed variable in a raw where clause
                ->take(1)
                ->toSql();
                            
$five_nearest_towns = Town::query()
                      ->select(['postal_code'])
                      ->orderBY(DB::raw("ST_DISTANCE(`location`, (".$visitor_town."))"), 'asc')
                      ->offset(10)
                      ->limit(5);

$five_nearest_providers_query = DB::query()
                                ->select(['name', 'phone', 'address_town', 'address_street', 'address_postal_code'])
                                ->fromSub($five_nearest_towns, 'nearby')
                                ->join('providers', 'providers.address_postal_code', '=', 'nearby.postal_code')
                                ->distinct()
                                ->get();

I needed to pass the variable in a raw expression, to ensure it was fully transmitted to the upper level query.

You cannot use ->bind* and ->execute($params). Use either or; if you pass parameters to execute(), those will make PDO forget the parameters already bound via ->bind*. This is one of the suggestions from quora.

Marketing executive from Amer Services