search

netsells / laravel-geoscope

10 stars 6 forks source link

SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION harvesine.ST_Distance_Sphere does not exist #6

Closed cloudeweb closed 3 years ago

cloudeweb commented 3 years ago

Hi, I'm using your package in localhost with 10.1.35-MariaDB version.

I've added this 'scope-driver' => 'mysql', but I get this follow error: SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION harvesine.ST_Distance_Sphere does not exist

Can you help me? thanks!

AdamHutchison commented 3 years ago

Hi @cloudeweb. Because mariadb uses the same DB connection driver as mysql in laravel you need to explicitly set the scope driver to mariadb in your geoscope,php config file. e.g.

    'defaults' => [
        'lat-column' => 'latitude',
        'long-column' => 'longitude',
        'units' => 'miles',
        'scope-driver' => 'mariadb',
    ],

Give me a shout if you need any more help.

cloudeweb commented 3 years ago

Works fine! but with only 3 results I get a lot of queries: ​5 statements were executed, 4 of which were duplicated, 1 unique.

With classic harvesine code I get 1 query only.

This is my code:

$jobs = Job::addDistanceFromField($lat, $lng, 'custom_field_name') ->orderByDistanceFrom($lat, $lng, 'asc') ->get();

thanks!