See Extension only
Source Code

Ext Grid Query Builder Example

This example shows how to use Query Builder.

You can also save queries as public and private for future use

You can add filter fields, where Conditions, additional fields from database table

Here is the fresh query used to load the grid at time of rendring

SELECT c.* DATE_FORMAT(created, '%b %e, %Y') as created_date, DATE_FORMAT(modified, '%b %e, %Y') as modified FROM com_extensiondemo_content c

Additional fields can be added dynamically to the grid (See example "News Section categories which are published", below is the resultant query )

SELECT c.* , cat.title as cattitle , DATE_FORMAT(created, '%b %e, %Y') as created_date, DATE_FORMAT(modified, '%b %e, %Y') as modified FROM com_extensiondemo_content c,com_extensiondemo_categories cat WHERE 1=1 AND ( c.id=cat.id and cat.section = 1 and cat.published = '1' )


<!-- QueryBuilder ux Start-->
<script type="text/javascript" 	src="http://developerextensions.com/components/com_extensiondemo/views/extensiondemo/tmpl/ext3/ux/queryBuilder/queryBuilder.js"></script>
<script type="text/javascript" 	src="http://developerextensions.com/components/com_extensiondemo/views/extensiondemo/tmpl/ext3/ux/queryBuilder/queryBuilderForm.js"></script>
<script type="text/javascript" 	src="http://developerextensions.com/components/com_extensiondemo/views/extensiondemo/tmpl/ext3/ux/queryBuilder/queryBuilderFormWindow.js"></script>
<!-- QueryBuilder ux End-->
<script type="text/javascript">
Ext.onReady(function(){	
					 
	var sampleGridColumns= [{
		header: 'Id',
		width:90,
		dataIndex: 'id'
	},{
		header: 'Title', 
		dataIndex: 'title',
		sortable: true,
		width:150
	},{
		header: 'Alias', 
		dataIndex: 'alias',
		sortable: true,
		width:130
	},{
		header: 'Created',
		width:80,
		dataIndex: 'created_date'
	},{
		header: 'Modified',
		width:80,
		sortable: true,
		dataIndex: 'modified',
	},{
		header: 'Published', 
		dataIndex: 'state',
		sortable: true,
		renderer:function(v){if(v==1){return 'Published'}else{return '<span style="color:red">UnPublished</span>'}}
	}];
	
	var sampleGridReader = new Ext.data.JsonReader({
			totalProperty: 'total',
			successProperty: 'success',
			idProperty: 'id',
			root: 'data'
		},[
			{name: 'id'},
			{name: 'title'},
			{name: 'created'},
			{name: 'state'},
			{name: 'alias'},
			{name: 'created_date'},
			{name: 'modified'}
	]);
		
	
	// Typical Store collecting the Proxy, Reader and Writer together.
	var sampleGridStore = new Ext.data.Store({
		reader		: sampleGridReader,
		autoLoad	: true,	
		url			: scriptUrl+'ux/queryBuilder/example/index.php',
	});
	
	// create the Grid
	var grid = new Ext.grid.GridPanel({
		tbar:[{ 
			text: 'Query Builder', 
			icon: scriptUrl+'ux/queryBuilder/images/icons/querybuilder.png',
			handler: function(){
				queryBuilder.show();
			}, 
			scope: this
		}],
		store: sampleGridStore,
		renderTo:'queryBuilderGridExample',
		columns: sampleGridColumns,	
		stripeRows: true,
		height: 350,
		title: 'Grid with QueryBuilder'
	});
	
	var queryBuilder = new Ext.ux.QueryBuilder({
		title					: 'Query Builder',
		border					: true,
		width					: 1000,
		y_						: 200,
		height					: 200,
		grid					: grid,
		filePath				: scriptUrl+'ux/queryBuilder/', 
		treeDataUrl				: scriptUrl+'ux/queryBuilder/example/index.php?task=queryBuilder&subtask=treeData&mainquery=contents',
		fieldStoreUrl			: scriptUrl+'ux/queryBuilder/example/index.php?task=queryBuilder&subtask=fields&mainquery=contents',
		multipleValuesStoreUrl	: scriptUrl+'ux/queryBuilder/example/index.php?task=queryBuilder&subtask=multipleValues',
		querySaveUrl			: scriptUrl+'ux/queryBuilder/example/index.php?task=queryBuilder&subtask=saveQuery&mainquery=contents',
		countRecordUrl			: scriptUrl+'ux/queryBuilder/example/index.php?task=queryBuilder&subtask=countRecord&mainquery=contents',
		parentNodesComboStoreUrl: scriptUrl+'ux/queryBuilder/example/index.php?task=queryBuilder&subtask=parentNodes'
	});
	
	queryBuilder.on('run', function(filterObject){		
		this.grid.store.baseParams = {};		
		this.grid.store.baseParams['filterQuery'] = filterObject.filter;		
		this.grid.store.reload();
	});
});

