Custom object returned by join in Codeigniter Acti

Question

I have these two database tables:

locations
id
name

users
id
location_id
last_name
first_name

I also have User and Location class, they both extends Model and contain some custom methods. For example, there is a get_full_name() method in the User class.

I am using the following codes to load the data,

$this->db->select('users.id, locations.name as location_name, users.last_name, users.first_name');
$this->db->from('users');
$this->db->join('locations', 'users.location_id = location.id');
$query = $this->db->get();
$users= $query->custom_result_object('User'); //now $users is an array of User object

The custom_result_object is a built-in but undocumented function in Codeigniter. It accepts a string of class name and will use it to create objects of that class.

With the above codes, I can access the data like this,

foreach($users as $user)
{
 echo $user->id;
 echo $user->get_full_name();
 echo $user->location_name;
}

But as you can see, location_name is now a field of the User object, what I want is the User object has a field of Location object that allows me to use it like this,

foreach($users as $user)
{
 echo $user->id;
 echo $user->get_full_name();
 echo $user->location->name; // Location is an object
}

I don't want to use DataMapper ORM or any other ORMs, and would also like to avoid the N+1 query performance issue.

How can I do this with minimal amount of codes?

Note: I made up this example just for the demonstration purpose, in my real case, there are quite a lot of tables and classes (which has custom methods defined on them).

Many thanks to you all.

Answer 1

I had questioned the same for myself but found the simplest way was to iterate through each result (in your case, user) and select the corresponding child (location) and set to the field in the parent object (user->location).

The only alternative way I can think of would involve editing (or extending and creating your own db set) the db_result class to use getters/setters instead of direct fields - perhaps with call_user_func_array(). At that point, you could code your setId() function in the user model to set the $location field upon receiving the userId/foreign key.

Answer 2

get location id from table user, use it's number to grab from table locations by id a object. and put in it.

Answer 3

Have you considered the following?

class User {

    var location;

    public function location()
    {
        if ( empty($this->location) )
        {
            $ci =& get_instance(); 
            $this->location = $ci->db->get_where('locations', array('id' => $this->location_id))->row();
        }

        return $this->location;

    }

}

This way, you completely avoid the overhead of loading the location's table unless you need the data, in which case you cache it inside the object for future use.