Growler Growler - 5 months ago 15
MySQL Question

laravel append wheres in a loop to perform AND filtering

I'm trying to build my own dynamic filtering for my Angular App and Laravel 5.1 API using

$httpParamSerializer(params);
and Laravel
whereIns
.

My goal is to pass a set of fields and values I want to filter on and drill down the records I need.

I loop through the fields in Laravel and perform
whereIns
, then group them into an array, removing duplicates. Unfortunately, this acts more as an OR than an AND, as each
whereIn
does a new search to match.

Frontend Query:

var filters = {
'review[]' : ['no', 'yes'],
'user_id[]' : [1]
};


HTTP URL: "http://dde-api.localhost/1.0/userquestions?review%5B%5D=no&review%5B%5D=yes&user_id%5B%5D=1"

DB:

enter image description here

Laravel:

$results = [];
// Loop through each field (`review`, `users`, etc..), then search thru array of params
foreach ($input as $filter_field => $filters_array) {
if ($this->validField($table, $filter_field)) {
$res = DB::table($table)->whereIn($filter_field, $filters_array)->get();
if (!in_array($res, $results)) {
array_push($results, $res);
}


I need the query to work as a multiple
WHERE
clause (AND, not OR) which loops through and appends
where
clauses to each
field
($filter_field), then searches for matching field values.

So the result should be all
yes
and
no
records for user
1
. Since user 1 doesn't have a record with
review: yes
, it uses the record from
user_id: 4
. This is bad.

How can I append multiple WHERE statements to one query as I loop through multiple fields?

Answer

Use Your loop like this

$dbTbOBJ = \DB::table("table_name")
// Loop through each field (`review`, `users`, etc..), then search thru array of params
foreach ($input as $filter_field => $filters_array) {
         $dbTbOBJ->whereIn($filter_field, $filters_array);
}
$results = $dbTbOBJ->get()