Can I please have a design suggestion for the following problem:
I am using Codeigniter/Grocery_CRUD.
My system is multi tenanted - different autonomous sites - within the same client. I have quite a few instances of tables that have unique logical keys. One such table structure is:
equip_items
id (pk)
equip_type_id (fk to equip_types)
site_id (fk to sites)
name
Where (equip_type_id, site_id, name) together are a unique key in my db.
The issues is that when using a grocery_CRUD form to add or edit a record that breaks this database rule - the add or edit fails (due to the constraints in the db) but I get no feedback.
I need a variation on the is_unique form_validation rule by which I can specify the field*s* that must be unique.
The issues:
How to specify the rule? set_rules() is for a given field and I have multiple fields that the rule will apply to. Does that mean I should abandon the Form_validation pattern? Or do I follow the 'matches' rule pattern and somehow point to the other fields?
Perhaps a callback function would be better but this would mean writing a custom function in each model where I have this problem at last count this is 9 tables. It seems far better to do this in one place (extending form_validation).
Am I missing something already in codeigniter or grocery_CRUD that has already solved this problem?
Any suggestion/advice you might have would be appreciated.
EDIT: Actually it appears the solution Johnny provided does not quite hit the mark - it enforces each field in unique_fields() being independently unique - the same as setting is_unique() on each one. My problem is that in my scenario those fields are a composite unique key (but not the primary key). I don't know if it is significant but further to the original problem statement: 1) site_id is a 'hidden' field_type - I don't want my users concerned they are on a different site so I'm dealing with site_id behind the scenes. 2) Same deal with an equip_status_id attribute (not part of the unique key). And 3) I have set_relations() on all these foreign key attributes and grocery_CRUD kindly deals with nice drop downs for me.
EDIT 2 I have solved this using a callback.
UPDATE: This code is now part of grocery CRUD version >= 1.4 and you don't need to use an extension anymore. For more see the documentation for unique_fields
I will try to explain it as easy as I can:
1. First of all for those who have grocery CRUD lower or equal to 1.3.3 has to use this small change: https://github.com/scoumbourdis/grocery-crud/commit/96ddc991a6ae500ba62303a321be42d75fb82cb2
2. Second create a file named grocery_crud_extended.php at application/libraries
3. Copy the below code at your file application/libraries/grocery_crud_extended.php
<?php
class grocery_CRUD_extended extends grocery_CRUD
{
protected $_unique_fields = array();
public function unique_fields()
{
$args = func_get_args();
if(isset($args[0]) && is_array($args[0]))
{
$args = $args[0];
}
$this->_unique_fields = $args;
return $this;
}
protected function db_insert_validation()
{
$validation_result = (object)array('success'=>false);
$field_types = $this->get_field_types();
$unique_fields = $this->_unique_fields;
$add_fields = $this->get_add_fields();
if(!empty($unique_fields))
{
$form_validation = $this->form_validation();
foreach($add_fields as $add_field)
{
$field_name = $add_field->field_name;
if(in_array( $field_name, $unique_fields) )
{
$form_validation->set_rules( $field_name,
$field_types[$field_name]->display_as,
'is_unique['.$this->basic_db_table.'.'.$field_name.']');
}
}
if(!$form_validation->run())
{
$validation_result->error_message = $form_validation->error_string();
$validation_result->error_fields = $form_validation->_error_array;
return $validation_result;
}
}
return parent::db_insert_validation();
}
protected function db_update_validation()
{
$validation_result = (object)array('success'=>false);
$field_types = $this->get_field_types();
$unique_fields = $this->_unique_fields;
$add_fields = $this->get_add_fields();
if(!empty($unique_fields))
{
$form_validation = $this->form_validation();
$form_validation_check = false;
foreach($add_fields as $add_field)
{
$field_name = $add_field->field_name;
if(in_array( $field_name, $unique_fields) )
{
$state_info = $this->getStateInfo();
$primary_key = $this->get_primary_key();
$field_name_value = $_POST[$field_name];
$ci = &get_instance();
$previous_field_name_value =
$ci->db->where($primary_key,$state_info->primary_key)
->get($this->basic_db_table)->row()->$field_name;
if(!empty($previous_field_name_value) && $previous_field_name_value != $field_name_value) {
$form_validation->set_rules( $field_name,
$field_types[$field_name]->display_as,
'is_unique['.$this->basic_db_table.'.'.$field_name.']');
$form_validation_check = true;
}
}
}
if($form_validation_check && !$form_validation->run())
{
$validation_result->error_message = $form_validation->error_string();
$validation_result->error_fields = $form_validation->_error_array;
return $validation_result;
}
}
return parent::db_update_validation();
}
}
4. Now you will simply have to load the grocery_CRUD_extended like that:
$this->load->library('grocery_CRUD');
$this->load->library('grocery_CRUD_extended');
and then use the:
$crud = new grocery_CRUD_extended();
instead of:
$crud = new grocery_CRUD();
5. Now you can simply have the unique_fields that it works like this:
$crud->unique_fields('field_name1','field_name2','field_name3');
In your case:
$crud->unique_fields('equip_type_id','site_id');
Pretty easy right?
This is checking if the field is unique or not without actually change the core of grocery CRUD. You can simply use the grocery_CRUD_extended instead of grocery_CRUD and update grocery CRUD library as normal. As I am the author of the library I will try to include this to grocery CRUD version 1.4, so you will not have to use the grocery_CRUD_extended in the future.