I recently moved from using datatables to using flexigrid. After a couple of days I noticed that when I try to use the search feature, the ajax call returns a "500 Internal Server" error.
I started playing with my code and I noticed that when I stop using "set_relation" the search was working again. So I started thinking that maybe the automatically generated SQL query is some how incorrect.
In order to get the actual query sent to the MySQL driver, I added a file_put_content directive at the end of the "_compile_select" function in codeigniter DB_active_rec.php.
The generated SQL query was indeed incorrect. The issue was that the relation is translated into a JOIN. If the joined tables contain fields with the same name, the search query will fail because they both contain the same field and therefor it's ambiguous. This would not happen if the field names would include a table name (e.g. table_name.field_name instead of just field_name).
To make this clearer I'll give you an example:
I have a table called t_deploy_groups. It's SQL schema and data:
--
-- Table structure for table `t_deploy_groups`
--
DROP TABLE IF EXISTS `t_deploy_groups`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_deploy_groups` (
`dgID` int(11) NOT NULL AUTO_INCREMENT,
`userID` int(11) NOT NULL,
`dgName` varchar(256) NOT NULL,
`dgDescription` varchar(1000) NOT NULL,
`sameAsDG` int(11) DEFAULT NULL,
PRIMARY KEY (`dgID`)
) ENGINE=InnoDB AUTO_INCREMENT=1114 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t_deploy_groups`
--
LOCK TABLES `t_deploy_groups` WRITE;
/*!40000 ALTER TABLE `t_deploy_groups` DISABLE KEYS */;
INSERT INTO `t_deploy_groups` VALUES (1,1,'Telus','Telus Labs',NULL),(3,1,'T-Mobile','T-Mobile POS',NULL),(67,1,'AT&T','AT&T POS',NULL),(300,1,'QA','Testing & QA',1),(1111,1,'Demo','Playground',NULL);
/*!40000 ALTER TABLE `t_deploy_groups` ENABLE KEYS */;
UNLOCK TABLES;
The sameAsDG field should contain an existing dgID. So when I display this table using grocery crud, I added a set relation on the sameAsDG that will show you the existing names of the existing deploy groups (in this example the relation is from the table onto itself).
My controller code is:
public function deploy_groups() {
$uid = $this->session->userdata('uid');
$crud = new grocery_CRUD();
$crud->set_table('t_deploy_groups')
->set_subject('Deploy Group')
->required_fields('userID', 'dgName', 'dgDescription')
->display_as('dgName', 'Deploy Group Name')
->display_as('dgDescription', 'Deploy Group Description')
->display_as('sameAsDG', 'Use Same Apps As')
->display_as('dgID', 'ID')
->columns('dgID', 'dgName', 'dgDescription', 'sameAsDG')
->fields('dgID', 'dgName', 'dgDescription', 'sameAsDG');
$crud->set_relation('sameAsDG', 't_deploy_groups', 'dgName', "t_deploy_groups.userID = $uid", 'dgName ASC')
->callback_insert(array($this->simon_model, 'deploy_group_insert_callback'))
->callback_after_delete(array($this->simon_model, 'deploy_group_after_delete_callback'))
->where("t_deploy_groups.userID = $uid");
$crud->unset_add()
->unset_edit()
->unset_delete()
->unset_print();
$output = $crud->render();
$output->active = 'deploy_groups';
$this->load->view('deploy_groups.php', $output);
}
In my application the table looks like this:
[attachment=384:dg_table_screenshot.png]
If you try to search for the word "POS" you would expect to see 2 rows (becuase they are the only ones that contain this word), but you will get all the rows because the ajax search call would fail.
The automatic SQL query used is:
SELECT *
FROM (`t_deploy_groups`)
LEFT JOIN `t_deploy_groups` as ja7d8fa27 ON `ja7d8fa27`.`dgID` = `t_deploy_groups`.`sameAsDG`
WHERE `t_deploy_groups`.`userID` = 1
AND `dgID` LIKE '%POS%'
OR `t_deploy_groups`.`dgName` LIKE '%POS%'
OR `dgDescription` LIKE '%POS%'
OR `ja7d8fa27`.`dgName` LIKE '%POS%'
HAVING `t_deploy_groups`.`userID` = 1
If you try to run this query directly through MySQL you will get: "Error Code: 1052. Column 'dgID' in where clause is ambiguous"
If the field names in the where section would include the table name (t_deploy_groups), this query would be correct.
In order to fix this issue I added the following function to the grocery_CRUD_Model class in the grocery_crud_model.php file:
/**
* We use this function to make sure that the format for each field is <table_name>.<field>
* @param string $field
* @return string
*/
function long_field_name($field)
{
if ((!is_null($this->table_name)) && (trim($this->table_name) != '') && (strpos($field, '.') === false)) {
return "$this->table_name.$field";
} else {
return $field;
}
}
This code adds the table name to the field name if it doesn't already exists.
I added a call to this function in the following functions: like() and or_like() (both are in the grocery_crud_model.php file)
function like($field, $match = '', $side = 'both')
{
$this->db->like($this->long_field_name($field), $match, $side);
}
function or_like($field, $match = '', $side = 'both')
{
$this->db->or_like($this->long_field_name($field), $match, $side);
}
I also changed the _get_field_names_to_search() function from the grocery_crud.php file:
protected function _get_field_names_to_search(array $relation_values)
{
if(!strstr($relation_values[2],'{'))
return $this->_unique_join_name($relation_values[0]).'.'.$relation_values[2];
else
{
$relation_values[2] = ' '.$relation_values[2].' ';
$temp1 = explode('{',$relation_values[2]);
unset($temp1[0]);
$field_names_array = array();
foreach($temp1 as $field)
list($field_names_array[]) = explode('}',$field);
//New Code - this was added to make sure that relations using more than one field in the
//$related_title_field paramter, will be using the unique table name infront of the field name
foreach ($field_names_array as $key => $field) {
$field_names_array[$key] = $this->_unique_join_name($relation_values[0]).'.'.$field;
}
return $field_names_array;
}
}
The change is the additional foreach following the "New Code" comment. This was added to handle a situation where you have a complex $related_title_field (e.g. "{dgID} - {dgName}"). Without this addition these fields might not get the correct table name added to them (happens when your relation is between 2 different tables).
This fixed the issue for me. And now the Flexigrid search is working in all my views.
You can probably work around this in several other ways (like using a view with unique field names), but this way works for me.
Hope this might help other people that are stuck on the same issue.
Johnny, what do you think about adding something like this to the code?