</script>
<h1 class="extensionHeading">Ext Grid Query Builder Example</h1>
<p>This example shows how to use Query Builder.</p>
<p>You can also save queries as public and private for future use </p>
<p>You can add filter fields, where Conditions, additional fields from database table </p>
<p>Here is the fresh query used to load the grid at time of rendring</p>
<p class="query">SELECT c.*  DATE_FORMAT(created, '%b %e, %Y') as created_date, DATE_FORMAT(modified, '%b %e, %Y') as modified FROM com_extensiondemo_content c</p>
<p>Additional fields can be added dynamically to the grid (See example "News Section categories  which are published", below is the resultant query )</p>
<p class="query">SELECT c.* , <span class="dynamicField">cat.title as cattitle</span> , DATE_FORMAT(created, '%b %e, %Y') as created_date, DATE_FORMAT(modified, '%b %e, %Y') as modified FROM com_extensiondemo_content c,<span class="dynamicTable">com_extensiondemo_categories cat </span>WHERE 1=1 AND (<span class="whereCondition"> c.id=cat.id and cat.section = 1 and cat.published = '1' </span>)</p>
<p><br /></p>
<div id="queryBuilderGridExample"></div>
Table:com_extensiondemo_querybuilder_fields
idcolumnNamecolumnAliasdataTypevalueTypetablemultipleValuesmainqueryconditionmultipleValues2additionalFieldspublished
Ext.onReady(function(){	
					 
	var sampleGridColumns= [{
		header: 'Id',
		width:90,
		dataIndex: 'id'
	},{
		header: 'Title', 
		dataIndex: 'title',
		sortable: true,
		width:150
	},{
		header: 'Alias', 
		dataIndex: 'alias',
		sortable: true,
		width:130
	},{
		header: 'Created',
		width:80,
		dataIndex: 'created_date'
	},{
		header: 'Modified',
		width:80,
		sortable: true,
		dataIndex: 'modified',
	},{
		header: 'Published', 
		dataIndex: 'state',
		sortable: true,
		renderer:function(v){if(v==1){return 'Published'}else{return '<span style="color:red">UnPublished</span>'}}
	}];
	
	var sampleGridReader = new Ext.data.JsonReader({
			totalProperty: 'total',
			successProperty: 'success',
			idProperty: 'id',
			root: 'data'
		},[
			{name: 'id'},
			{name: 'title'},
			{name: 'created'},
			{name: 'state'},
			{name: 'alias'},
			{name: 'created_date'},
			{name: 'modified'}
	]);
		
	
	// Typical Store collecting the Proxy, Reader and Writer together.
	var sampleGridStore = new Ext.data.Store({
		reader		: sampleGridReader,
		autoLoad	: true,	
		url			: scriptUrl+'ux/queryBuilder/example/index.php',
	});
	
	// create the Grid
	var grid = new Ext.grid.GridPanel({
		tbar:[{ 
			text: 'Query Builder', 
			icon: scriptUrl+'ux/queryBuilder/images/icons/querybuilder.png',
			handler: function(){
				queryBuilder.show();
			}, 
			scope: this
		}],
		store: sampleGridStore,
		renderTo:'queryBuilderGridExample',
		columns: sampleGridColumns,	
		stripeRows: true,
		height: 350,
		title: 'Grid with QueryBuilder'
	});
	
	var queryBuilder = new Ext.ux.QueryBuilder({
		title					: 'Query Builder',
		border					: true,
		width					: 1000,
		y_						: 200,
		height					: 200,
		grid					: grid,
		filePath				: scriptUrl+'ux/queryBuilder/', 
		treeDataUrl				: scriptUrl+'ux/queryBuilder/example/index.php?task=queryBuilder&subtask=treeData&mainquery=contents',
		fieldStoreUrl			: scriptUrl+'ux/queryBuilder/example/index.php?task=queryBuilder&subtask=fields&mainquery=contents',
		multipleValuesStoreUrl	: scriptUrl+'ux/queryBuilder/example/index.php?task=queryBuilder&subtask=multipleValues',
		querySaveUrl			: scriptUrl+'ux/queryBuilder/example/index.php?task=queryBuilder&subtask=saveQuery&mainquery=contents',
		countRecordUrl			: scriptUrl+'ux/queryBuilder/example/index.php?task=queryBuilder&subtask=countRecord&mainquery=contents',
		parentNodesComboStoreUrl: scriptUrl+'ux/queryBuilder/example/index.php?task=queryBuilder&subtask=parentNodes'
	});
	
	queryBuilder.on('run', function(filterObject){		
		this.grid.store.baseParams = {};		
		this.grid.store.baseParams['filterQuery'] = filterObject.filter;		
		this.grid.store.reload();
	});
});
<?php
    error_reporting
