CODEWITHSUNDEEP
phpsqllaravellaravel-5query-builder
mikelovelyuk
mikelovelyuk

Reputation: 4152

orWhere to orWhereIn with Laravel syntax and raw queries

To get an SQL Statement like this;

SELECT postcode FROM Payment WHERE 
  countries IN ('E92000001', 'L93000001') AND 
  counties IN ('95', 'E10000002') AND 
  gors in ('A', 'B');

My code is currently;

$postcode_extract = PostcodeExtract::all();

foreach ($input as $column => $values) {
            $postcode_extract->whereIn($column, $values);
    }

I need to switch those ANDs to ORs. I think I might have it;

foreach ($input as $column => $values) {
    $postcode_extract->orWhere(function ($query) use ($column, $values) {
                                $query->whereIn($column, $values);
                                });
        }

I do get results.. It's hard to tell without seeing the raw query though. I used toSql() and this is the result;

select * from `201502_postcode` where (`country` in (?, ?)) or (`county` in (?, ?)) or (`gor` in (?, ?))

Why the ?s though? Why not the real data or is that not how toSql works?

-- UPDATE --

Here is my code using toSql;

$sql = $postcode_extract->toSql();

print_r($sql);

I do it after the foreach loop

Upvotes: 1

Views: 2232

Answers (1)

Marcin Nabiałek
Marcin Nabiałek

Reputation: 111859

Laravel uses prepared statements so to get sql query and arguments you need to use not only toSql() method but also getBindings(), so to get full query you could use for example:

$sql = str_replace(['%', '?'], ['%%', "'%s'"], $postcode_extract->toSql());
$fullSql = vsprintf($sql, $postcode_extract->getBindings());

Upvotes: 1

Related Questions