| 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.
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' 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.
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});
})
}
})
});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.
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?
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 :)
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent