Active Record Save (insert/update) And destroy Feature

**oddz** · Apr 20, 2009 04:19

solution:

PHP Code:


class ActiveRecord {

    public static function _find($className,$options) {
    
        $config = ActiveRecordModelConfig::getModelConfig($className);
        
        return $config->getTable();
    
    }

}


class User extends ActiveRecord {

    public static $table = 'users';
    
    public static function find() {
        $args = func_get_args();
        return parent::_find(__CLASS__,$args);
    }    

}

User::find(); returns 'users'

Now the base class can access all information about itself and also generate new model configurations for included models.

PHP Code:


class ActiveRecord {

    public static function _find($className,$options) {
    
        $config = ActiveRecordModelConfig::getModelConfig($className);
               $postConfig = ActiveRecordModelConfig::getModelConfig('Post');
        
        return $config->getTable().':'.$postConfig->getTable();
    
    }

}


class User extends ActiveRecord {

    public static $table = 'users';
    
    public static function find() {
        $args = func_get_args();
        return parent::_find(__CLASS__,$args);
    }    

}

User::find(); returns 'users : posts '

Basic dynamic select:

PHP Code:


class ActiveRecord {

    public static function _find($className,$options) {
    
        $config = ActiveRecordModelConfig::getModelConfig($className);
        
        return 'SELECT '.implode(',',$config->getFields()).' FROM '.$config->getTable();
    
    }

}


class User extends ActiveRecord {

    public static $table = 'users';

       public static $fields = array('id','name');
    
    public static function find() {
        $args = func_get_args();
        return parent::_find(__CLASS__,$args);
    }    

}

User::find(); returns 'SELECT id,name FROM users'

Its no where near that simple, but that is the basic idea.

**oddz** · Apr 20, 2009 08:34

Originally Posted by me

I'm just trying to decide whether or not its worth the hassle. Considering it breaks the current implementation. I haven't found a need for it yet, that is why I ask.

I do have a example, which I avoided by using a separate table.

project_items
- id
- file (references files(id))
- thumbnail (references files(id))

files
- id
- path

In this case a project_item can have 2 files which breaks the system because dependencies are resolved to the model name. Furthermore, its always assumed that a dependency can only resolve to one field. However, in this case it can resolve to two file and thumbnail. Now, while this won't "break" the system only one field will ever be resolved to. In this case the association would never resolve to thumbnail since file preceded it.

$project_item->file (file instance => file)

Extending the File ActiveRecord class with Thumbnail seems to do the trick:

Thumbnail Class

PHP Code:


require_once('file.class.php');
class Thumbnail extends File {
    
    public static function find() {
        $args = func_get_args();
        return parent::_find(__CLASS__,$args);
    }

}

File Class

PHP Code:


class File extends ActiveRecord {

    public static $table = 'files';
    
    public static $primaryKey = 'id';
    
    public static $fields = array(
        'id'
        ,'media_type_id'
        ,'name'
        ,'path'
        ,'created'
    );
    
    public static $requiredFields = array(
        'media_type'
        ,'name'
        ,'path'
    );
    
    public static $defaults = array(
        'created'=>'NULL'
    );
    
    public static $dataTypes = array(
        'id'=>'int(10) unsigned'
        ,'media_type_id'=>'int(10) unsigned'
        ,'name'=>'varchar(100)'
        ,'path'=>'varchar(255)'
        ,'created'=>'timestamp'
    );
    
    public static $transformations  = array(
    
        'created'=>array(
            'save'=>'FROM_UNIXTIME({this})'
        )
    
    );
    
    public static $foreignKeys = array(
        'media_type_id'=>array('MediaType','id')
    );
    
    public static $hasMany = array('project_items');
    
    public static $belongsTo = array('media_type');
    
    public static function find() {
        $args = func_get_args();
        return parent::_find(__CLASS__,$args);
    }

}
?>

Case

PHP Code:


$projects = 
Project::find(
    'all'
    ,array(
        'include'=>'project_items'
    )
    ,array(
        'require'=>false
        ,'include'=>array('file','thumbnail')
    )
    ,array(
        'join'=>'LEFT'
        ,'include'=>array('media_type')
    )
    ,array(
        'join'=>'LEFT'
    )
    ,array(
        'join'=>'LEFT'
        ,'include'=>array('media_type')
    )
    ,array(
        'join'=>'LEFT'
    )
);

