Laravel MySQL Search. Allowing users to do custom Boolean Search

Question

I have this new requirement from client for an employee management project I am doing to allow their users to do custom boolean search.

basically allow them to use: AND, OR, NOT, brackets and quotes.

what is the best way to implement this? i have checked out mysql and they are using different syntax.

This is the test scenario:

Requirement: Search for Magicians or Barbarians having Elite or Veteran Level rank and who is from Singapore

Boolean string: ("Magician" OR "Barbarian") AND (Elite OR "Veteran Level") and Singaporean

This is one of the codes I will be consider to be modifying, which I got from Pure PHP based boolean search for strings

function booleanSearch($content, $search) {
    $criteria = str_getcsv($search, ' ');

    while ($criteria) {
        $not = false;
        $q = array_shift($criteria);

        if (substr($q, 0, 2) === '-"') {
            $not = true;

            while (substr($q, -1) != '"') {
                $q .= " " . array_shift($criteria);
            }

            $q = substr($q, 2, -1);
        }
        else if (substr($q, 0, 1) === '-' && strpos($q, ' ') === false) {
            $not = true;
            $q = substr($q, 1);
        }

        $found = strpos($content, $q) !== false;

        if ($found === $not) {
            return false;
        }
    }

    return true;
}

Do you already have some code or more context for us here to look at? — Quezler, May 06 '18 at 06:50
You need to write some sort of parser. It will not be easy. For example get all strings within parentheses (e.g. "Magician" OR "Barbarian") split get the tokens (e.g. `[Magician, OR, Barbarian]` and then since OR is a binary operator you get what's left and right of it for an OR comparison. NOT is right associative unary operator so you'd match it with the first thing on the right. Again, not going to be easy — apokryfos, May 06 '18 at 06:59

score 2 · Answer 1 · answered May 06 '18 at 11:47

You just need a query builder implementation. Based off you description it would be trivial to roll your own, otherwise, there are a number of packages out there to make life easier. Spatie's query builder works well: https://github.com/spatie/laravel-query-builder

Laravel MySQL Search. Allowing users to do custom Boolean Search

1 Answers1