(E_ALL&~E_NOTICE);
    include(
'db.php');
    
    
/***
    *helper function
    ***************/
    
function RequestGetVar($key$default=''){
        if(isset(
$_REQUEST[$key])){
            return 
$_REQUEST[$key];
        }
        return 
$default;
    }
    
    
/***********************************
    *Query builder task are handled under this function
    ************************************************************/
    
function queryBuilder($subtask$mainquery){
        
$userId                        123;//dummy user id, you can put here user id from session
        
        
switch( $subtask ){            
            case 
'treeData':
                
$node                RequestGetVar'node''Root' );
                
$result                getQueryBuilderTreeData$node$mainquery );
            break;
            case 
'fields':
                
$result                = new stdClass();
                
$result->fields        getQueryBuilderFields$mainquery );
                
$result->total        count$result->fields );
            break;
            case 
'multipleValues':
                
$multipleValues        RequestGetVar'multipleValues''' );
                
$table                RequestGetVar'table''' );
                
$result                = new stdClass();
                
$result->data        getQueryBuilderMultipleValues$multipleValues$table );
                
$result->total        count$result->data );
            break;
            case 
'parentNodes':
                
$result                    = new stdClass();                    
                
$publicQueries             = new stdClass( );
                
$publicQueries->text     'Public';
                
$publicQueries->id         'Public';
                if(
$userId){//if logged in user
                    
$privateQueries         = new stdClass( );
                    
$privateQueries->text     'Private';
                    
$privateQueries->id     'Private';
                    
$result->data             = array( $publicQueries$privateQueries );
                }else{
                    
$result->data             = array( $publicQueries );
                }
                
$result->total        count$result->data );
            break;
            case 
'countRecord':
                
$filter                RequestGetVar'filter''' );
                
$result                getQueryBuilderRecord$filter$mainquery );
                
$result                count$result );
            break;
            case 
'saveQuery':
                include(
'tableQuery.php');
                
$table                = new TableQuery('com_extensiondemo_querybuilder_queries''id');
                
$post                $_REQUEST;                
                
$post['createdBy']  = $userId;
                
$result                = new stdClass();
                if( 
$table->bind($post) ){
                    if ( 
$table->store(false) ) {
                        
$result->feedback    'Details Saved' ;
                        
$result->id         = (int) $table->get('id');    
                        
$result->success      true;            
                    } else {
                        
$result->feedback    $table->getError();
                        
$result->success      false;
                    }
                }else{
                    
$result->success          false;
                    
$result->feedback         $table->getError();
                }        
            break;            
        }        
        
        return 
json_encode($result);
        
    }
    
    
/*************************************
    * Return the private and public queries list
    ****************************************************************/
    