echo '<pre>',print_r($projects[0]),'</pre>';

Generated SQL

Code SQL:

SELECT 
     t0.`id` AS t0_id
     ,t0.`name` AS t0_name
     ,t0.`summary` AS t0_summary
     ,t0.`weight` AS t0_weight
     ,t0.`created` AS t0_created
     ,t1.`id` AS t1_id
     ,t1.`project_id` AS t1_project_id
     ,t1.`thumbnail_id` AS t1_thumbnail_id
     ,t1.`file_id` AS t1_file_id
     ,t1.`title` AS t1_title
     ,t1.`summary` AS t1_summary
     ,t1.`weight` AS t1_weight
     ,t1.`created` AS t1_created
     ,t2.`id` AS t2_id
     ,t2.`media_type_id` AS t2_media_type_id
     ,t2.`name` AS t2_name
     ,t2.`path` AS t2_path
     ,t2.`created` AS t2_created
     ,t3.`id` AS t3_id
     ,t3.`media` AS t3_media
     ,t3.`mime` AS t3_mime
     ,t4.`id` AS t4_id
     ,t4.`media_type_id` AS t4_media_type_id
     ,t4.`name` AS t4_name
     ,t4.`path` AS t4_path
     ,t4.`created` AS t4_created
     ,t5.`id` AS t5_id,t5.`media` AS t5_media
     ,t5.`mime` AS t5_mime 
  FROM 
      projects AS t0 
  LEFT 
  JOIN 
      project_items AS t1 
    ON 
      t0.id = t1.project_id 
  LEFT 
  JOIN 
      files AS t2 
    ON 
      t1.file_id = t2.id 
  LEFT 
  JOIN 
      media_types AS t3 
    ON 
      t2.media_type_id = t3.id 
  LEFT 
  JOIN 
      files AS t4 
    ON 
      t1.thumbnail_id = t4.id 
  LEFT 
  JOIN 
      media_types AS t5 
    ON 
      t4.media_type_id = t5.id

Collected result Project ActiveRecord

Code:

**Vali** · Apr 20, 2009 13:54

Originally Posted by oddz

PHP Code:


class User extends ActiveRecord {



    public static $table = 'users';

    

    public static function find() {

        $args = func_get_args();

        return parent::_find(__CLASS__,$args);

    }    



}

Should be:

PHP Code:


class User extends ActiveRecord implements iActiveRecord {



    public static $table = 'users';

    

    public static function find() {

        $args = func_get_args();

        return parent::_find(__CLASS__,$args);

    }    



}

With iActiveRecord being an interface where the "find" function is required.

**oddz** · Apr 20, 2009 14:17

ActiveRecord is a abstract class with find() being a abstract method. Got it covered

There isn't a ActiveRecord interface. Everything is based on two interfaces:

1.) IActiveRecordDataEntity
2.) IActiveRecordModelConfig

The first is a glorified multidemensional array that just holds properties.

PHP Code:


$entity = new ActiveRecordDataEntity();
$entity->name = 'tom90'

The second describes the attributes of a table.

Put them together and you have the base functionality:

PHP Code:


$config = new ActiveRecordModelConfig('User');

foreach($config->getFields() as $field) {
  if($entity->hasProperty($field)) echo '<p>',$entity->getProperty($field),'</p>';
}

The config is responsible for giving the data a context in relationship to a table. The entity is responsible for storing the data.

The ActiveRecord base class implements IActiveRecordDataEntity so it by itself can also be treated as such. Which makes it possible to do things like:

PHP Code:


$user = new User();

$user->setProperty('name','tom90');

or

PHP Code:


$user->name = 'tom90';

which would just resolve to the previous IActiveRecordDataEntity method.

That is unless the argument is a IActiveRecordDataEntity instance itself. In that caseit resolves to a special method of the iActiveRecordDataEntity interface strictly for handling records.

**oddz** · Apr 21, 2009 14:31

PHP Code:


class User {

