PDA

View Full Version : Making an access 2000 style query builder



leprechaun
17 Dec 2010, 2:01 AM
Hi,

We are thinking of getting a license for ExtJs. We had a look through the examples and there's some things that fit the bill, However, what we're really looking to put together something that can recreate the functionality of the query builder in access (we have access 2000 - screenshot attatched).

Something like where we can add tables to a 'canvas' and then drag fields/keys from one table to another and have lines drawn in between. I like HTML5 canvas but we can't use it due to browser compatiblity.

http://raphaeljs.com/ looks good, but was hoping we could just use the ExtJs library.

Hope you can help.

leprechaun
11 Jan 2011, 8:44 AM
Ok, so Iíve been looking at the code examples, and I have some idea of how the UI side of the drag and drop could work, based on a example I found where you can drag and drop records grid to grid (I'd reference it if I could remember where i found it) all i did was implement drag and drop on the grid panels themselves so they can be dragged around a panel (see fig1 24221 ), although I still have to work out a means of drawing a line from the dragged element (plus I just realised as writing this the dragged element must remain in the original box but this should be straight forward enough). Iím hoping to get something along these line to demonstrate the relationship between tables in the query builder (fig2 24223) where by dragging the field across will allow a javascript object on the client side to build a query, that can then be serialised, sent to the server side, stored or executed. The query can then be retrieved a later time and edited, Iíve only got to worry about how the Javascript query will be built / serialised and handled on the UI (weíve got a javascript model that will serialised and deserialised in JSON, just need to make it editable for non technical users)



Iíve also been looking at this example;

http://dev.sencha.com/deploy/dev/examples/grid/binding.html

Iíve been looking at the code and thereís elements Iím trying to figure out as my code will read from JSON instead of XML but the JSON will also be ďobject-orientatedĒ rather than array based. I will be feeding it objects, containing arrays of objects which in turn may contain arrays.

Example of JSON to process into separate grids; (cut down version with only one level of recursion for simplicity of development)



[
{"_type":"int","_name":"int"},
{"_type":"text","_name":"text"},
{"_type":"boolean","_references":[
{"_relation":"person","_column":"position","_nullable":false},
{"_relation":"country","_column":"isoCode","_nullable":false}
],"_name":"boolean"}
]

(fig3 24222- illustration of JSON model for query)

I wrote some code (see below) based on a basic grid example that can yet read the above object into two grids but can take simpler JSON and make a grid on the fly, I wondering though whether Extjs already has some similar functionality Iíve not discovered yet;




<script type="text/javascript">

// Path to the blank image must point to a valid location on your server
Ext.BLANK_IMAGE_URL = 'ext/resources/images/default/s.gif';
Ext.onReady(function() {
// sample static data for the store
var myData = [
{"_relation":"person","_column":"position","_nullable":false},
{"_relation":"person","_column":"position","_nullable":false},
{"_relation":"person","_column":"position","_nullable":false}
];

var tableArray = [];
var arrayStoreFields = [];
var columnHeaders = []; //create array of objects for columnModler

//build table array as data for table
Ext.each(myData, function(person, index) {
var row = [];
var tempField = null;
var tempHeader = null;
columnHeaders = [];

Ext.iterate(person, function(key, value) {
//columnHeaders = [];
//console.log(key + "'s value is the " + value);
row.push(value);
tempField = {name: key};
tempHeader = {header: key, sortable: true, dataIndex:key};
arrayStoreFields.push(tempField);
columnHeaders.push(tempHeader);
});
tableArray.push(row);


});


myJson = JSON.stringify(tableArray);
myData = JSON.parse(myJson);

// create the data store
var ds = new Ext.data.ArrayStore({
fields: arrayStoreFields
});
// manually load local data
ds.loadData(myData);

// create the colum Manager
var colModel = new Ext.grid.ColumnModel(columnHeaders);

// create the Grid
var grid = new Ext.grid.GridPanel({
store: ds,
colModel: colModel,
height: 300,
width: 600,
title: 'References'
});

// render the grid to the specified div in the page
grid.render('gridMain');

});

</script>
So to summarise my main concerns are;

(i)How would I take a JSON object (that is a object rather than an array containing arrays of objects), and display each of these objects & arrays in data grids?

(ii)How can I draw a line from one grid record to a different gridís record?

If anyone has some ideas of examples that would a great help, thanks! :)

leprechaun
13 Jan 2011, 9:20 AM
Solved (ii) using Raphael to draw lines.

martinorth
29 Apr 2012, 1:54 PM
@leprechaun Did you managed to code a drag&drop query builder like in access?