如何使用laravel的查询构建器命名绑定？

问题描述：

I'd like to do the following:

MyModel::where(MyModel::sql_distance_to(), ['lat' => '...', 'lng' => '...'])->get();

Where sql_distance_to() returns some complex sql expression. But from what I can see, query builder doesn't support named bindings. Is this true? Any workaround?

我想执行以下操作： p>

  MyModel  :: where（MyModel :: sql_distance_to（），['lat'=＆gt;'...'，'lng'=＆gt;'...']） - ＆gt; get（）; 
  code>   pre> 
 
 其中 sql_distance_to（） code>返回一些复杂的sql表达式。 但是从我所看到的，查询构建器不支持命名绑定。 这是真的？ 任何解决方法？ p> 
  div>

答

I belive you mean using Query Scopes

class MyModel extends Model
{
    public function scopeSqlDistanceTo($query, $lat, $lon)
    {
        // Previously DB::raw(expr) was here.
        // No need since we're using whereRaw()
        $whereRaw = 'lat = ? AND lon = ?';

        return $query->whereRaw($whereRaw, [$lat, $lon]);
    }
}

Usage:

$latitude = 0.0;
$longitude = 180.0;
MyModel::sqlDistanceTo($latitude, $longitude)->get();

答

tl;dr Use Skysplit's solution, if possible.

Here's what I came up with. One additional complication is that laravel by default disables pdo's emulation mode. Which results in being unable to use a named parameter marker of the same name more than once in a statement.

$q = MyModel::whereRaw(MyModel::sql_distance_to() . ' < :dist');
run_query_with_params(MyModel::class, $q,
    array_merge(MyModel::sql_distance_to_params($lat, $lng), [
        'dist' => 10,
    ]));

class MyModel {
    static function sql_distance_to() {
        return 'ACOS(
            SIN(RADIANS(lat)) * SIN(RADIANS(:lat1))
            + COS(RADIANS(lat)) * COS(RADIANS(:lat2))
                * COS(RADIANS(:lng - lng))
        ) * 6371';
    }

    static function sql_distance_to_params($lat, $lng) {
        return [
            'lat1' => $lat,
            'lat2' => $lat,
            'lng' => $lng,
        ];
    }
}

function run_query_with_params($class, $q, $params)
{
    $r = \DB::select($q->toSql(), $params);
    return call_user_func([$class, 'hydrate'], $r);
}

如何使用laravel的查询构建器命名绑定？

相关推荐