⚠ 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

Relation between three tables



Amber

Amber
  • profile picture
  • Member

Posted 11 April 2017 - 09:03 AM

Hello, I'm new on the forum but I'm using Grocery CRUD for a few days now and I'm really impressed with its possibilities, but going further I've came across this situation and can't figure it out how it should be done.

 

I have three tables:

 

people (id, name)

for example:

1, Person Name

2, Other Name

 

attributes (id, attribute)

for example:

1, photo

2, birthday

3, bio

and so on...

 

__people_attributes (id, person_id, attribute_id, value)

for example:

1, 1, 1, http://url_to_photo

2, 1, 2, 1987-06-05

3, 1, 3, bio of the person of id 1

and so on...

 

I created this method:

public function people() {
        $crud = new grocery_CRUD();
        $crud->set_subject('person', 'people');
        $crud->set_table('people');
        $crud->columns('id', 'name', 'attributes');
        $crud->unique_fields(array('name'));
        $crud->display_as('id', 'Person ID')
             ->display_as('name', 'Person Name')
             ->display_as('attributes', 'Attributes');

        $crud->set_relation_n_n(
            'attributes',
            '__people_attributes', // junction table
            'attributes',
            'person_id',
            'attribute_id',
            'value'
        );

        $output = $crud->render();
        $this->_example_output($output);
    }

But it returns the following error:

Unknown column 'attributes.value' in 'field list'

SELECT `people`.*, (SELECT GROUP_CONCAT(DISTINCT attributes.value) FROM attributes LEFT JOIN __people_attributes ON __people_attributes.attribute_id = attributes.id WHERE __people_attributes.person_id = `people`.id GROUP BY __people_attributes.person_id) AS attributes FROM `people`

 

I'm aware of that it should get the value field from the __people_attributes table but it's getting the value field from the attributes table instead.

 

How can rewrite my code?

I want to be able to add values to the particular attributes for each person I'm adding or editing.

 

Thank you.