Could you recommend a JQuery plugin to compose a set of conditions mappable to a SQL query?

Question

I've found http://redquerybuilder.appspot.com/ but that generates SQL client side which I want to avoid. On hat page there is a link to JQuery Query Builder plugin but that link goes to jquery home page. Seems that this plugin does nto exist anymore (also see Simple SQL Query Builder in JQuery for same link).

I found http://kindohm.com/posts/2013/09/25/knockout-query-builder/ which looks pretty much what I want except I do not want to add yet another JavaScript library.

Last there is http://devtools.korzh.com/easyquery/javascript/docs/javascript-query-builder-php which looks very nice. But they use a web service to generate SQL and you have to get an API key for it to work. For now it's free...but looks like a nice trap to lure in users and then when they can't easily get away, the will probably start to charge for the web service or can shut it down any time they want.

So before I just build a custom tailored query form, does such a query builder exist at all?

Answer 1

I needed a query builder which generates a nice JSON I could use to create Java POJO and wrote this :
http://mistic100.github.io/jQuery-QueryBuilder

It would be easy to write a parser which create SQL queries.

Answer 2

I recommend Mistic's work. Pros of this choice:

• if you don't use Bootstrap, you can always extract the only classes used by the plugin and merge them in query.builder.css, modifing them as you need it.
• I've tested it with other plugins with no problem like jquery MultiSelect and jquery TimePicker
• there's an option to disable subgroups. if you want only a two level structure (no subgroups of subgroups), you can use an event to hide the group button after creating a new group rule.
• You can easily parse JSON in PHP. Put the case you call $('#builder').builder('getRules') in your client code and you assign the result to a variable c, which you'll post as you want:

$operators = array('equal' => "=", 
                   'not_equal' => "!=",
                   'in' => "IN (?)",
                   'not_in' => "NOT IN (_REP_)", 
                   'less' => "<", 
                   'less_or_equal' => "<=", 
                   'greater' => ">", 
                   'greater_or_equal' => ">=",
                   'begins_with' => "ILIKE",
                   'not_begins_with' => "NOT ILIKE",
                   'contains' => "ILIKE",
                   'not_contains' => "NOT ILIKE",
                   'ends_with' => "ILIKE",
                   'not_ends_with' => "NOT ILIKE",
                   'is_empty' => "=''",
                   'is_not_empty' => "!=''", 
                   'is_null' => "IS NULL", 
                   'is_not_null' => "IS NOT NULL"); 

        $jsonResult = array("data" => array());
        $getAllResults = false;
        $conditions = null;
        $result = "";
        $params = array();
        $conditions = json_decode(utf8_encode($_POST['c']), true);

        if(!array_key_exists('condition', $conditions)) {
            $getAllResults = true;
        } else {

            $global_bool_operator = $conditions['condition'];

            // i contatori servono per evitare di ripetere l'operatore booleano
            // alla fine del ciclo se non ci sono più condizioni
            $counter = 0;
            $total = count($conditions['rules']);

            foreach($conditions['rules'] as $index => $rule) {
                if(array_key_exists('condition', $rule)) {
                    $result .= parseGroup($rule, $params);
                    $total--;
                    if($counter < $total)
                       $result .= " $global_bool_operator ";
                } else {
                    $result .= parseRule($rule, $params);
                    $total--;
                    if($counter < $total)
                       $result .= " $global_bool_operator ";
                }
            }
        }

/**
 * Parse a group of conditions */
function parseGroup($rule, &$param) {
    $parseResult = "(";
    $bool_operator = $rule['condition'];
    // counters to avoid boolean operator at the end of the cycle 
    // if there are no more conditions
    $counter = 0;
    $total = count($rule['rules']);

    foreach($rule['rules'] as $i => $r) {
        if(array_key_exists('condition', $r)) {
            $parseResult .= "\n".parseGroup($r, $param);
        } else {
            $parseResult .= parseRule($r, $param);
            $total--;
            if($counter < $total)
                $parseResult .= " ".$bool_operator." ";
        }
    }

    return $parseResult.")";
}

/**
 * Parsing of a single condition */
function parseRule($rule, &$param) {

    global $fields, $operators;

    $parseResult = "";
    $parseResult .= $fields[$rule['id']]." ";

    if(isLikeOp($rule['operator'])) {
       $parseResult .= setLike($rule['operator'], $rule['value'], $param);
    } else {
       $param[] = array($rule['type'][0] => $rule['value']);
       $parseResult .= $operators[$rule['operator']]." ?";                
    }
    return $parseResult;
}

Answer 3

Here is your answer.

Please download from Here

https://github.com/gantir/jsexpbuilder

Which you looking for.