ianvizarra/query-builder-parser

QueryBuilderParser

Status Label	Status Value
Build
Insights
Code Climate
Test Coverage

QueryBuilderParser is designed mainly to be used inside Laravel projects, however it can be used outside Laravel projects by using Illuminate/Database.

A simple to use query builder for the jQuery QueryBuilder plugin.

Using QueryBuilderParser

Building a new query from QueryBuilder rules.

    use timgws\QueryBuilderParser;

    $table = DB::table('table_of_data_to_integrate');
    $qbp = new QueryBuilderParser(
        // provide here a list of allowable rows from the query builder.
        // NOTE: if a row is listed here, you will be able to create limits on that row from QBP.
        array( 'name', 'email' )
    );

    $query = $qbp->parse($input['querybuilder'], $table);

    $rows = $query->get();
    return Response::JSON($rows);

This query when posted will create the following SQL query:

SELECT * FROM table_of_data_to_integrate WHERE `name` LIKE '%tim%' AND `email` LIKE '%@gmail.com'

Getting results from MongoDB

    use timgws\QueryBuilderParser;

    $table = DB::collection('data');
    $qbp = new QueryBuilderParser(
        // provide here a list of allowable rows from the query builder.
        // NOTE: if a row is listed here, you will be able to create limits on that row from QBP.
        array( 'name', 'email' )
    );

    $query = $qbp->parse($input['querybuilder'], $table);

    $rows = $query->get();
    return Response::JSON($rows);

This query when posted will create the following MongoDB query:

    {
      "$and": [
        {
          "name": {
            "$regex": "tim"
          }
        },
        {
          "email": {
            "$regex": "@gmail\\.com$"
          }
        }
      ]
    }

Note that to use this you will need to install and configure jenssegers/mongodb.

Integration examples

Integrating with jQuery Datatables

Mixed with Datatables, jQuery QueryBuilder makes for some true awesome, allowing limitless options for filtering data, and seeing the results on the fly.

    use timgws\QueryBuilderParser;
    
    class AdminUserController {
        function displayUserDatatable() {
            /* builder is POST'd by the datatable */
            $queryBuilderJSON = Input::get('rules');
            
            $show_columns = array('id', 'username', 'email_address');
            
            $query = new QueryBuilderParser($show_columns);
            
            /** Illuminate/Database/Query/Builder $queryBuilder **/
            $queryBuilder = $query->parse(DB::table('users'));
            
            return Datatable::query($queryBuilder)
                ->showColumns($show_columns)
                ->orderColumns($show_columns)
                ->searchColumns($show_columns)
                ->make()
        }
    }

On the client side, a little bit of magic is required to make everything work.

    // the default rules, what will be used on page loads...
    var datatablesRequest = {};
    var _rules = defaultRules = {"condition":"AND","rules":[
        {"id":"active","field":"active","type":"integer","input":"radio","operator":"equal","value":"1"}
    ]};

    // a button/link that is used to update the rules.
    function updateFilters() {
        _rules = $('#querybuilder').queryBuilder('getRules');
        reloadDatatables();
    }

    function filterChange() {
        var _json = JSON.stringify( _rules );
        datatablesRequest = { rules: _json };
    }

    filterChange();

    function reloadDatatables() {
        /* Datatables first... */
        filterChange();

        $('.dataTable').each(function() {
            dt = $(this).dataTable();
            dt.fnDraw();
        })
    }

    jQuery(document).ready(function(){
        // dynamic table
        oTable = jQuery('.datatable').dataTable({
            "fnServerParams": function(aoData) {
                // add the extra parameters from the jQuery QueryBuilder to the Datatable endpoint...
                $.each(datatablesRequest , function(k,v){
                    aoData.push({"name": k, "value": v});
                })
            }
        })
    });

Using JoinSupportingQueryBuilderParser

JoinSupportingQueryBuilderParser is a version of QueryBuilderParser that supports building even more complex queries.

    $joinFields = array(
        'join1' => array(
            'from_table'      => 'master',
            'from_col'        => 'm_col',
            'to_table'        => 'subtable',
            'to_col'          => 's_col',
            'to_value_column' => 's_value',
        ),
        'join2' => array(
            'from_table'      => 'master2',
            'from_col'        => 'm2_col',
            'to_table'        => 'subtable2',
            'to_col'          => 's2_col',
            'to_value_column' => 's2_value',
            'not_exists'      => true,
        )
    );

    $table = DB::table('table_of_data_to_integrate');
    $jsqbp = new JoinSupportingQueryBuilderParser($fields, $this->getJoinFields());
    $test = $jsqbp->parse($json, $builder);

Which will build an SQL query similar to:

select * where exists (select 1 from `subtable` where subtable.s_col = master.m_col and `s_value` < ?)

For simple queries, QueryBuilderParser should be enough.

Exporting CSV files

Just as a footnote, there are right ways to export CSV files, and there are wrong ways.

For the right way, check out the question on StackOverflow, How can I output a UTF-8 CSV in PHP that Excel will read properly?

Reporting Issues

I use this code in a number of my projects, so if you do find an issue, please feel free to report it with GitHub's bug tracker for this project.

Alternatively, fork the project and make a pull request :)