Jump to content


Error in Flexigrid search when using relations between 2 tables that contain same field name

error sql relation bug work around ambiguous felxigrid search

  • Please log in to reply
8 replies to this topic

#1 Eran

Eran

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 30 November 2012 - 10:32 PM

Hi,
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:
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?

#2 Eran

Eran

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 09 December 2012 - 09:11 PM

No body responded to this yet...

I think this fix should be added to future versions and I would appreciate if one of the code contributors would check it out.
Thanks.

#3 Pablo Boff Pigozzo

Pablo Boff Pigozzo

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 23 January 2013 - 01:28 AM

Works fine for me! thanks Eran

#4 davidoster

davidoster

    Grocery CRUD Ninja

  • Advanced Member
  • PipPipPip
  • 1,068 posts
  • LocationAthens, Greece

Posted 12 February 2013 - 01:13 AM

I think you need to post it as an issue on github, https://github.com/s...crud/issues/new

[https://github.com/s...ues?state=open]

No body responded to this yet...

I think this fix should be added to future versions and I would appreciate if one of the code contributors would check it out.
Thanks.


____________________________________________________________

 

rtfm_small.jpg---!!!Please read these guidelines before asking to the forums!!!---

____________________________________________________________

 

David Oster aka George Pasparakis,
http://odphotography.com
http://eletter.gr


#5 shum

shum

    Member

  • Members
  • PipPip
  • 10 posts
  • LocationRussia, Moscow

Posted 08 May 2013 - 05:26 PM

It works!

 

diff:

 

diff --git a/application/libraries/grocery_crud.php b/application/libraries/grocery_crud.php
index 504690d..6767282 100644
--- a/application/libraries/grocery_crud.php
+++ b/application/libraries/grocery_crud.php
@@ -1031,6 +1031,11 @@ class grocery_CRUD_Model_Driver extends grocery_CRUD_Field_Types
 			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;
 		}
 	}
diff --git a/application/models/grocery_crud_model.php b/application/models/grocery_crud_model.php
index 7df2f7d..c5bb737 100644
--- a/application/models/grocery_crud_model.php
+++ b/application/models/grocery_crud_model.php
@@ -151,16 +151,27 @@ class grocery_CRUD_Model  extends CI_Model  {
     function or_having($key, $value = NULL, $escape = TRUE)
     {
     	$this->db->or_having( $key, $value, $escape);
-    }    
+    }
+    
+    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;
+        }
+    }
+    
+    
     
     function like($field, $match = '', $side = 'both')
     {
-    	$this->db->like($field, $match, $side);
+    	$this->db->like($this->long_field_name($field), $match, $side);
     }
     
     function or_like($field, $match = '', $side = 'both')
     {
-    	$this->db->or_like($field, $match, $side);
+    	$this->db->or_like($this->long_field_name($field), $match, $side);
     }    
     
     function limit($value, $offset = '')

git apply --whitespace=fix < diff_fix_relative_search_gc.diff

 

 


I do not speak English, sorry :(


#6 Eran

Eran

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 08 May 2013 - 06:37 PM

I'm glad it helped you guys :)

I don't currently have a Github account so I couldn't post it as an issue there. I'll try to get to it soon, so it would be fixed in future versions.



#7 davidoster

davidoster

    Grocery CRUD Ninja

  • Advanced Member
  • PipPipPip
  • 1,068 posts
  • LocationAthens, Greece

Posted 12 May 2013 - 06:13 AM

Also there is this fix, without changing the core of the library.

http://www.grocerycr...urned/#entry982


____________________________________________________________

 

rtfm_small.jpg---!!!Please read these guidelines before asking to the forums!!!---

____________________________________________________________

 

David Oster aka George Pasparakis,
http://odphotography.com
http://eletter.gr


#8 edgarvaldes

edgarvaldes

    Newbie

  • Members
  • Pip
  • 7 posts

Posted 30 May 2013 - 02:27 PM

When using JOINS with two different tables, search fails.

It would be great if someone colud add a fix like this. Great job, Eran!



#9 tamer

tamer

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 10 October 2017 - 12:24 PM

Hi Eran,

 

just created an account here to say a big thank you to your awesome work! You saved me lots of time ! :rolleyes: :)







Also tagged with one or more of these keywords: error, sql, relation, bug, work around, ambiguous, felxigrid, search

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users