I've used passing a query object to a sub-function many times, with no issues. However, when I want an OR relationship, the query builder is very OCD and always gives me AND!
Business Logic: site_id = $site_id AND (cond1 OR cond2 OR ..)
cond1..n is taken from $cols
any are matched $search
.
$cols = "priority, status, name, actionType"
$search
is the string to be searched for, in this case: Medium
The issue here is not the missing column, that's there to create the error to spit out the SQL. The issue how to get Laravel to behave properly with orWhere and parenthesis.
First method:
$data = $this->where('site_id', $site_id) ;
$data = $this->search($cols, $orderBy, $search, $data) ;
Second method (search(...)):
$searches = explode(',', $col);
$len = sizeof($searches) ;
if ($len > 0) {
for ($i=0; $i < $len ; $i++) {
$val = trim($searches[$i]) ;
$data->where(function ($query) use ($val, $search, $data) {
$query->orWhere(function ($query1) use ($val, $search, $data) {
$query1->orWhereRaw("$val LIKE '%$search%'");
}) ;
}) ;
I've deliberately left an unknown column to see the SQL Laravel generates:
Unknown column 'name' in 'where clause' (SQL: select count(*) as aggregate from `actions` where `site_id` = 555 and ((priority LIKE '%Medium%')) and ((status LIKE '%Medium%')) and ((name LIKE '%Medium%')) and ((actionType LIKE '%Medium%')))
As you can see, despite multiple orWhere's I still get ANDs! Because the second method is in the base model, it's designed for all queries, I trying to avoid excessive duplicated searching functions.
The SQL is nearly what I want.
If I omit the following code:
// $data->where(function ($query) use ($val, $search, $data) {
$data->orWhere(function ($query1) use ($val, $search, $data) {
$query1->orWhereRaw("$val LIKE '%$search%'");
}) ;
// }) ;
With the result of:
Unknown column 'name' in 'where clause' (SQL: select count(*) as aggregate from `actions` where `site_id` = 555 or (priority LIKE '%Medium%') or (status LIKE '%Medium%') or (name LIKE '%Medium%') or (actionType LIKE '%Medium%')
from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2zpRG4A
via IFTTT
Aucun commentaire:
Enregistrer un commentaire