[Tip]:Ext.sql.Proxy extention to use a query instead of a table

[murrah]

Hi,

Here is a way I worked out to be able to load a (grouping) store (for a grid, in this case) from a sqlite query rather than a table. This also solves another issue I had with the Grouping Store. Since I am still learning Ext, there may be a simpler way to do this, but it works.

First, the sqlite bit. I had a couple of instances where I wanted to include a derived field in the data for the grid and/or return the data from a table join, rather than just one table.

The constructor in the store for the standard Ext.sql.Proxy is:

this.proxy = new Ext.sql.Proxy(tx.data.conn, 'timesheet', 'tsId', this);
where 'timesheet' is a table and 'tsId' is the primary key.

I wanted to load the store with records based on this sql (for example):

'SELECT timesheet.*, task.title, date(taskStart) as groupDate FROM timesheet left join task on (timesheet.taskId = task.taskId)'

ie return data from joined tables and also return a derived field (groupDate - just the Y/m/d part of the datetime field timesheet.taskStart - more on that Grouping Store issue below).

There didnt seem to be a way to do this so I extended the Ext.sql.Proxy and overrode the load method like this:

ProxyQuery = function(conn, table, keyName, store, readonly){
ProxyQuery.superclass.constructor.call(this, conn, table, keyName, store, readonly);
};

Ext.extend(ProxyQuery, Ext.sql.Proxy, {
load : function(params, reader, callback, scope, arg){
if(!this.conn.isOpen()){ // assume that the connection is in the process of opening
this.conn.on('open', function(){
this.load(params, reader, callback, scope, arg);
}, this, {single:true});
return;
};
if(this.fireEvent("beforeload", this, params, reader, callback, scope, arg) !== false){
var clause = params.where || '';
var args = params.args || [];
var group = params.groupBy;
var sort = params.sort;
var dir = params.dir;

if(group || sort){
clause += ' ORDER BY ';
if(group && group != sort){
clause += group + ' ASC, ';
}
clause += sort + ' ' + (dir || 'ASC');
}

// The following line was replaced by the block below
// var rs = this.table.selectBy(clause, args);

// Start replacement
var sqlStr = params.sqlStr || '';

if (sqlStr != ''){
var rs = this.conn.query(sqlStr);
} else {
var rs = this.table.selectBy(clause, args);
}
// End replacement

this.onLoad({callback:callback, scope:scope, arg:arg, reader: reader}, rs);
}else{
callback.call(scope||this, null, arg, false);
}
}
});

ie What I did was replace the line in the original code (ext-air v0.3):
var rs = this.table.selectBy(clause, args);
with the following:


var sqlStr = params.sqlStr || '';

if (sqlStr != ''){
var rs = this.conn.query(sqlStr);
} else {
var rs = this.table.selectBy(clause, args);
}

then I passed the required sql string via a (new) config option like this:


this.load({
params: {
sqlStr:'SELECT timesheet.*, task.title, date(taskStart) as groupDate FROM timesheet left join task on (timesheet.taskId = task.taskId)'
},
scope: this
});

and, at another place where I only wanted certain records:


this.load({params: {
sqlStr:"SELECT timesheet.*, task.title, date(taskStart) as groupDate FROM timesheet left join task on (timesheet.taskId = task.taskId) where timesheet.taskId = '"+taskId+"'"
}});

To use my extended load() method I replaced the standard proxy in the store constructor ie:

this.proxy = new Ext.sql.Proxy(tx.data.conn, 'timesheet', 'tsId', this);
with

this.proxy = new ProxyQuery(tx.data.conn, 'timesheet', 'tsId', this);

In the Record for the store (tx.data.Timesheet) I added the fields:

{name: 'title', type:'string'},
{name: 'groupDate', type:'date', dateFormat: 'Y-m-d', defaultValue: ''},
where 'title' is the field from the joined table and groupDate is the derived field.

and in the grid columnModel I added:


{
header: "TaskName",

dataIndex: 'title',
width: 100
},
{
header: "Date",
dataIndex: 'groupDate',
width: 100,
sortable: false,
hidden:true, // hidden because I only wanted to group on it, not display it in the grid
renderer: Ext.util.Format.dateRenderer('D d/m/Y')
}


Now, the GroupingStore issue. My timesheet table contains a datetime field called taskStart. I wanted to group all timesheet records for a day, but since the taskStart field contained times if I grouped on the taskStart field it obviously grouped by datetime, not just the date. At first I tried to add a field to the grid's columnModel that used taskStart but with a render for just 'Y/m/d' however I got undefined errors which implied that the field actually had to be in the store (which makes sense when I thought about it). So, using the method above, I was able to add the derived groupDate field to the store and then group on it!

Here is the relevant part of my store object:

tx.data.TimesheetStore = Ext.extend(Ext.data.GroupingStore, {

constructor: function(){
tx.data.TimesheetStore.superclass.constructor.call(this, {
sortInfo:{field: 'groupDate', direction: "ASC"},
groupField:'groupDate',
reader: new Ext.data.JsonReader({
id: 'tsId',
fields: tx.data.Timesheet
})
});

this.conn = tx.data.conn;
this.proxy = new ProxyQuery(tx.data.conn, 'timesheet', 'tsId', this);
},


This all works very well and as I said, there may be a better way to do it.

Cheers,
Murray

[aconran]

Great idea. We'll see what we can do. Thanks for the detailed post and explainations.

[murrah]

Thanks for the detailed post and explainations.

Thanks. After all the help I have had it is gratifying to be able to return the favour!!
Murray