DB binding error | Laravel.io

posted 9 years ago

HI Im building a package that has mapping, now one of the function it needs to be able to do and query for locations within a radius distance of a location now Ive done this 1000's of times and the code is every where on the internet. Now Ive made my query a bit more laravel like. It works 100% on all database but sqlite requires a groupBY before a Having in a query and sqlite dose not have maths functions . So I added the missing maths functions to sqlite but Im getting a binding error

eg: General error: 25 bind or column index out of range

This is the Code.

$model->select(
            DB::raw("*,  ( 6371 * acos( cos( radians(?) ) *
                cos( radians( latitude ) )
                * cos( radians( longitude ) - radians(?) ) + sin( radians(?) ) *
                 sin( radians( latitude ) ) )) AS distance"))
            ->groupBy('id')
            ->having("distance", "<", "?")
            ->orderBy("distance")
            ->setBindings([$latitude, $longitude, $latitude, $radius])
            ->get();

Now the the groupBy is only there for sqlite as its required before a having.

Last updated 2 years ago.

apps4u

replied 9 years ago

If anyone want to know how to add a function to sqlite database without having to hack to source code. If you are using PDO then its real easy and even easier when the functions you want to add are part of the programming language used with PDO .

So add this to your project I used the AppServiceProvider register method.

		$pdo = DB::connection('sqlite')->getPdo();
		$pdo->sqliteCreateFunction('cos', 'cos', 1);
		$pdo->sqliteCreateFunction('acos', 'acos', 1);
		$pdo->sqliteCreateFunction('sin', 'sin', 1);
		$pdo->sqliteCreateFunction('radians', 'deg2rad', 1);

apps4u

replied 9 years ago

Solution

Fixed it I was missing a BD::raw within the having as i was using a bind param

 DB::raw("*,  ( 6371 * acos( cos( radians(?) ) * cos( radians( `latitude` ) ) * cos( radians( `longitude` ) - radians(?) ) + sin( radians(?) ) * sin( radians( `latitude` ) ) )) AS distance"))
            ->groupBy('id')
            ->having("distance", "<", DB::raw('?'))
            ->orderBy("distance")
            ->setBindings([$latitude, $longitude, $latitude, $radius])
            ->get();

Moderators