⚠ 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

Set a n-n relation with where clause



joakin1602

joakin1602
  • profile picture
  • Member

Posted 17 February 2016 - 11:51 AM

Hi, i have a problem with one field with n-n relation, in which i want to use a where clause.

 

An example similar to my problem is this:

 

I have 3 tables:

 

[attachment=1074:Captura.PNG]

 

I want to define the relation in students table, but I want to be able to select only the teachers with the same school and I can't find the solution.

 

Sorry for my english


Barzo

Barzo
  • profile picture
  • Member

Posted 17 February 2016 - 22:34 PM

Hi,

I have to do a similar thing.

My tables (simplified) are:

 

8DTEiHS.png

 

In my controller I have a function like this:

public function create_houses_table($usr_id)
{
  $crud = new grocery_CRUD();

  $crud->set_language("italian");

  $crud->set_theme('datatables');
  $crud->set_subject('Casette');
  $crud->set_table('tbl_houses');

  $crud->set_relation_n_n('Casette', 
                          'tbl_users_houses', 
                          'tbl_users', 
                          'house_id', 
                          'user_id', 
                          'usr_name',
                          NULL,
                          array('user_id' => $usr_id));
...
}

What I get is this:

 

KIMIV6l.png

 

Every time I select a user from the combo I need to refresh my list filtering on usr_id...but I get always all the houses.

 

What I'm wrong?


zeren828

zeren828
  • profile picture
  • Member

Posted 19 February 2016 - 08:51 AM

Your problem may be that the form fields can not be aligned.

 

tbl_users -> usr_id  >>  tbl_users -> user_id

 

tbl_users_houses -> user_id


zeren828

zeren828
  • profile picture
  • Member

Posted 19 February 2016 - 09:12 AM

Hi, i have a problem with one field with n-n relation, in which i want to use a where clause.

 

An example similar to my problem is this:

 

I have 3 tables:

 

attachicon.gifCaptura.PNG

 

I want to define the relation in students table, but I want to be able to select only the teachers with the same school and I can't find the solution.

 

Sorry for my english

relation-n-n.png

primary_table primary_key  =  relation_table primary_table_field

selection_table primary_key  =  relation_table selection_table_field

 

so

 

student             student_teacher     teacher

student_id        student_id               teacher_id

                         teacher_id


Barzo

Barzo
  • profile picture
  • Member

Posted 20 February 2016 - 14:38 PM

Your problem may be that the form fields can not be aligned.

 

tbl_users -> usr_id  >>  tbl_users -> user_id

 

tbl_users_houses -> user_id

 

Hi zeren828,

 

thanks for your suggestion but changing the tbl_users_houses ->user_id to tbl_users_houses ->usr_id does not work.

 

Using the following give me always all the houses.

  $crud->set_relation_n_n('Casette', 
                          'tbl_users_houses', 
                          'tbl_users', 
                          'house_id', 
                          'usr_id', 
                          'usr_name',
                          NULL,
                          array('usr_id' => $usr_id));

Barzo

Barzo
  • profile picture
  • Member

Posted 24 February 2016 - 18:15 PM

No one know how to filter the list query in an n-to-n relation?

 

I try to use set_model() but maybe I misunderstood something...

I changed my code like:

    $crud->set_model('Houses_custom_model');

    $crud->set_theme('datatables');
    $crud->set_subject('Casette');
    $crud->set_table('tbl_houses');

    $crud->set_relation_n_n('Casette', 
                            'tbl_users_houses', 
                            'tbl_users', 
                            'house_id', 
                            'usr_id', 
                            'usr_name',
                            NULL,
                            array('usr_id' => $usr_id));

And in my custom model put:

class Houses_custom_model extends Grocery_crud_model  
{
    function get_relation_n_n_unselected_array($field_info, $selected_values)
    {
        $selection_primary_key = $this->get_primary_key($field_info->selection_table);
 
        // Here I do nothing...

        //$results = $this->db->get($field_info->selection_table)->result();
 
        $results_array = array();
        foreach($results as $row)
        {
            if(!isset($selected_values[$row->{$field_info->primary_key_alias_to_selection_table}]))
                $results_array[$row->{$field_info->primary_key_alias_to_selection_table}] = $row->{$field_info->title_field_selection_table}; 
        }
        return $results_array;
    }

}

and I get the list too also if I don't have made a query...

 

Now I have some questions:

  - which method I have to override?

  - which differences are between get_relation_n_n_unselected_array() and get_relation_n_n_selection_array()?

 

Thanks in advance to anyone who wanna give me an hint :)


carperolm

carperolm
  • profile picture
  • Member

Posted 02 March 2016 - 18:32 PM

Hi Barzo,

 

I have similar problem. Did you get any solution?

 

Thanks


carperolm

carperolm
  • profile picture
  • Member

Posted 03 March 2016 - 12:24 PM

I found the solution here:

/topic/2367-pre-filtering-data/


Hemanth Raj

Hemanth Raj
  • profile picture
  • Member

Posted 12 March 2016 - 11:25 AM

Hello Everyone the first time i integrated Grocery Crud to my project My question is i need to get the user id from users table and make it join for educations table can anybody help me live and i'm fresher so kindly any body help me