    public static $hasMany = array('blogs','threads');

}

class Thread {

    public static $belongsTo = array('user');

}

class Blog {

    public static $belongsTo = array('user');
    
    public static $cancelDelete = array('user');    

}


$user->delete();

In this case the user and the users threads would only be removed. Do you think that is the correct way to handle the cascade for the delete? By default the delete cascades unless the flag for cancelDelete(I hate this name) has been set for the dependency. Not sure I like this interface though. Any other ideas or recommendations?

The other thing I was thinking that if a value wasn't required for the field(fk) then it wouldn't be deleted, but that seems flawed also.

**oddz** · Apr 21, 2009 14:49

These are my thoughts on the delete "options". Just some thinking out loud.

-- deletes root and all dependencies recursivly
-- deletes only children recursively

-- deletes specified in memory entities (including root)
-- deletes specified in memory entities (excluding root)

-- keeps specified in memory entities (including root) and deletes everything else recursively
-- keeps specified in memory entities and deletes root with everything recursively

PHP Code:


$user = new User(3);
$user->delete(1);

This would remove all dependencies and the user (root item)

PHP Code:


$user = new User(3);
$user->delete(2);

This would delete all dependencies only. The user would be kept in tact.

PHP Code:


$user = User::find(
    'one'
    ,array(
        'include'=>array('threads','posts')
    )
)

The first line would return a user objects loaded with the users threads and posts.

Now, I was thinking there would be four options in terms of im memory entities.

1.) Deletes only in memory items and root. In this case only the user and its in memory posts or threads would be removed.

2.) Deletes only in memory items excluding root. Same as above but user would be deleted

3.) Keeps in memory items including user and deletes all other dependencies recursivly.

4.) Feeps in memory items but deletes user and all other dependencies recursivly

The integers being passed to the delete method are just for a proof of concept. These options don't actually exists yet. Currently, calling the delete method just removes the entire tree hierarchy for the root item. However, I think it can be much richer then that offering the above options(perhaps others?) so that's my aim. Ideas, thoughts and recommendations appreciated.

**oddz** · Apr 21, 2009 15:04

Here is how I was thinking of making this work.

Scenario: Remove all posts for a thread besides those with 6 and 5 as a primary key:

PHP Code:


$thread = Thread::find(
    'one'
    ,array(
        'id'=>5
        ,'include'=>array('posts')
    )
    ,array(
        'require'=>false
        ,'(id IN'=>array('(?,?))',6,5)
    )
);

$thread->delete();

1.) DELETE FROM threads WHERE id = 5 (ommit this)
2.) DELETE FROM posts WHERE id NOT IN (5,6)

**oddz** · Apr 21, 2009 16:39

Do you think it is necessary to bind data to the query in this instance? I don't really think its a must considering this module acts on objects that have already been brought into memory:

PHP Code:


$project = new Project(24);
$delete = new ActiveRecordDelete(array($project));
$delete->query(ActiveRecord::getConnection());

Currently that would run the below queries:

Code:

SQL: DELETE FROM won_projects WHERE bid_id IN (SELECT DISTINCT t0.id FROM bids AS t0 INNER JOIN projects AS t1 ON t0.project_id = t1.id WHERE t1.id = 24)

SQL: DELETE FROM won_projects WHERE project_id = 24

SQL: DELETE FROM bids WHERE project_id = 24

SQL: DELETE FROM projects WHERE id = 24

**oddz** · Apr 21, 2009 16:57

Wouldn't it be quicker and safer as well considering I may be running any number of queries with any number of primary keys in an IN or NOT IN condition?

**oddz** · Apr 22, 2009 10:03

Anothe ridea I've been throwing around is something like a cascading update. For example, if multiple models have the same fields in columns then it would be possible to update them similar to the way a delete would work. This may come in handy if there is a model named thread and another named post. Then both share a status column. By setting the status of a thread to 0 and calling a special method with a name yet to be determined the threads posts status column could also update to 0.

$thread = new Thread(2);
$thread->status = 0;
$thread->cascadeUpdate();

Maybe something like that I'm thinking. Also, maybe it would be possible in the model to declare fields that are related. So if the post model "status" field isn't named status one could declare a array that resolves that relationship. I was thinking something like:

