Connector Filtering doesn't work with aliases

Suite and Optimus Suite 5
#1

Hi everyone!

We are encountering a problem using the dhtmlx grid 2.5 pro with the connector 1.0 by using following Select Statement for rendering a xml stream to the grid and then filtering by standart dhtmlx connector text filters:

$THE_ABOVE_SQL_STMT = '
SELECT 
    person.id as id, 
    concat(concat(person.lastname,", "),person.firstname) as [b]fullname[/b],
    concat_ws(",",
	  IF(person.learner_id IS NOT NULL,"GLOBAL_learner",null),
	  IF(person.expert_id IS NOT NULL,"GLOBAL_expert",null),
	  IF(person.referee_id IS NOT NULL,"GLOBAL_referee",null),
	  IF(person.company_supervisor_regular > 0,"GLOBAL_supervisorRegular",null),
	  IF(person.company_supervisor_ba > 0,"GLOBAL_supervisorBB",null),
	  IF(person.company_trainer > 0,"GLOBAL_trainer",null),
	  IF(person.admin > 0,"GLOBAL_admin",null),
	  IF(person.organisation_staff > 0,"GLOBAL_staff",null)
    ) as [b]type[/b],
    company.name,
FROM person
LEFT JOIN company ON company.id = person.company_id
WHERE person.active = 1
';

Structure for SQL is:

DROP TABLE IF EXISTS `company`;
CREATE TABLE  `company` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `person`;
CREATE TABLE  `person` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `active` tinyint(1) NOT NULL default '0',
  `lastname` varchar(50) NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `admin` tinyint(1) NOT NULL default '0',
  `organisation_staff` tinyint(1) NOT NULL default '0',
  `company_trainer` tinyint(1) NOT NULL default '0',
  `company_supervisor_regular` tinyint(1) NOT NULL default '0',
  `company_supervisor_ba` tinyint(1) NOT NULL default '0',
  `company_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_person_company` (`company_id`),
  CONSTRAINT `fk_person_company` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

PHP Code is:

$res = mysql_connect($dbConfig->host,$dbConfig->username,$dbConfig->password);
$connector = new GridConnector($res);
$connector->dynamic_loading(20);
$connector->render_sql($THE_ABOVE_SQL_STMT, "id", "fullname, type, company.name");

JS Code is:

grid = new dhtmlXGridObject('gridDiv');
grid.setImagePath("/images/dhtmlxgrid/");
grid.setHeader( "name, type, Company Name");
grid.attachHeader("#connector_text_filter,#connector_text_filter,#connector_text_filter");
grid.setInitWidths("100,100,100");
grid.setColAlign("left,left,left");
grid.setColTypes("ro,ro,ro");
grid.setColSorting("connector,connector,connector");
grid.forceLabelSelection(true);
grid.enableColumnMove(true);
grid.init();

grid.enableHeaderMenu();
grid.enableSmartRendering(true);
grid.enableColSpan(true);
grid.loadXML(url);

Now the problem is that if you filter something through the dhtmlx connector the server says e.g.:
MySQL operation failed - Unknown column ‘fullname’ in 'where clause

What am i doing wrong?

Greetz

vidj

#2

Connector filters implemented as SQL queries against the source DB, so they can’t run against the virtual fields ( as fullname in your case )

You can

a) create a view, based on your custom sql query and use this view in render_table command of connector. In such case connector will work with view field and filter correctly

b) if above is not possible by some reason, you can change syntax of command as

$connector->render_sql($THE_ABOVE_SQL_STMT, “id”, “firstname(fullname), type, company.name”);

as result connector will output fullname in the dataset, but will use firstname ( which is a real field name ) for filter commands.

#3

Hi!
thx for your quick answer!

Creating a View is not a problem but i don’t know if it is possible with our full sql statement.

We have an additional LEFT JOIN where a variable id is set for identifying those associated with that table.

Extend the given SELECT by a:

LEFT JOIN person_has_event ON person.id = person_has_event.person_id AND person_has_event.event_id = ${VARIABLE}

I’m not seeing how i can implement this in a MySQL View… Are SQL Procedures also working with render_table(…) ?? There i can add some variables as parameter…

As last resort i could

  1. create a temp view
  2. render_table(…)
  3. drop the temp view

what do you think?

greetz and thx4ur help

vidj

#4

Are SQL Procedures also working with render_table(…)
in most cases - yes, render_table results in sql command as

select … from {parameter of render_table}

To resolve problem you need to have something which produces fullname field, instead of creating it in the sql query. So you can still use render_sql, for joins, but use some table|procedure|view which has fullname field.