GroceryCrud set a n-n relation with where clause

Question

I have three tables (simplified) which are:

And I have to display all houses for each user.
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));
...
}

and what I get is this:

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?

You should use the tbl_users as your main table, and then filter it with $crud->where(). However, you will end with a single row table for this user. Is that what you intend? — jrierab, Feb 24 '16 at 16:40
Hi @jrierab thanks for your reply! What you mean is not exactly what I want. Each users owns different houses (Eg. User 1 -> HOUSE_1, HOUSE_4, HOUSE_7). What I need is a way to show only the houses linked to the user selected in the combo. I hope I was clear :) — Barzo, Feb 24 '16 at 19:38
This is not the intended usage for set_relation_n_n (it will show all the user houses in one field inside the user row). What you want can be better done listing from tbl_users_houses, filtering by client with $crud->where() and linking with the other tables with two simple relations. — jrierab, Feb 25 '16 at 07:44
Hi @jrierab, you're right. I changed my logic and now I was be able to accomplish what I wanted. — Barzo, Feb 26 '16 at 09:29
Hi @Barzo, I just added the above comment as an answer, so you can accept it, if you will. This way, the question appears as closed (solved). — jrierab, Feb 26 '16 at 12:29

score 0 · Accepted Answer · answered Feb 26 '16 at 12:28

This is not the intended usage for set_relation_n_n (it will show all the user houses in one field inside the user row).

What you want can be better done listing from tbl_users_houses, filtering by client with $crud->where() and linking with the other tables with two simple relations.

score 0 · Answer 2 · edited Apr 14 '21 at 01:41

If I understand correctly you are trying to fetch only the records for the logged in User... and u have multiple users per house, hence the n-n relation.

I also faced this problem and here's what I did.

        $myprojects = $this->admin_model->get_employee_projects($this->user_id);
        $myprojectids = array_column($myprojects, 'id');
        //get only one column from the multi-dimensional array
        $crud->where("`projects`.id IN", "(" . implode(",", $myprojectids) . ")", false); 
        // the false disables escaping

        $crud->set_relation_n_n('assigned_employees', 'project_employees', 'employees', 'project', 'employee', 'name');
        //Only so it also still shows the name of Users assigned

So basically projects here is like houses, and I am using the WHERE IN clause to filter the records based on the projects I get from my model method...

GroceryCrud set a n-n relation with where clause

2 Answers2