CODEWITHSUNDEEP
mysqlyii2query-builder
Steve
Steve

Reputation: 391

Yii2 Query not quoting correctly

I run into this problem, time and time again. It would be nice to find out how to build queries properly so I can stop resorting to Yii::$app->db->createCommand() as a workaround.

My Yii2 query:

            $users = UserSpatial::find()
            ->select('user_id, harvesine(y(coordinates), x(coordinates), :lat, :lon) as dist, astext(coordinates)')
            ->where('st_within(coordinates, envelope(linestring(point(:rlon1, :rlat1), point(:rlon2, :rlat2))))')
            ->orderBy('st_distance(point(:lon, :lat), coordinates)')
            ->params([
                ':lon' => $geo->lon,
                ':lat' => $geo->lat,
                ':rlon1' => $rlon1,
                ':rlat1' => $rlat1,
                ':rlon2' => $rlon2,
                ':rlat2' => $rlat2
            ])
            ->all();

The generated query ends up with backticks in all the wrong places and, oddly enough, not all parameters were backticked (sorry but you'll need to look closely for the misplaced backticks because I didn't know how best to highlight the incorrect placements):

SELECT \`user_id\`, harvesine(y(coordinates), x(coordinates),      \`32.7699547\`, \`-116.9911288)\` AS \`dist\`, astext(coordinates) 
FROM \`user_spatial\` 
WHERE st_within(coordinates, envelope(linestring(point(-117.07730792871, 32.697490931884), point(-116.90494967129, 32.842418468116)))) 
ORDER BY st_distance(point(-116.9911288, \`32.7699547)\`, \`coordinates)\`

The query should look like the following as I did not wrap double-square-brackets around any of the fields or values:

SELECT \`user_id\`, harvesine(y(coordinates), x(coordinates),      32.7699547, -116.9911288) AS dist, astext(coordinates) 
FROM \`user_spatial\` 
WHERE st_within(coordinates, envelope(linestring(point(-117.07730792871, 32.697490931884), point(-116.90494967129, 32.842418468116)))) 
ORDER BY st_distance(point(-116.9911288, 32.7699547), coordinates)

I can live with Yii2 adding some backticks around field names and table names but why on earth is it backticking numerical values? (FYI: the $rlon and $rlat values don't seem to get backticked but I was assuming that was because they are a result of math calculations!?!?).

I've already tried forcing $geo->lon and $geo->lat to float values like so:

'lon' => (float)$geo->lon;

or

'lon' => (float)$geo->lon * 1;

but it didn't help.

Upvotes: 4

Views: 1406

Answers (1)

Tony
Tony

Reputation: 5867

Try to use array format for select and orderBy methods, like docs suggest:

Besides column names, you can also select DB expressions. You must use the array format when selecting a DB expression that contains commas to avoid incorrect automatic name quoting. For example,

$query->select(["CONCAT(first_name, ' ', last_name) AS full_name", 'email']);

In you case it will be like this:

$users = UserSpatial::find()
->select([
    'user_id', 
    'harvesine(y(coordinates), x(coordinates), :lat, :lon) as dist',
    'astext(coordinates)'
])
->where('st_within(coordinates, envelope(linestring(point(:rlon1, :rlat1), point(:rlon2, :rlat2))))')
->orderBy(['st_distance(point(:lon, :lat)', 'coordinates)'])
->params([
    ':lon' => $geo->lon,
    ':lat' => $geo->lat,
    ':rlon1' => $rlon1,
    ':rlat1' => $rlat1,
    ':rlon2' => $rlon2,
    ':rlat2' => $rlat2
])
->all();

Upvotes: 3

Related Questions