⚠ 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

Selecting and adding data into link table



J-Hob
  • profile picture
  • Member

Posted 27 November 2013 - 21:30 PM

I have just started using GroceryCrud and am very impressed so far and have been able to get it to do what I want really quite easily.  However I have come a little unstuck on one point.

 

I have 3 tables:

  • item (item_id)
  • item_categories (item_id, category_id)
  • categories (category_id)

As you have probably already figured this structure is so that I can represent a many to many relationship between items and categories, with item_categories being the link table between the two.

 

When adding or editing an item I want to display checkboxes for each category (or multi-select, but checkboxes preferred)

 

On add/update I then want to write the selected categories (category_id, item_id) to the item_categories table.

 

From reading the documentation I think I probably want to be using callback_before_update and callback_before_insert to populate item_categories.  The main bit I am stuck on is how to get the categories to display as checkboxes on the form.

 

Can anyone advise how I would go about this?

 

 


J-Hob
  • profile picture
  • Member

Posted 27 November 2013 - 23:01 PM

Just discovered set_relation_n_n, does almost exactly what I want!

 

There is a couple of things that I would to do over the default functionality:

 

1. The categories are in 2 levels.  Is there any way of showing that in the list?

2. Alternatively, is there a way of ordering the items other than alphabetically?


J-Hob
  • profile picture
  • Member

Posted 27 November 2013 - 23:44 PM

Well, I have managed to do what I wanted by editing the grocery_crud_model.

 

If anyone's interested I edited the get_relation_n_n_unselected_array function.

 

Changed the line 

$this->db->order_by("{$field_info->selection_table}.{$field_info->title_field_selection_table}");

to

$this->db->order_by("{$field_info->selection_table}.{$selection_primary_key}");

to get the ordering working as I wanted.

 

And then I edited the foreach loop to indent child items in the list:

foreach($results as $row)
{
    if(!isset($selected_values[$row->$selection_primary_key])) {
        if (@$row->parent_id == 0) {
            $results_array[$row->$selection_primary_key] = $row->{$field_name_hash};
        } else {
            $results_array[$row->$selection_primary_key] = '   '.$row->{$field_name_hash};
        }
    }
}

so now the list looks like this: Image%202013-11-27%20at%2011.43.10%20PM.

 

If anyone knows of a neater way to do this please let me know but I'm happy with the solution for now.


J-Hob
  • profile picture
  • Member

Posted 28 November 2013 - 09:02 AM

OK, I think I may have found a bug with this:

 

I can only add one category at a time.  If I add more than one only the first is saved.  To add multiple categories I need to add one at a time and click save in between each.

 

I reverted the changes I made to the model and the issue still occurred so I'm sure it's not as a result of something I've done.

 

Has anyone else experienced this?


J-Hob
  • profile picture
  • Member

Posted 28 November 2013 - 09:17 AM

OK, I've sorted that one now too.

 

I didn't quite understand what the priority field was so I set it as the PK field of the relation table (item_categories). 

 

In looking into this bug I now understand what it is used for so I created a priority field in item_categories and set that as the priority field. 

 

As if by magic it all works!

 

I realise I'm having a conversation with myself here, sometimes I find it just helps me get the issue straight in my head once I've written it down.


Amit Shah
  • profile picture
  • Member

Posted 28 November 2013 - 09:37 AM

Hi J-Hob

 

Nice to see you finally figured out the solution and you too shared your experience with us. Happy to see you with the solution.

 

Happy GCing :)