How to add a select query to builder ?

How can i add a select query to a method? I tried adding a query to the zipcode method but it failed and returned an error


namespace App;

use Illuminate\Database\Eloquent\Builder;


class InventoryFilters extends QueryFilters
{

    public function years($years)
    {
        $years = explode(',',$years);
            return $this->builder->whereIn('year', $years);
    }

    public function colors($colors)
    {
        $colors = explode(',',$colors);
            return $this->builder->whereIn('color', $colors);
    }

    public function zipcode($zipcode)
    {
        return $this->builder->select("SELECT *,( 6371 * acos( cos( radians(32.715736) ) * cos( radians( `lati` ) ) * cos( radians( `long` ) - radians(-117.161087) ) + sin( radians(32.715736) ) * sin( radians( `lati` ) ) ) ) AS distance FROM `inventories` HAVING distance <= 75 ORDER BY distance ASC");
        
    }

    public function limit($count)
    {
        return $this->builder->limit($count);
    }
}

i get this error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`715736) ) * sin( radians( ``lati`` ) ) ) )` as `distance FROM ``inventories`` ' at line 1 (SQL: select `SELECT *,( 6371 * acos( cos( radians(32`.`715736) ) * cos( radians( ``lati`` ) ) * cos( radians( ``long`` ) - radians(-117`.`161087) ) + sin( radians(32`.`715736) ) * sin( radians( ``lati`` ) ) ) )` as `distance FROM ``inventories`` HAVING distance <= 75 ORDER BY distance ASC` from `inventories`)

I got all the code working but just adding this method gave me a error

https://laracasts.com/series/eloquent-techniques/episodes/4

tykus

8 years ago

Level 104

$this->builder->selectRaw("
    *,( 6371 * acos( cos( radians(32.715736) ) * cos( radians( `lati` ) ) * cos( radians( `long` ) - radians(-117.161087) ) + sin( radians(32.715736) ) * sin( radians( `lati` ) ) ) ) AS distance 
    FROM `inventories`
    HAVING distance <= 75
    ORDER BY distance ASC
");

willthinkk

8 years ago

Level 4

@tykus i tried that but it returns this error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from `inventories`' at line 6 (SQL: select 
*,( 6371 * acos( cos( radians(32.715736) ) * cos( radians( `lati` ) ) * cos( radians( `long` ) - radians(-117.161087) ) + sin( radians(32.715736) ) * sin( radians( `lati` ) ) ) ) AS distance 
FROM `inventories`
HAVING distance <= 75
ORDER BY distance ASC
from `inventories`)

tykus

8 years ago

Best Answer

Level 104

:facepalm: the selectRaw should only be the fields you want to select, not the from, where etc.

$this->builder
    ->selectRaw("*,( 6371 * acos( cos( radians(32.715736) ) * cos( radians( `lati` ) ) * cos( radians( `long` ) - radians(-117.161087) ) + sin( radians(32.715736) ) * sin( radians( `lati` ) ) ) ) AS distance")
    ->having('distance', '<=',  75)
    ->orderBy('distance')
    ->get();

willthinkk

8 years ago

Level 4

@tykus I now get this

SQLSTATE[42000]: Syntax error or access violation: 1463 Non-grouping field 'distance' is used in HAVING clause (SQL: select *,( 6371 * acos( cos( radians(32.715736) ) * cos( radians( `lati` ) ) * cos( radians( `long` ) - radians(-117.161087) ) + sin( radians(32.715736) ) * sin( radians( `lati` ) ) ) ) AS distance from `inventories` having `distance` <= 75 order by `distance` asc)

willthinkk

8 years ago

Level 4

@tykus I added 'strict' => false, to my config/database.php and it works Thank You So much for the help, this was driving me nuts

Please or to participate in this conversation.