Jump to content

Removing the set_relation() limitation


  • Please log in to reply
2 replies to this topic

#1 rafael84



  • Members
  • PipPip
  • 10 posts
  • LocationRio de Janeiro, Brazil

Posted 28 December 2011 - 04:03 AM


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}');

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:

FILE: application/models/grocery_model.php
function get_relation_array($field_name , $related_table , $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";
	  $select .= "$related_table.$related_field_title as $field_name_hash";

function get_list() {
                    // 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";
                    $select .= ", $unique_join_name.$related_field_title as $unique_field_name";


#2 colegatron



  • Members
  • Pip
  • 2 posts

Posted 29 December 2011 - 11:08 AM

Thank you for the correction!!

#3 web-johnny


    grocery CRUD Author

  • Administrators
  • 1,151 posts
  • LocationLondon

Posted 30 December 2011 - 12:02 AM

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

Also tagged with one or more of these keywords: suggestion

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users