Code:

class Post {
         public static $links = array(
                    'thread'=>array('status'=>'hidden')
         );
}

maybe?

**b1ind** · Apr 23, 2009 09:27

Hey, hate to barge in, but maybe you could try this.

Code PHP:

 
//For related objects that should be deleted in a cascading way:
$this->ownsMany = array();
 
//For related objects that should NOT be deleted
$this->hasMany = array();

**oddz** · Apr 23, 2009 09:51

Currently the individual models determine whether of not they are deleted. To me that seems more appropriate. This is determined based on the parent node. Where the parent node represents a independent and child of that node represents the dependent.

User
- Post
- Thread
  - Post
- BlogEntry
  - BlogComment
- BlogComment

In this case when the destroy() method is called on a user object the delete would cascade by default. However, if inside the Post class where property such as:

public static $cascadeDelete = array('thread'=>false)

Then the branch for the post residing as a child of the thread would not be removed. Only the posts that are direct children of the user would be remove. So the Post for the threads started by the user would stay in tact in this instance.

I'm thinking about doing a similar thing for the link mechanism. There is already a cascading save. However, that cascading save was built codependent of the ability to link up properties and I believe it should stay that way. Instead I think it would be appropriate to have a separate method and property like cascade delete to build relationships on the field level of models.

For example, the above hierarchy needs to generated regardless of whether dealing with a delete or a "cascadingUpdate" . Essentially, every node within that hierarchy would need to be processed in a similar manor to the delete. Only instead of a delete statement being generated a update statement with the appropriate bound parameters would need to be made. The system would also need to only add node if the a child field can be related to a parent field. That could either be expressed based on two models sharing field names or set explicitly I'm thinking.

// inside post class
public static $link = array('thread'=>array('status'=>'status'));

Where post status would be linked to the thread status field. So when the status of a thread is changed all the posts status for that thread could also be changed. This wouldn't happen in accordance with the save() method though. There would be a separate method that runs this type of hierarchical save based on the model tree.

Where is would come in particular handy would be a situation where items are hidden rather then removed from the database. Instead of bringing all the posts into memory and saving individually one could just change the status of the thread and the related post would also be affected.

PHP Code:


$thread = new Thread(7);
$thread->status = 0;
$thread->cascadeUpdate();

Since, the cascade update would work on the model hierarchy level only the Tread(one item) would need to be brought into memory instead of also bringing in all the posts like the below:

PHP Code:


$thread = Thread::find('one',array('id'=>7,'include'=>'posts'));
$thread->status=0;
foreach($thread->posts as $post) $post->status = 0;
$thread->save();

That does the same thing still generating only two queries. However, the posts need to be brought into memory to modify. So if there are 100 posts there would be 100 post items acted upon. Its obviously more efficient to have the option of acting only on parent object and having the change cascade to children if and only if the changed field can be resolved successfully to a child model field though its links property.

**oddz** · Apr 28, 2009 23:41

Any opinions on this syntax for subqueries?

PHP Code:


User::find(
    array(
        'dynamic'=>array(
            'messages'=>PersonalMessage::find(
                'selectCount',array('id'=>array('User.id'))
            )
        )
    )
);

Where the find method can return the select object by passing selectCount to the find method. I'm finding it difficult to do this any other way other then embedding the string directly which is supported.

**oddz** · Apr 29, 2009 01:34

Simple example of of using a subquery in the where clause. The main problem at this point is by default the primary key is always selected. So there need to be a good way to say its ok to not select the primary key.

PHP Code:


$projects = 
Project::find(array(
    'id NOT IN'=>WonProject::find('subquery',array('select'=>array('project_id')))
));

Code SQL:

SELECT 
     t0.`id` AS t0_id
     ,t0.`user_id` AS t0_user_id
     ,t0.`category_id` AS t0_category_id
     ,t0.`range_id` AS t0_range_id
     ,t0.`title` AS t0_title
     ,t0.`message` AS t0_message
     ,t0.`status` AS t0_status
     ,t0.`created` AS t0_created 
  FROM 
      projects AS t0 
  WHERE 
      t0.id NOT IN 
      (SELECT 
            t0.`project_id` AS t0_project_id
            ,t0.`id` AS t0_id 
         FROM 
             won_projects AS t0 
       )

