Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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)

  1. I have a method for assigning new filters. $search->addFilter($fieldname, $value, $operator, $fieldLinkId, $fieldLinkOperator);
  2. 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)
  3. 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 ? ')' : '');
    }
    
  4. 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?

share|improve this question
    
I think the way you're going about it is a bit weird. why would you construct nested conditions as a flat array? Your conditions array in php for the first example could be $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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.