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#}
- Code: Select all
{$country_short$}
- 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#}
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');
2) add
- Code: Select all
$value = iSmartReplaceMacros($value,$this->app->currentRecord->_values, $this->tablename, $vlname);
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);"
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.