function getQueryBuilderTreeData$parent 'Root' $mainquery='contents' ){
        
        
$userId    123;//dummy user id, you can put here user id from session
        
        
if( $parent == 'Root' ){
            
$publicQueries             = new stdClass( );
            
$publicQueries->text     'Public';
            
$publicQueries->id         'Public';
            
$publicQueries->cls        'folder';
            if(
$userId){//if logged in user
                
$privateQueries         = new stdClass( );
                
$privateQueries->text     'Private';
                
$privateQueries->id     'Private';
                
$privateQueries->cls    'folder';
                return array( 
$publicQueries$privateQueries );
            }
            
            return array( 
$publicQueries );
        }else{
            
$sql     "Select title as text, 'true' as leaf, id, json from com_extensiondemo_querybuilder_queries where parent = '{$parent}' AND mainquery='{$mainquery}' ";
            if( 
$parent=='Private' ){
                
$sql .= " AND createdBy = '{$userId}' ";
            }
            
            return 
loadObjectlist$sql );
        }        
    }
    
    
/*************************************
    * Return the Query Builder Fields
    ****************************************************************/
    
function getQueryBuilderFields$mainquery 'contents'  ){        
        
$sql     "Select * from com_extensiondemo_querybuilder_fields WHERE mainquery='{$mainquery}' and published=1";        
        return 
loadObjectlist($sql);
    }
    
    
/*************************************
    * Return the 'multiple value' field data
    ****************************************************************/
    
function getQueryBuilderMultipleValues$query $table=''){        
        return 
loadObjectlist$query );
    }
    
    
    
    
/****
    *Return the table to be used for builing query
    **********/
    
function getTables($filterQuery){
        
$tables            = array ('com_extensiondemo_content c');
        if( 
$filterQuery !='' ){
            
$filterQuery              json_decode($filterQuery);
            foreach(
$filterQuery->tables as $table){
                
$table trim($table);
                if( 
$table!=='com_extensiondemo_content' && $table!=='c' && !in_array($table$tables)){
                    
$tables[] = $table;
                }
            }
        }
        return 
implode(','$tables);        
    }
    
    
/*************************************
    * Return resultset returned from the query built upon given filter
    ****************************************************************/
    
function getQueryBuilderRecord$filter$mainquery='contents' ){        
        
$filterQuery              json_decode($filter);
        
$whereCondition              $filterQuery->where;        
        
$tables                    getTables($filter);        
        
        
$query "SELECT * FROM  ".$tables." WHERE 1=1 AND ("$whereCondition .")";            
        
        return 
loadObjectlist$query );        
    }
    
    
/**
     * Gets Atricals from the com_extensiondemo_content table by filtering them accroding to $filter option
     *
     *
     * @access public
     * @return records found
     */
    
function contents$filter ="" ){        
        
$tables         getTables($filter);
        
$whereCondition    '1=1';
        if( 
$filter !='' ){
            
$filterQuery              json_decode($filter);
            
$whereCondition              $filterQuery->where;
            
$additionalFields        $filterQuery->additionalFields;
            
$newFields                = array();
            foreach(
$additionalFields as $additionalField){
                
$newFields[]        = $additionalField->fieldIndex;
            }
            
$newFields                implode(',',$newFields);
            if(
$newFields){
                
$newFields        ', '.$newFields;
            }
        }    
        
        
$sql "SELECT c.* $newFields , DATE_FORMAT(created, '%b %e, %Y') as created_date, DATE_FORMAT(modified, '%b %e, %Y') as modified FROM ".$tables." WHERE 1=1 AND ("$whereCondition .")";            
                
        return 
loadObjectlist($sql);
    }
    
    
$task                $_REQUEST['task'];
    if(
$task=='queryBuilder'){
        
$subtask                $_REQUEST['subtask'];
        
$mainquery                $_REQUEST['mainquery'];
        echo 
queryBuilder($subtask$mainquery);        
    }else{    
        
$filterQuery        $_REQUEST['filterQuery'];
        
$rows                contents($filterQuery);
        
$result             = new stdClass();
        
$result->data        $rows;
        
$result->total        count($rows);
        
$result->success    true;
        echo 
$json             =  json_encode($result);    
    }

?>