okay i’ve had some time so i’m making an attempt again to add join functionality to the new version of DM. It seems like i’m making some good progress, so i thought i’d post what i’ve got so far, to see if it works for anyone. It seems like it works better now than in my previous attempt, due to stensi’s great handling of statically storing model data.
Anyway, I’m trying to work in these methods to allow joining relations (one and many) plus joining counts of ‘has_many’ relations in a single query, at the same time easily being able to use the where, order_by, and other query types. Additionally, even though its not 5nf, a lot of people seem to want to use foreign keys. And in some cases it makes sense, like having a generic set of keys on many models (i’ve got ‘created_by’ and ‘updated_by’ as foreign ‘user’ ids, but i don’t want to have to create two separate linking tables for each model that uses them)
new methods are:
join_related($model = NULL, $fields = array(), $alias = '', $count_many = TRUE)
join_count($model) // Only works on has_many items
join_key( $field_name = NULL, $model = NULL, $fields = array() )
now instead of running get() on every relation, you just specifiy
$user = new User();
$user->join_related('userclass');
$user->get();
foreach($user->all as $u)
{
echo $u->userclass->name; // Works just like you ran $u->userclass->get();
echo $u->userclass->rank;
}
with join_related and join_key, you can speicify the fields (to cut down on db load), or leave it blank and all fields from the joined table will be included. The ID field is enforced in all cases. The join key function uses table aliasing to prevent name collisions, and turns the field into a model, so:
$post = new Post();
$post->get();
echo $post->created_by; // echos '1', or whatever id the user is;
$post = new Post();
$post->join_key('created_by','user', array('id','username','email'));
$post->get();
echo $post->created_by->username; // echos the user's username
echo $post->created_by->email; // you get the idear
also i added
where_count($relation, $value = NULL, $condition = '=');
which, in conjunction with join_count, or join_related on a has_many item (with count_many set to TRUE), you can specify results based on number of relations
$user = new User();
$user->join_count('post');
$user->where_count('post',10,'>');
$user->get(); // Gets users with more than 10 posts
there are a few additional functions as well in there. I changed the _to_object method to load_query_result(), which functions a bit differently, and i’ve had to rework several db methods, but now you can use where() and order_by() with relations that have been joined, just like regular fields
$user->join_related('userclass','name'); // Joins userclass model with only id & name
$user->where('userclass','admin','name'); // Selects where userclass.name = admin
$user->get();
only thing is if the third parameter (field to search) in the where clause isn’t speicified, it assumes you’re searching against the id. The same thing goes for joined_key fields
$user->join_key('created_by','user','username');
$user->where('created_by','commandercool','username');
$user->get(); // Gets users created by username 'commandercool';
// or
$user->join_key('created_by','user');
$user->where('created_by',3); // Assumes ID
$user->get(); // Gets users (fields id & username) created by user ID 3
So, other than that, i also modified the _to_array() function to spit out a complete array with relations, has_many counts, and joined keys etc..
All of this is done in single-query fashion as well. I know it’s adding some to memory consumption, etc, but i think it pays back in database performance. I was also trying to be careful not to mess with too much of the code, so everything else should work just fine. That being said, i also haven’t extensively tested it, and i’m sure more modifications might be needed in the remaining AR methods to account for the joins.
I’m not going to post every one of the added vars, functions and changes I made, plus this is still a work in progress, so i’ll just attach the DataMapper file i’ve got so far and let anyone who wants to play with it, do just that.
cheers,
CC
