CODEWITHSUNDEEP
phpmysqllaravel
Kaizokupuffball
Kaizokupuffball

Reputation: 2843

Laravel query builder raw AND statement

I need to run a raw SQL query part in Laravel.

The raw SQL would look basically like this:

AND (forretningsadresse_fylke = 'HEDMARK' 
    AND (forretningsadresse_kommune = 'HAMAR') 
    OR (forretningsadresse_kommune = 'ELVERUM')
) 
OR (forretningsadresse_fylke = 'HORDALAND' 
    AND (forretningsadresse_kommune = 'BERGEN')
)

I try to use:

$result = Company::where(function($a) use($input, $sql) {

        // Name or keywords LIKE string
        $a -> where('navn', 'LIKE', '%'. $input['query_string'] .'%');
        $a -> where('keywords', 'LIKE', '%'. $input['query_string'] .'%');
        if (!empty($sql)) {
            $a -> where(DB::statement($sql));
        }

    });

    $result = $result -> take($input['limit'])       // Take $limit
                      -> offset($input['offset'])    // Offset by $offset
                      -> orderBy('rank', 'DESC')     // Sponsors first
                      -> get();

But it doesn't work. See the error below. How would I get this to work? Thanks in advance!

QueryException in Connection.php line 673: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND (forretningsadresse_fylke = 'AKERSHUS')' at line 1 (SQL: AND (forretningsadresse_fylke = 'AKERSHUS') )

EDIT: The table has 1 million rows, and I use this method to create the raw query: https://i.sstatic.net/v0r79.png

Upvotes: 2

Views: 3317

Answers (2)

oseintow
oseintow

Reputation: 7391

Try 

$result = Company::where('navn', 'LIKE', '%'. $input['query_string'] .'%')
    ->where('keywords', 'LIKE', '%'. $input['query_string'] .'%')
    ->where(function($query){
        $query->where('forretningsadresse_fylke', '=', 'HEDMARK' )
              ->where('forretningsadresse_kommune', '=', 'HAMAR')
              ->orWhere('forretningsadresse_kommune','=', 'ELVERUM');
    })
    ->where(function($query){
        $query->orWhere('forretningsadresse_fylke','=', 'HORDALAND')
              ->where('forretningsadresse_kommune', '=', 'BERGEN');
   })
   ->take($input['limit'])       // Take $limit
   ->offset($input['offset'])    // Offset by $offset
   ->orderBy('rank', 'DESC')     // Sponsors first
   ->get();

hope it helps

Upvotes: 1

Luís Cruz
Luís Cruz

Reputation: 14970

There are two issues with your code:

  1. You are using DB::statement where you should be using DB::raw. DB::statement will execute the string that you pass as an argument and that is not what you need. You need to pass a raw statement to be included to the query builder.
  2. Assuming the raw block you posted is the $sql parameter, then you need to remove the starting AND, otherwise the query will end up with .... and AND (, which will throw an error.

Given this, you need to update your code to:

// Notice the removal of the `AND`
$sql = "(forretningsadresse_fylke = 'HEDMARK' 
        AND (forretningsadresse_kommune = 'HAMAR') 
        OR (forretningsadresse_kommune = 'ELVERUM')
    ) 
    OR (forretningsadresse_fylke = 'HORDALAND' 
        AND (forretningsadresse_kommune = 'BERGEN')
    )";

$result = Company::where(function($a) use($input, $sql) {
    // Name or keywords LIKE string
    $a->where('navn', 'LIKE', '%'. $input['query_string'] .'%');
    $a->where('keywords', 'LIKE', '%'. $input['query_string'] .'%');
    if (!empty($sql)) {
        // Using DB::raw instead of DB::statement
        $a->where(DB::raw($sql));
    }
});

Upvotes: 2

Related Questions