My solution for dynamic dependent lists

A place to discuss development of the Xataface core.

My solution for dynamic dependent lists

Postby iCoder » Wed Jun 30, 2010 2:27 pm

Hi all!

My great respect to author of this brilliant software!

And sorry for my English. It's not my native language.

So, I'm totally new to Xatafae. 2 week before now I knew nothing about Xataface, even though I needed such a helpful tool very much.

Long ago I was programming under DOS/Clipper 5.2, and developed similar engine. Several years ago I moved to PHP/MySQL, and lake of such an instrument was very sensible for me. I was both searching for it and trying to develop my own version.

I know very well, how much work was done by the author, to make thing happen the way they are. So, I'm very grateful for that!

When I found Xataface, I installed it and started trying to make it working. Everything worked well with the very first try, not like other frameworks, many of which are very nasty.

But, Xataface didn't provide a very needed and useful feature - dynamic reload of dependent, related lists. For example I have 3 DB tables - countries, states, cities. In every place they're used, I need them to be loaded partially, dependent of what was selected before.

Cities table contains about 18000 rows. That's too much either to search for, or to dynamically load by whole.

So, after several hours of searching forum and Xataface site, I found some solutions, but I wasn't satisfied with them.

I like simple and effective solutions, like Xataface itself in use. So I decided to try to improve Xataface a little bit.

You know, Xataface already uses templates, so I thought it would be a good idea, to make Xataface able to "understand" simple macros included in valuelists.ini __sql__ queries.

simple example:

Code: Select all
__sql__ = "SELECT city_id, concat(city,'  / ',country_short,'') AS ccity
                FROM cities
                {#WHERE country_short='{$country_short$}'||WHERE 1=1#}
                ORDER BY country_short, city"


This request is called while operating table, which includes both country and city selection. As I mentioned before, loading whole cities DB-table is no way, because it is very-very large.

In this request you can see simple macro
Code: Select all
{#WHERE country_short='{$country_short$}'||WHERE 1=1#}
. I wrote small module, which parses __sql__ queries, and, if found, tries to replace
Code: Select all
{$country_short$}
by a value of corresponding field of related table, in other words - by value of TABLE.country_short, so tham whole __sql__ becames like this:


Code: Select all
__sql__ = "SELECT city_id, concat(city,'  / ',country_short,'') AS ccity
                FROM cities
                WHERE country_short='US'
                ORDER BY country_short, city"


If value wasn't found, whole macro-part
Code: Select all
{#WHERE country_short='{$country_short$}'||WHERE 1=1#}
either removed from query, or replaced by part going after '||' sign. It is useful, if you already have WHERE condition in your request, and need to compensate macro, if it is unsuccessful. If ‘||’ and what coming after it omitted, than unsuccessful macro replaced with empty space ‘’.

To make things work, you will have to patch ./Dataface/Table.php :
1) add
Code: Select all
require_once('Dataface/icoder/i.macro.lib.php');
somewhere around line 50;
2) add
Code: Select all
$value = iSmartReplaceMacros($value,$this->app->currentRecord->_values, $this->tablename, $vlname);
somewhere around line 1205;
3) Place 2 files i.dynamic.lib.php and i.macro.lib.php inside ./Dataface/icoder/, so that You’ll include them later on in where they’re needed.
4) Write some actions, to retrieve dependent data, like ./yourApp/actions/get_cities.php
5) include instruction like this
Code: Select all
widget:atts:onchange = "company_country_changed(this.value);"
in ./yourApp/tables/TABLE/fields.ini for the parent field.
5a) You can construct chains of dependent fields. In my example I've built chain of 3 dependent fields, vocabularies for which are downloaded from DB dynamically, dependent of values, selected in parent lists.
6) Include JS function, in your user JavaScripts file, which will execute download procedure, on select event in parent list:
Code: Select all
function optionsLoad(id,url) {
  jQuery('#'+id).attr('disabled',true).html("<option>Wait please, loading...</option>").load(url).attr('disabled',false);
}
function company_country_changed(country_short) {
  url = 'index.php?-action=get_cities&country_short='+country_short+'&optionlist';
  optionsLoad('city_id',url)
}


That's it. If you do everything correct, You'll get what I described - dependent lists, connected in chains, load dynamically, on select event in parent list.

For example: states depend on countries, and cities depend on states. When I select country - states reloaded, dependent on which country I've selected. The same thing with cities - it is reloaded, dependent on states.

If you need, I can construct chains of almost unlimited lengths... I have chains of 5-6 dependent elements in them. You can have several different chains on the same form. You even can have complex chains, having more than 1 child at ones. For example states can have cities and zips as a dependent child tables. So that both cities and zips reloaded simultaneously on state select.

Still there is a problem with all this stuff. As I think, while being in list mode, Xataface loads vocabulary once, and then substitutes corresponding foreign keys with values from vocabulary. So that you will see foreign keys instead of their values, because vocabulary was limited by a successful macro.

Instead, it should have joined 1 to 1 vocabulary table with strict condition. Or, either, we have to switch macro off at all, while being in list mode. In all other modes macros work pretty Ok.

Probable quick solution - to read -action type of request, and force macro to fail, if in list mode.

--

I realize, that my solution is not perfect. I've spent around 4-5 hours to make thigs work, ad by now I'm sutisfied with it.

Actualy it would be much better, if such a feature was included in Xataface natively.

Thank you very much for your attention!

P.S.: All needed files and examples included in attachment.
Attachments
i.macro.lib.zip
(38.86 KiB) Downloaded 644 times
iCoder
 
Posts: 4
Joined: Sun Jun 20, 2010 5:04 am

Re: My solution for dynamic dependent lists

Postby iCoder » Mon Jul 05, 2010 2:58 pm

An update.

Though filter does not work properly in LIST mode, we need to remove it at all. Simplest way to do that - provide an empty array of values, so that value will definetely be not found, and whole macro in __sql__ request will be replaced either with empty space, or macro replacement provided.

./appName/Dataface/Table.php => replace

Code: Select all
$value = iSmartReplaceMacros($value,$this->app->currentRecord->_values, $this->tablename, $vlname);


with

Code: Select all
                  $vl = $this->app->currentRecord->_values;
                  if (!isset($_REQUEST['-action']) || in_array($_REQUEST['-action'],array('list','find'))) {
                     $vl = array();
                  }
                  $value = iSmartReplaceMacros($value,$vl, $this->tablename, $vlname);


somewhere around row 1206.

By doing this you will avoid seeing ID instead of corresponding value.

Enjoy. :)
iCoder
 
Posts: 4
Joined: Sun Jun 20, 2010 5:04 am


Return to Xataface Developers

Who is online

Users browsing this forum: No registered users and 16 guests

Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved