当前位置: 动力学知识库 > 问答 > 编程问答 >

postgresql - Laravel 4 not able to query on postgres function

问题描述:

I have a postgres function, distance(), that determines the distance between two points.

DECLARE

x float = 69.1 * (lat2 - lat1);

y float = 69.1 * (lon2 - lon1) * cos(lat1 / 57.3);

BEGIN

RETURN sqrt(x * x + y * y);

END

This works fine when executed in Postgres. But now I want to use it in a Laravel 4 search to find all addresses in a radius. Using query builder I have

$query->where(\DB::select('select distance(coords,'.$parameters['lat'].','.$parameters['lon'].')') < 9);

This should be looking at the coords field in the addresses (ex. '39.606912,-104.881802') and resolve that in the query. It errors saying the column 'coords' does not exist. If I use "addresses"."coords" it then says it cannot find the table.

网友答案:

Usually DB::raw() work in these cases and there were some missing commas and quotes in your command

$query->where(\DB::select(\DB::raw('select distance(coords,'.$parameters['lat'].','.$parameters['lon'].')')), '<', '9');

To make sure Laravel is building it the way you need and that the query it's building will work on your database, use this command to dump your query in log:

DB::listen(function($sql, $bindings, $time) { 
    Log::info($sql); 
    Log::info($bindings); 
});

What your error is saying is that you are missing a FROM, so your query should be:

$query->where(
    \DB::select(
        \DB::raw('select distance(addresses.coords,'.$parameters['lat'].','.$parameters['lon'].') FROM addresses')
    )
    , '<', '9'
);

But if this is just part of your query and not your whole query, you should not need a FROM, unless you are gettind this info from a different table, which is not already in your main query FROM list.

分享给朋友:
您可能感兴趣的文章:
随机阅读: