Custom "with()" method that has two joins, how to?

[paulcanning]

I am struggling to write a custom "with()" method that involves two joins.

I'll try to breakdown my tables:

Users - generic table, users can be joined to other tables via lookup tables.
Resellers - entity that describes a reseller, has an owner_id which is a foreign key to users.id
Agents - lookup table to connect a (different) user to a reseller, as an agent (sales person). Can be multiple per reseller.

So, typically, looking up a reseller, I can get the owner (users) details by using the $belongs_to relationship and using "with('users')" in my call, before get_all() eg $this->resellers->with('users')->get_all()

This works great!

Now, the tricky part.

I want to get the above, but with the agent(s) details. eg $this->reseller->with('users')->with_agents()->get_all()

I tried the following:

public function with_agents() {
    $this->db->select('users.first_name AS agent_name');
    $this->db->join('agents_resellers', 'users_resellers.reseller_id = resellers.id', 'LEFT');
    $this->db->join('users u', 'u.id = agents_resellers.user_id', 'LEFT');

    return $this
}

But I get an error:

Notice 0 - Undefined property: stdClass::$owner_id ... application\core\MY_Model.php on line 473

Sooo, how can I write a custom "with()" method, that has multiple joins, and where it is fetching one or more results? (not just one result like the user join)

[avenirer]

Are you sure it is line 473? Because looking at MY_Model, I don't see anything written on that line...

[michail1982]

@paulcanning check MY_Model version . i think it`s happends on empty results (closed issue)
check this line https://github.com/jamierumbelow/codeigniter-base-model/blob/master/core/MY_Model.php#L441

[paulcanning]

@avenirer Definitely that line number!

Does anyone know if its possible to write a custom method, e.g. get_some_users() and have it use the with() methods that methods like get() and get_all() can use?

So I could do $this->model->with('owner')->my_custom_query_method()

[michail1982]

@paulcanning Sure, its return model object and you can call get() or get_all() or any other MY_Model method

[paulcanning]

@michail1982 care to provide an example? :)

[michail1982]

$this->model->with('owner')->my_custom_query_method()->get_many_by(('score' => 1));

[paulcanning]

Sorry, I meant an example of how I write the custom method to return the object so it can be used with get() and get_all()

[michail1982]

@paulcanning ,

return $this;