I'm thinking about just using some regex to remove the id field here. The only problem is that if multiple models are included in the subquery then a regex solution wouldn't work well. I was also thinking about adding a subquery property. If this was present then nothing would be selected by default.

So the actual query I would need generated in this instance would be one where the primary key isn't selected for the subquery.

Code SQL:

SELECT 
     t0.`id` AS t0_id
     ,t0.`user_id` AS t0_user_id
     ,t0.`category_id` AS t0_category_id
     ,t0.`range_id` AS t0_range_id
     ,t0.`title` AS t0_title
     ,t0.`message` AS t0_message
     ,t0.`status` AS t0_status
     ,t0.`created` AS t0_created 
  FROM 
      projects AS t0 
  WHERE 
      t0.id NOT IN 
      (SELECT 
            t0.`project_id` AS t0_project_id
         FROM 
             won_projects AS t0 
       )

**oddz** · Apr 29, 2009 12:18

What would be the best array index name for deselecting the primary key?

The problem I'm having is that the below syntax would deselect all fields besides the primary key. This is intended because the primary key is always required to identify the uniqueness of the object. That works beautifully. However, when used as a subquery the primary key is not required.

PHP Code:


User::find(
   array('select'=>array())
);

The other option that is the inverse of select is deselect. By default all model fields are included in the select statement unless a select option has been specified. On the other hand, when a deselect option is specified the field names within that array are removed from the select statement. So the below would essentially remove the password field from the select statement.

PHP Code:


User::find(
   array('deselect'=>'pwd')
)

Maybe the best way to handle this would be to force a deselect on the primary key field:

PHP Code:


User::find(
   array('deselect'=>'id')
)

However, the problem with this is it embeds the logic statically. In theory the way the system is set up the primary key name can be changed. It is merely a concept rather then a field name. So embedding the primary key field name goes against this idea.

The other option I was thinking about was to use a special keyword. However, that would mean that the keyword can't be used as field name ever.

PHP Code:


User::find(
   array('deselect'=>'pk') // references primary key whatever field it may be
)

The other option was to pass a true or false for the deselect. This would essentially remove all columns. So then columns could be dynamically added.

PHP Code:


User::find(
   array('deselect'=>true) // nothing is selected by default
   ,'dynamic'=>array(
       'total'=>'COUNT(*)'
   )
)

The only field that would be selected in the above example would then be the dynamic field total. Which would make it possible to use this nested in the where clause.

My last idea was just to add a new option like deslectAll which would be a boolean of true or false.

PHP Code:


User::find(
   array('deselectAll'=>true) // nothing is selected by default
   ,'dynamic'=>array(
       'total'=>'COUNT(*)'
   )
)

or perhaps call it empty:

PHP Code:


User::find(
   array('empty'=>true) // nothing is selected by default
   ,'dynamic'=>array(
       'total'=>'COUNT(*)'
   )
)

That seems semantically correct.

could go ruby style:

PHP Code:


User::find(
   array('nil'=>true) // nothing is selected by default
   ,'dynamic'=>array(
       'total'=>'COUNT(*)'
   )
)

Although in theory a developer could change the related constant to hookey_pookey if they wished. I'm just trying to think of the best getter and setter names for the interface which these options pertain.

I'm leaning towards empty. It seems the most intuitive,semantically correct and short.

**oddz** · Apr 29, 2009 12:58

So now this:

PHP Code:


$projects = 
Project::find(array(
    'id NOT IN'=>WonProject::find(
        'subquery'
        ,array(
            'empty'=>true,
            'select'=>array('project_id')
        )
    )
));

would generate:

Code SQL:

SELECT
     t0.`id` AS t0_id
     ,t0.`user_id` AS t0_user_id
     ,t0.`category_id` AS t0_category_id
     ,t0.`range_id` AS t0_range_id
     ,t0.`title` AS t0_title
     ,t0.`message` AS t0_message
     ,t0.`status` AS t0_status
     ,t0.`created` AS t0_created
  FROM
      projects AS t0
  WHERE
      t0.id NOT IN
      (SELECT
            t0.`project_id` AS t0_project_id
         FROM
             won_projects AS t0
       )

The find method could even set the empty option as false if the mode is equal to subquery.

PHP Code:


$projects = 
Project::find(array(
    'id NOT IN'=>WonProject::find(
        'subquery' // mode is subquery so empty option set to false for all nodes
        ,array(
            'select'=>array('project_id')
        )
    )
));

**oddz** · Apr 29, 2009 14:29

This syntax seems to working well for a correlated subquery.

In this example the total messages for each user will be a dynamic property called total_messages on each User object.

PHP Code:


$users = 
User::find(array(
    'dynamic'=>array(
        'total_messages'=>PersonalMessage::find(
            'subquery'
            ,array(
                'empty'=>true
                ,'select'=>array()
                ,'user_id'=>array('User.id')
                ,'dynamic'=>array(
                    'total'=>'COUNT(*)'
                )
            )            
        )
    )
    ,'select'=>array('name')
));

Code SQL:

SELECT 
     t1.`name` AS t1_name
     ,t1.`id` AS t1_id
     ,(
     SELECT 
           COUNT(*) AS t0_total 
       FROM 
           personal_messages AS t0 
       WHERE 
           t0.user_id = t1.id
      ) AS t1_total_messages 
   FROM 
       users AS t1

Now the tricky part is figuring out how a subquery can be included in a join. So essentially it would be possible to include a subquery is what I'm thinking.

Hypothetically something like:

PHP Code:


Project::find(
     array(
         'include'=>array(
             Bid::find('subquery')
         )
     )
)

**oddz** · Apr 30, 2009 09:59

These are the problems I'm facing attempting to allow the joining of subqueries.

resolving the relationship (on clause)
storing data

Resolving the relationship could be solved with a extra array key called 'on'. The on keyword could be used to defined a relationship at run time.

PHP Code:


Post::find(array(
    'include'=>array(
        Post::find(
            'subquery'
            ,array(
                'group'=>'thread_id'
                ,'dynamic'=>array(
                    'num_posts'=>'COUNT(Post.id)'
                    ,'recent_post'=>'MAX(Post.created)'
                )
            )            
        )
    )
    ,array(
        'on'=>array(
            'created'=>'recent_post'
            ,'thread_id'=>'thread_id'
        )
    )
));

Although, this seems incredibly dirty I'm having trouble thinking about another way to include this feature.

The question is where the result object would resolve to as a property. Since the root object of the subquery is Post on would think it should resolve to a post:

$post->post[0]->recent_post;

However, then it wouldn't be possible to include posts table at the same level. Instead I was thinking an alternative might be something along the lines of the below.

PHP Code:


Post::find(array(
    'include'=>array(
        Post::find(
            'subquery'
            ,array(
                'group'=>'thread_id'
                ,'dynamic'=>array(
                    'num_posts'=>'COUNT(Post.id)'
                    ,'recent_post'=>'MAX(Post.created)'
                )
            )            
        )
    )
    ,array(
        'on'=>array(
            'created'=>'recent_post'
            ,'thread_id'=>'thread_id'
        )
               'propertyName'=>'xxxx'
    )
));

The property name could describe the name of the dynamic property which thie information of the subquery is available.

$post->xxx[0]->recent_post

A possibility may also be to wrap the subquery in a new object descriptive of these things:

PHP Code:


Post::find(array(
    'include'=>array(
        new DerivedModel(Post::find(
            'subquery'
            ,array(
                'group'=>'thread_id'
                ,'dynamic'=>array(
                    'num_posts'=>'COUNT(Post.id)'
                    ,'recent_post'=>'MAX(Post.created)'
                )
            )            
        ),'xxxx')
    )
    ,array(
        'on'=>array(
            'created'=>'recent_post'
            ,'thread_id'=>'thread_id'
        )
    )
));

Not sure at this point. This entire feature seems dirty to me though.

User Tag List

Thread: Active Record Save (insert/update) And destroy Feature

Thread Tools

Display

Bookmarks

Bookmarks

Posting Permissions