Thank you! Your feedback has been delivered
Thank you! Your feedback has been sent

Not able to get proper return from sql raw test

I have a postgres function that measures the distance between two points. I want to use this as a test to find all addresses in a radius. The following returns a correct result:

(DB::select(DB::raw("select cpdistance('39.606912,-104.881802','39.654872,-104.996809')"))[0]->cpdistance)

I have organizations which have addresses associated with them. When I put this into a whereHas it errors saying that a from clause, "missing FROM-clause entry for table", is missing.

 $org = $this->repository->find('27854');
        $result = $org->whereHas('addresses', function($query)
                    {
                            $query->where((DB::select(DB::raw("select cpdistance(".'addresses.coords'.",'39.654872,-104.996809')"))[0]->cpdistance), '<', '9');                            
                    })->get();

My goal is to select only organizations with addresses which fall within the radius. The example here has been simplified to show only a single organization.

User Gravatar

Forbin

Posted Mar 27 2014 12:27 UTC

$50


  • Assigned To gmaliar
  • Solved
  • php
    postgresql
    sql
  • 2443 Views

11 Replies


Try adding a 'with' clause.

Your second line of code thus becomes: $result = $org->with('addresses')->whereHas('addresses', function($query)

User Gravatar

jawish

Posted Mar 27 2014 14:02 UTC

Nope, that did not work. Same result.

User Gravatar

Forbin

Posted Mar 28 2014 6:06 UTC

Try just accessing PDO directly.... PDO::query ( string $statement )

User Gravatar

fatalexe

Posted Mar 28 2014 7:52 UTC

Hey, I think that something gets mixed with Eloquent's ORM, try dumping the full query you get.

DB::getQueryLog();

Also give a try to something like this

$addresses_distance = (DB::select(DB::raw("select cpdistance(".'addresses.coords'.",'39.654872,-104.996809')"))[0]->cpdistance;

$result = $this->repository
           ->where('id', '=', '27854')
           ->whereHas('addresses', function($query) {
               $query->where($addresses_distance, '<', '9');
           })->get();
User Gravatar

gmaliar

Posted Mar 28 2014 8:02 UTC

Shouldn't it be more like the following:

$org    = $this->repository->find('27854');
$result = $org->whereHas('addresses', function($query) {
    $query->where(DB::raw("cpdistance(". 'addresses.coords' . ",'39.654872,-104.996809')"), '<', '9');
})->get();

You are mixing eloquent and pure DB queries with each other... (http://laravel.com/docs/eloquent#querying-relations)

User Gravatar

Stayallive

Posted Mar 28 2014 8:10 UTC

Stayallive your suggestion seems hopeful, as it does not error, but it also just spins forever. Dumping to sql gives

select * from "organizations" where (select count(*) from "addresses" inner join "addresses_organizations" on "addresses"."id" = "addresses_organizations"."address_id" where "addresses_organizations"."organization_id" = "organizations"."id" and cpdistance(addresses.coords,'39.654872,-104.996809') < ?) >= ?

I am not sure how that will resolve as I believe it needs the DB::select( to surround the db raw in order to execute it. Using the form that I show at the top of the question does return the correct response.

User Gravatar

Forbin

Posted Mar 28 2014 8:29 UTC

Hmm, seeing that query this might be more helpful:

// I am not sure how you interface with all orgs since you removed that so I use $orgModel as an placeholder
// Also not sure wich foreign key links the addresses to the org so i used org_id as a placeholder

// Find all adressed in range
$adresses = $org->addresses()->where(DB::raw("cpdistance('addresses.coords','39.654872,-104.996809')"), '<', '9')->get();

// Create an array to store found orgs in
$orgs = array();

// Check if we have adresses otherwise we error out
if ($adresses->count() > 0) {
    // Select all orgs that we found addresses for
    $orgs = $orgModel->whereIn('id', $adresses->lists('org_id'))->get();
}

// This are the orgs with adresses in range
return $orgs;
User Gravatar

Stayallive

Posted Mar 28 2014 8:42 UTC

That did not work and produced the following. I am not sure how that will resolve as I believe it needs the DB::select( to surround the db raw in order to execute it. Using the form that I show at the top of the question does return the correct response.

Substring error: 7 ERROR: negative substring length not allowed CONTEXT: PL/pgSQL function cpdistance(character varying,character varying) line 15 at assignment (SQL: select "addresses".*, "addresses_organizations"."organization_id" as "pivot_organization_id", "addresses_organizations"."address_id" as "pivot_address_id" from "addresses" inner join "addresses_organizations" on "addresses"."id" = "addresses_organizations"."address_id" where "addresses_organizations"."organization_id" = 27854 and cpdistance('addresses.coords','39.654872,-104.996809') < 9)
User Gravatar

Forbin

Posted Mar 28 2014 8:50 UTC

You can try a

$addresses = $org->addresses()
            ->select(DB::raw("cpdistance('addresses.coords','39.654872,-104.996809') as radius"))
            ->get();

If that works you'll get all the addresses with the radius, then you can filter it out with PHP.

A step afterwards would be to try adding a HAVING clause, maybe something like this

$addresses = $org->addresses()
            ->select(DB::raw("cpdistance('addresses.coords','39.654872,-104.996809') as radius"))
            ->having('radius', '<', 9)
            ->get();
User Gravatar

gmaliar

Posted Mar 28 2014 8:55 UTC

gmaliar, getting close! Your first statement does work. The second errors on radius

Undefined column: 7 ERROR: column "radius" does not exist LINE 1: ...sses_organizations"."organization_id" = $1 having "radius" <... ^ (SQL: select cpdistance(addresses.coords,'39.654872,-104.996809') as radius, "addresses".*, "addresses_organizations"."organization_id" as "pivot_organization_id", "addresses_organizations"."address_id" as "pivot_address_id" from "addresses" inner join "addresses_organizations" on "addresses"."id" = "addresses_organizations"."address_id" where "addresses_organizations"."organization_id" = 27854 having "radius" < 9)
User Gravatar

Forbin

Posted Mar 28 2014 9:09 UTC

Okay yeah I got something wrong, pSQL's HAVING clauses are evaluated before the SELECT so it won't work like that.

$addresses_with_radius = $org->addresses()
        ->select(DB::raw("cpdistance('addresses.coords','39.654872,-104.996809') as radius"))
        ->get();

That gets you all the addresses in the organization with their respective radius,

then you can just go

$addresses_within_radius = array_filter($addresses, function($address) {
  return ($address->radius < 9);
});

(Maybe you have to tweak it a little bit)

If you still want to go full pSQL on it can you create a small sqlfiddle for me with test data of both the organization and addresses tables, http://sqlfiddle.com/, that way I can get the query working and then to make it a Laravel expression would be a piece of cake.

User Gravatar

gmaliar

Posted Mar 28 2014 9:24 UTC

Solution

This didn't solve your task? Get your own custom solution.

Add a reply

By posting a reply on CodersClan you agree to our Terms & Conditions