⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Removing the set_relation() limitation



rafael84

rafael84
  • profile picture
  • Member

Posted 28 December 2011 - 04:03 AM

Guys,

I guess many of you have already noticed a minor bug when you put in your $related_title_field parameter a field that accept NULL values: the resulting value may also be NULL, regardless of some other field is not null.

For example, let's assume you have built a simple customer CRUD.
The customer table has the ID, NAME, PHONE1 and PHONE2 fields.
ID is the primary key, NAME is not null, but PHONE1 and PHONE2 are nullables.
Suppose the following records in the customer table:

1, 'BOB', null, null
2, 'MARY', '555-4444', null
3, 'PETER', null, '555-3333'


Now let's also assume there's an order CRUD. For simplicity sake, the order table has only the following fields: ID, CUSTOMER_ID. ID is the primary key, CUSTOMER_ID is a foreign key, related to the customer table.
The records in the order table are:

1001, 3
1002, 1
1003, 2


If you want to display the customer name and its phone numbers in the order list, you would do something like this:


$crud = new grocery_CRUD();
...
$crud->set_relation('customer_id','customers','{name} {phone1} {phone2}');
$crud->set_table('orders');
...
$crud->render();


In that case, the customer_id column is always empty.

Grocery CRUD uses the CONCAT function to build up the final output value, and that's the cause of the problem.

We can easily fix that by using the COALESCE function. Check it out:

[b]FILE: application/models/grocery_model.php[/b]

function get_relation_array($field_name , $related_table , $related_field_title) {
...
if(strstr($related_field_title,'{'))
// OLD // $select .= "CONCAT('".str_replace(array('{','}'),array("',",",'"),mysql_escape_string($related_field_title))."') as $field_name_hash";
/* NEW */ $select .= "CONCAT('".str_replace(array('{','}'),array("',COALESCE(",", ''),'"),mysql_escape_string($related_field_title))."') as $field_name_hash";
else
$select .= "$related_table.$related_field_title as $field_name_hash";
...
}

function get_list() {
...
if(strstr($related_field_title,'{'))
// OLD // $select .= ", CONCAT('".str_replace(array('{','}'),array("',",",'"),mysql_escape_string($related_field_title))."') as $unique_field_name";
/* NEW */ $select .= ", CONCAT('".str_replace(array('{','}'),array("',COALESCE(",", ''),'"),mysql_escape_string($related_field_title))."') as $unique_field_name";
else
$select .= ", $unique_join_name.$related_field_title as $unique_field_name";
...
}


-Rafael

colegatron

colegatron
  • profile picture
  • Member

Posted 29 December 2011 - 11:08 AM

Thank you for the correction!!

web-johnny

web-johnny
  • profile picture
  • Administrator
  • 1,166 posts

Posted 30 December 2011 - 00:02 AM

You are right. Added for the next version. ;) https://github.com/s...y-crud/issues/5
Thanks