Friday, January 11, 2013

Grocery CRUD Column Naming Conflict Solution

While working on a quick project for a client, I ran into an issue where related tables were being joined together had conflicting column names. Because both tables shared some column names, the field names were not unique to each table causing the query to fail due to ambiguity. Some might think it is related to flexigrid which is used in grocery, but it has nothing to do with flexigrid.

After digging around in undocumented grocery CRUD, I decided to look online to see if anyone had the same problem but I didn't seem to find any helpful results until I struck pure gold. Thank you, Eran, you saved me!

Eran's Original Forum Post

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)
          return $this->_unique_join_name($relation_values[0]).'.'.$relation_values[2];
   $relation_values[2] = ' '.$relation_values[2].' ';
   $temp1 = explode('{',$relation_values[2]);
   $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;

No comments: