I need to convert a multidimensional array of sql where statements to a string with the correct operators and parenthesizes. I want to end up with a string that looks like this:
WHERE (author_id=1 || author_id=2) && status=1
The key here is that I need to create a "nested" string, i.e. have conditionals in my string wrapped with parenthesizes at the right spots.
It seems easy enough, but I am wool-headed today and can't come up with anything other than the below - and it feels like poor design.
Short explanation - How to convert the following array into the above, with any kind of links between filters
$aFilters[$filter_id] = array(
'field' => $field,
'value' => $value,
'operator' => $operator, /** = or != */
'link_id' => $another_filter_id,
'link_operator' => $link_operator, /** && or || */
);
Long explanation (My attempt)
- I have a method for assigning new filters.
$search->addFilter($fieldname, $value, $operator, $fieldLinkId, $fieldLinkOperator);
- I then create two arrays based on this.
- $filters - This contains the raw data from the addFilter() method, with the array key as filter ID
- $filterMap - This contains a map of all filter IDs and their children. i.e [1] => {2,3,4} (filter 1 is linked to filter 2,3 and 4)
I then have a very ugly recursive function to create my string (below)
private function buildCriteriaStringSection($aFilters, $aFilterMap, $aFilterID = 0) { $bHasChildrenLinks = isset($aFilterMap[$iFilterID]); $str = $aFilters[$iFilterID]['link_operator']; $str .= (($bHasChildrenLink && $iFilterID != 0) ? '(' : ''); $str .= $aFilters[$iFilterID]['field'] . $aFilters[$iFilterID]['operator'] . $aFilters[$iFilterID]['value']; if($bHasChildrenLinks) { foreach($aFilterMap[$iFilterID] as $key => $iChildFilterId) { $str .= $this->buildCriteriaStringSection($aFilters, $aFilterMap, $iChildFilterId); } } return $str .= ($bHasChildrenLinks && $iFilterID != 0 ? ')' : ''); }
At the very last I even have to trim off some operators that have been added due to to the structure above
$str = ltrim($this->buildCriteriaString(), '&|=');
So how do I make this more sophisticated?
$conditions = ['OR' => [['author_id' => 1], ['author_id' => 2]], 'status' => 1]
I.e. the php representation of your nested sql conditions would be a nested array. I recommend looking at how any well known php framework/ORM does what you're doing - and if you really want to write your own solution, base it on what you see. – AD7six 2 days ago