Thread

@OverZealous - Cool stuff!.. it seems like we’re both working similarly on enhancing datamapper to build more effective queries and allow for more relevant data to be available upon getting a datamap.

in my last post i tried creating a join_related function, although in only joined one field from a related item, so i have it joining every field now and inserting it into the related datamaps’ object fields. Although i believe very much in using aliasing, so that where clauses don’t need to explicitly specify a table, and also to prevent name collisions.

before, i had it set up so it needed to be called explicitly in the controller, but now i have it called in the get() method automatically..

// in method get()..

$this->total_count = $this->db->count_all($this->table);

// Clear this object to make way for new data
$this->_clear(TRUE);

if( ! $this->table_joined)
{
    $related = array_merge($this->has_one, $this->has_many);
    $this->join_related($related);
}

// Get by built up query
$query = $this->db->get($this->table, $limit, $offset);

..and have it auto-assign the values to each related object (db values for has_one, count for has_many)...

if ($query->num_rows() > 0)
{
    $this->record_count = $query->num_rows();
    // Populate all with records as objects
    //$this->all = $this->_to_object($query->result(), $this->model, $this->fields);
    $this->all = $this->_to_object($query->result_array(), $this->model, $this->fields);
    
    $row = $query->row_array();
    
    foreach($this->has_one as $related)
    {
        if(is_object($this->{$related}))
        {
            $fields = $this->{$related}->fields;
            foreach($fields as $field)
            {    
                $lookup = $related.'_'.$field;
                if(array_key_exists($lookup, $row))
                {    
                    $this->{$related}->{$field} = $row[$lookup];
                }
            }
        }
    }
    
    foreach($this->has_many as $related)
    {
        if(is_object($this->{$related}))
        {
            $lookup = $related.'_count';
            if(array_key_exists($lookup, $row) && ! empty($row[$lookup]))
            {
                $this->{$related}->record_count = $row[$lookup];
            }
            else
            {
                $this->{$related}->record_count = 0;
            }
        }
    }
            
    
    foreach($this->fields as $field)
    {
        if(array_key_exists($field, $row))
        {
            $this->{$field} = $row[$field];
        }
    }
}

so now the query is compressed into one, and all related objects’ fields are populated as if they had get() called on them explicitly.

I also have two additional bits of data, total_count and result_count, auto populated in the datamap. so $user->total_count returns a count of all the users in its table, and $user->result_count returns the number of rows returned in the query..

There was one bump, however, and that was in order for all the records of the datamap to have their related objects populated in the ->all array, i similarly adjusted the _to_object method..

function _to_object($result_array, $model, $fields)
{
    $items = array();
    
    foreach ($result_array as $row_array)
    {
        $item = new $model;
        
        foreach($item->has_one as $related)
        {
            if(is_object($item->{$related}))
            {
                $related_fields = $item->{$related}->fields;
                
                foreach($related_fields as $related_field)
                {    
                    $lookup = $related.'_'.$related_field;
                    
                    if(array_key_exists($lookup, $row_array))
                    {    
                        $item->{$related}->{$related_field} = $row_array[$lookup];
                    }
                    else
                    {
                        $item->{$related}->{$related_field} = NULL;
                    }
                
                }
            }
        }
        
        foreach($item->has_many as $related)
        {
            if(is_object($item->{$related}))
            {
                $lookup = $related.'_count';
                if(array_key_exists($lookup, $row_array) && ! empty($row_array[$lookup]))
                {
                    $item->{$related}->result_count = $row_array[$lookup];
                }
                else
                {
                    $item->{$related}->result_count = 0;
                }
            }
        }
        
        foreach($fields as $field)
        {
            if(array_key_exists($field, $row_array))
            {
                $item->{$field} = $row_array[$field];
            }
            else 
            {
                $item->{$field} = NULL;
            }
        }
            
        array_push($items, $item);
    }

    return $items;
}

so, now it works with the efficient SQL, but with the same kind of ease of use originally designed in DataMapper..

//controller users

$user = new User();
$user->get();

// multiple users, one query

foreach($user->all as $u)
{
     echo $u->username .' is a '.$u->userclass->name.' with a rank of '.$u->userclass->rank.' and belongs to '.$u->usergroup->name . br();
     if($u->document->result_count > 0)
     {
          echo $u->username .' also has '.$u->document->result_count.' documents.'.br();
     }
}

still have to edit the _to_array method to spit out a multi-dimensional array containing the related information

as far as where searches.. i built up a prototype of where_related and i’m still working on that.. also for ‘many’ searches i want to create a where_related_count()

the other AR methods will also need attention (sort by alias, etc).. but i wont be able to do an entire overhaul of DM until i have time..

CC

Wow, lots of great suggestions since I`ve been away!  Thanks OverZealous.com for providing support in my absence.  I, as well as everyone else I`m sure, really appreciate it! 😊  I`m not back yet though as I`m still in Japan (and will be for another 25 or so days).  Finally found time in a nearby Internet Cafe to do some general catching up.  Once I`m home, I`ll have a full look at what`s been posted.  I like your idea of the HTML Viewer Phil.  Don`t have time right now to read up on the relationship codes but looking forward to seeing how you guys are extending DM for what you need.

