How to walk through the following nested array to build the correct query?

I am getting a nested array from the frontend that is selections made by users that I need to then filter results by these selections. The selections are based on company's hierarchy, for example:

// dd($selections)
[
    "group2" => [
        "unit11" => [
            "department50" => [
                0 => "team10",
                1 => "team58"
             ],
        ],    
        "unit10" => [],
    ],

    "group5" => [
        "unit23" => [
            "department101" => []                
        ],
    ],    
]

These are selections made in the frontend and I need to filter results by it. Each key points to the next level in the hierearchy, except for the last level (teams in this case, which is an array containing the selected teams as values, not keys)

I need to walk through the array in order to build a query similar to the following:

$fullQuery = DB::table('mytable');

$fullQuery
    ->where(function($query) {
        $query->where("group", "=", 'group2')
            ->where("unit", "=", 'unit11')
            ->where("department", "=", 'department50')
            ->where("team", "=", 'team10')
    })
    ->orWhere(function($query) {
        $query->where("group", "=", 'group2')
            ->where("unit", "=", 'unit11')
            ->where("department", "=", 'department50')
            ->where("team", "=", 'team58')
    })
    ->orWhere(function($query) {
        $query->where("group", "=", 'group5')
            ->where("unit", "=", 'unit23')
            ->where("department", "=", 'department101')                
    })

I thought about a few ways:

To "flatten" the array and build separated arrays like that:

arrays = [
    ['group' => 'group2', 'unit' => 'unit11', 'department' => 'department50', 'team' => 'team10'],
    ['group' => 'group2', 'unit' => 'unit11', 'department' => 'department50', 'team' => 'team58'],
    ['group' => 'group5', 'unit' => 'unit23', 'department' => 'department101'],
]

This way I can build the query with the same structure in a loop:

foreach ($arrays as $array) {
$fullQuery->orWhere(function($query) {
    $query->where("group", "=", $array->group)
        ->where("unit", "=", $array->unit)
        ->where("department", "=", $array->department)
        ->where("team", "=", $array->team)
    })
}

Build the above query but do it as I walk through the array. But in this case, the structure would be dynamic and I think it's harder to do.

But maybe there is an easier way to do that? I think that either way requires recursion, perhaps there's some simpler way though?

tisuchi

3 years ago

Best Answer

Level 70

@ligonsker (Untested), one way to simplify this process would be to use a recursive function to iterate through the nested array and build the query. Here is an example of how you could do this:


public function buildQuery($selections, $query)
{
    foreach ($selections as $key => $value) {
        if (is_array($value)) {
            $query->where($key, $key);
            $this->buildQuery($value, $query);
        } else {
            $query->orWhere($key, $value);
        }
    }

    return $query;
}

public function filterResults($selections)
{
    $query = DB::table('mytable');

    $fullQuery = $this->buildQuery($selections, $query);

    return $fullQuery->get();
}

The buildQuery function will recursively iterate through the nested array, adding a where clause for each key and value. When it reaches a value that is not an array (i.e. a team), it will add an orWhere clause for that value.

You can then call the filterResults function and pass in the $selections array as an argument. This will return a query that filters the results based on the selections made by the user.

1 like

Please or to participate in this conversation.