It seems my host must have had a meltdown at some stage and reverted my site to a backed up copy of an old version… I don`t have access to my files from here to put the latest back up.  I`ll be changing over to SliceHost.com when I get back.  My current host is sooo unreliable…

Anyway, that`s all from me!  My wife and I are off to dinner 😊

hi, i would like to ask question about datamapper.

1. for example i have 3 tables which is users, entries, languages.

users table have 2 field = (id, username)
languages table have 2 field (id, language)

normally, when i create entries table i include the user_id and language_id in it as foreign key, so the entries table will have 4 field = (id, user_id(FK), language_id(FK), content)

question:
if i’m gonna use datamapper, so i have to only create 2 field in entries table which is (id, content), then create join table named entries_users and have their id in table fields, also create entries_languages table and have their id in table fields.

is this correct?

2. for example, i have 2 table which is users and friends.

normally in my mind i will create table like this

users (id, username)

friends (user_id, friend_user_id, mute)
note: friend_user_id is also from user_id, mute is for example, i don’t want this friend_user_id message showup.

can you suggest how should i build the table if i want to use datamapper

thank you 😊

Yes, you have the correct idea for example 1 - you need a dedicated table with [id] for data, and a dedicated table for each relationship.  There, currently, is no way to store or retrieve data stored in a relationship table.

If you do this, you almost certainly will need to see my extension of DataMapper above, which allows you to add relationships to queries (where_related).  It looks like commandercool is working on a fix for the relationship tables, but I’m not sure how easy it is to implement that yet.

Also, you might want to create an in-between subclass of DataMapper so you can add tweaks to all of your models.  If you look back up the thread, there are examples of how to do this.  (Basically, I created a class called DataMapperExt that extends DataMapper.  Then all your models subclass DataMapperExt - you can name it anything you want.)

For example 2:
It gets trickier when doing self-referencing tables, but not too difficult.  There are good examples of this in the DataMapper documentation.  The process is:

Create the User model like normal.
Create a Friend model.  This model should subclass User, and explicitly set $table to “users”.
Add “user” to Friend::$has_many in the Friend constructor
Add “friend” to User::$has_many in the User constructor
Create a table called friends_users, with the columns (id, friend_id, user_id).

Now you can get the friends of a user through $user->friends->get.  Note, however, the Friend models will not be able to dig deeper without being converted into Users.  There isn’t an easy solution to this, yet.

Good Luck!

I just updated to CI 1.7, but I think datamapper isn’t working well with it:

A Database Error Occurred
Error Number: 1054

Unknown column 'archers.*' in 'field list'

SELECT `archers`.`*` FROM (`archers`) LEFT JOIN `z_archers_players` ON `archers`.`id` = `archer_id` LEFT JOIN `players` ON `players`.`id` = `player_id` WHERE `archers`.`display_name` = 'Ettst' AND `players`.`id` = 8

Does anyone else have the same problem?

Thanks!

UPDATE:
I added ->select(‘id, display_name, etc’) to the function and it worked. Is there a way so that i don’t need to select all the columns everytime? Thanks again!

I have not upgraded yet (Thanks for letting me know about it, though 😉 ), but I would suggest using:

$item->select('*', TRUE);

That TRUE turns off identifier protection.

If that works, you might want to change it within DataMapper for now, until a better fix can be found.

Yes, you have the correct idea for example 1 - you need a dedicated table with [id] for data, and a dedicated table for each relationship.  There, currently, is no way to store or retrieve data stored in a relationship table.

If you do this, you almost certainly will need to see my extension of DataMapper above, which allows you to add relationships to queries (where_related).  It looks like commandercool is working on a fix for the relationship tables, but I’m not sure how easy it is to implement that yet.

Also, you might want to create an in-between subclass of DataMapper so you can add tweaks to all of your models.  If you look back up the thread, there are examples of how to do this.  (Basically, I created a class called DataMapperExt that extends DataMapper.  Then all your models subclass DataMapperExt - you can name it anything you want.)

For example 2:
It gets trickier when doing self-referencing tables, but not too difficult.  There are good examples of this in the DataMapper documentation.  The process is:

Create the User model like normal.
Create a Friend model.  This model should subclass User, and explicitly set $table to “users”.
Add “user” to Friend::$has_many in the Friend constructor
Add “friend” to User::$has_many in the User constructor
Create a table called friends_users, with the columns (id, friend_id, user_id).

Now you can get the friends of a user through $user->friends->get.  Note, however, the Friend models will not be able to dig deeper without being converted into Users.  There isn’t an easy solution to this, yet.

Good Luck!

Thanks for your answers 😊

I would like to ask is there any tool to help me migrating the table (currently have records in it) to match the DataMapper table requirement (ie. dedicated table for each relationship).

I am interested using this datamapper just after reading the userguide because it’s looks like easy to use and have power, but currently i have many tables with many relationship using foreign key, it’s a bit pain in the ass to change it manually without tool :D

I don’t know of any tools.  You could look for some kind of database normalization tool, but I doubt it would work.

I know that pain, though.  Just get through it - it doesn’t take quite as long as you might think 😜 .

@dexcell

Here’s how you would set up your tables in your example:

table list

object tables

users ( id, username)
languages (id, language )
entries ( id, content )
friends ( id, mute, field2, field3, etc. )

linking tables

languages_users ( id, language_id, user_id )
friends_users (id, friend_id, user_id )
entries_users (id, entry_id, user_id )
entries_languages (id, entry_id, language_id )

Models - the models need to relate both ways, ie if user->has_one[‘language’] then language must has_one[‘user’] or has_many[‘user’]

user.php :

has_one = array('language');
has_many = array('entry','friend');

entry.php :

has_one = array('user','language');

friend.php :

has_one = array('user');

language.php :

has_many = array('user','entry');

it can be a pain to port foreign keys over to the new table setup. I’ve already had to deal with it and haven’t even tried to find any software to do it for me. what i usually do is write a script in a temporary controller that does it. It just reads the table with the keys and creates INSERT statements that i can just dump into PhpMyAdmin or something, so in your example (using CI’s active record from a controller) here’s what you could do to parse the ‘entries’ table you had with the foreign keys :

// table 'entries' - (id, user_id(FK), language_id(FK), content)

$this->db->from('entries');
$query = $this->db->get();

foreach($query->result() as $row)
{
    $sql = "INSERT INTO `entries_languages` SET `entry_id` = '".$row->id."', `language_id` = '".$row->language_id."' LIMIT 1;";
    $sql2 = "INSERT INTO `entries_users` SET `entry_id` = '".$row->id."', `user_id` = '".$row->user_id."' LIMIT 1;";

    echo $sql . br() . $sql2 . br();
}

that should spit out all the insert statements you just copy/paste and dump into the database. Just do that for every table that has keys, and write it up to insert into the linking table for each key.. so if you have 4 FKs you’ll create 4 separate INSERT statements

then when that’s run, just drop the user_id and language_id columns from ‘entries’ and you should be fine.

also, you could optionally create each $sql statement and then run $this->db->query($sql), or even run

$this->db->insert('entries_languages',array('entry_id'=>$row->id,'language_id'=>$row->language_id));

but personally i like to see the statements it creates before running them, since i want to make sure they’re correct.

CC

^
Thank you overzealous and CC 😊

To be able to use Datamapper with CI 1.7 with the buggy backticks, you need some changes in DM:

function select($select = '*', $protect_identifiers = FALSE) // was TRUE

in function _changed:
$this->db->select($field, FALSE); //AR FALSE parameter added

in function _related:
$this->db->select($this->table . '.*', FALSE); //AR FALSE parameter added

After that, everything functions as usual again.

To be able to use Datamapper with CI 1.7 with the buggy backticks, you need some changes in DM:

function select($select = '*', $protect_identifiers = FALSE) // was TRUE

in function _changed:
$this->db->select($field, FALSE); //AR FALSE parameter added

in function _related:
$this->db->select($this->table . '.*', FALSE); //AR FALSE parameter added

After that, everything functions as usual again.

I think this is fixed in latest SVN, so if you update your CI system folder you won’t have to modify DM

Hi,
I’ve played about with datamapper in a few different projects. Its worked great!
However I’ve recently hit a bit of a problem whenever I try to access a relationship.

Fatal error: Call to a member function get() on a non-object in .../application_store/controllers/Welcome.php on line 46

The offending code seems to be $c->product->get(). For some reason “product” is not being recognized as an object.

// load up the category model
$this->load->model('Category');
                
// create new Category model
$c = new Category();
        
// select chosen category
$c->where('id', $cat_id)->get();
                
// Populate the related catergory object with all related products
$c->product->get();

The two Datamapper models I have are as follows

class Category extends DataMapper {
    
    var $has_many = array('product' => 'products');    
    var $table = "categories";    
    
    function Category()
    {
        parent::DataMapper();
    }
}

class Product extends DataMapper {
    
    var $has_one = array('category' => 'categories');
    
    function Product()
    {
        parent::DataMapper();
    }
}

I’m running datamapper 1.3.4 as I can’t find a link to the latest 1.4.5 build.
Thanks

yeah the stensi.com site still doesn’t have the latest version to download.. i’m attaching v 1.4.5 here.

as for your models, i dunno at what point the relation arrays changed, but right now you should only specify the model names in the array (no associative keys), so it should be:

// Category Model
var $has_many = array('product');

and

// Product Model
var $has_one = array('category');

lemme know if that works for you.

CC

Nice one, a combination of changing the relationship arrays and upgrading to 1.4.5 worked a treat 😊