Posts Table
Code SQL:
CREATE TABLE posts (
	id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
	,thread_id INT(10) UNSIGNED NOT NULL
	,user_id INT(10) UNSIGNED NOT NULL
	,message text NOT NULL
	,STATUS tinyint UNSIGNED NOT NULL
	,created TIMESTAMP NULL DEFAULT NULL
	,PRIMARY KEY(id)
)ENGINE=MYSIAM;

Post Model
PHP Code:
<?php
class Post extends ActiveRecord {

    public static 
$fields = array('id','user_id','thread_id','message','status','created');
    
    public static 
$foreignKeys = array(
        
'user_id'=>array('User','id')
        ,
'thread_id'=>array('Thread','id')
    );
    
    public static 
$primaryKey 'id';
    
    public static 
$requiredFields = array('user_id','thread_id','message','status','created');
    
    public static 
$belongsTo = array('user','thread');
    
    public static 
$transformations = array(
        
'created'=>array(
            
'save'=>'FROM_UNIXTIME({this})'
        
)
    );
    
    public static 
$cascadeUpdate = array(
        
'project'=>array('status'=>'status')
    );
    
    public static function 
find() {
        
$args func_get_args();
        return 
parent::_find(__CLASS__,$args);
    }

}
?>
Task: select most recent post per thread.

That task can be broken down into the below method call.

PHP Code:
$posts =
Post::find(
    array(
        
'select'=>array('message')
        ,
'include'=>array(
            
Post::find(
                
ActiveRecord::findSelect
                
,array(
                    
'group'=>'thread_id'
                    
,'dynamic'=>array(
                        
'recent_post'=>'MAX(Post.created)'
                    
)
                    ,
'select'=>array('thread_id')
                )
            )
        )
    )
    ,array(
        
'association'=>array(
            
'thread_id'=>'t0_thread_id'
            
,'created'=>'t0_recent_post'
        
)
    )
); 
The above method will generate the following SQL.

Code SQL:
SELECT 
      t2.`t0_thread_id` AS t0_thread_id
      ,t2.`t0_id` AS t0_id
      ,t2.`t0_recent_post` AS t0_recent_post
      ,t1.`message` AS t1_message
      ,t1.`id` AS t1_id 
  FROM 
      posts AS t1 
 INNER 
  JOIN 
      (SELECT 
            t0.`thread_id` AS t0_thread_id
            ,t0.`id` AS t0_id
            ,MAX(t0.created) AS t0_recent_post 
         FROM 
             posts AS t0 
        GROUP 
           BY t0.thread_id) AS t2 
     ON 
       t1.`thread_id` = t2.`t0_thread_id` 
    AND 
       t1.`created` = t2.`t0_recent_post`

Which will yield the below array of object:

HTML Code:
Array
(
    [0] => Post Object
        (
            [_data:private] => ActiveRecordDataEntity Object
                (
                    [_data:private] => Array
                        (
                            [message] => Array
                                (
                                    [0] => xwd w qd qw 
                                )

                            [id] => Array
                                (
                                    [0] => 11
                                )

                            [posts] => Array
                                (
                                    [0] => ActiveRecordCollection Object
                                        (
                                            [container:protected] => Array
                                                (
                                                    [0] => Post Object
                                                        (
                                                            [_data:private] => ActiveRecordDataEntity Object
                                                                (
                                                                    [_data:private] => Array
                                                                        (
                                                                            [thread_id] => Array
                                                                                (
                                                                                    [0] => 6
                                                                                )

                                                                            [id] => Array
                                                                                (
                                                                                    [0] => 8
                                                                                )

                                                                            [recent_post] => Array
                                                                                (
                                                                                    [0] => 2009-04-13 09:28:08
                                                                                )

                                                                        )

                                                                )

                                                        )

                                                )

                                        )

                                )

                        )

                )

        )

    [1] => Post Object
        (
            [_data:private] => ActiveRecordDataEntity Object
                (
                    [_data:private] => Array
                        (
                            [message] => Array
                                (
                                    [0] => d wq d qw rf qwr
                                )

                            [id] => Array
                                (
                                    [0] => 14
                                )

                            [posts] => Array
                                (
                                    [0] => ActiveRecordCollection Object
                                        (
                                            [container:protected] => Array
                                                (
                                                    [0] => Post Object
                                                        (
                                                            [_data:private] => ActiveRecordDataEntity Object
                                                                (
                                                                    [_data:private] => Array
                                                                        (
                                                                            [thread_id] => Array
                                                                                (
                                                                                    [0] => 8
                                                                                )

                                                                            [id] => Array
                                                                                (
                                                                                    [0] => 14
                                                                                )

                                                                            [recent_post] => Array
                                                                                (
                                                                                    [0] => 2009-04-14 09:45:19
                                                                                )

                                                                        )

                                                                )

                                                        )

                                                )

                                        )

                                )

                        )

                )

        )

    [2] => Post Object
        (
            [_data:private] => ActiveRecordDataEntity Object
                (
                    [_data:private] => Array
                        (
                            [message] => Array
                                (
                                    [0] => r gerg e rgeg
                                )

                            [id] => Array
                                (
                                    [0] => 17
                                )

                            [posts] => Array
                                (
                                    [0] => ActiveRecordCollection Object
                                        (
                                            [container:protected] => Array
                                                (
                                                    [0] => Post Object
                                                        (
                                                            [_data:private] => ActiveRecordDataEntity Object
                                                                (
                                                                    [_data:private] => Array
                                                                        (
                                                                            [thread_id] => Array
                                                                                (
                                                                                    [0] => 5
                                                                                )

                                                                            [id] => Array
                                                                                (
                                                                                    [0] => 7
                                                                                )

                                                                            [recent_post] => Array
                                                                                (
                                                                                    [0] => 2009-04-24 12:24:36
                                                                                )

                                                                        )

                                                                )

                                                        )

                                                )

                                        )

                                )

                        )

                )

        )

    [3] => Post Object
        (
            [_data:private] => ActiveRecordDataEntity Object
                (
                    [_data:private] => Array
                        (
                            [message] => Array
                                (
                                    [0] => cxw dew dq
                                )

                            [id] => Array
                                (
                                    [0] => 18
                                )

                            [posts] => Array
                                (
                                    [0] => ActiveRecordCollection Object
                                        (
                                            [container:protected] => Array
                                                (
                                                    [0] => Post Object
                                                        (
                                                            [_data:private] => ActiveRecordDataEntity Object
                                                                (
                                                                    [_data:private] => Array
                                                                        (
                                                                            [thread_id] => Array
                                                                                (
                                                                                    [0] => 7
                                                                                )

                                                                            [id] => Array
                                                                                (
                                                                                    [0] => 13
                                                                                )

                                                                            [recent_post] => Array
                                                                                (
                                                                                    [0] => 2009-04-24 20:41:36
                                                                                )

                                                                        )

                                                                )

                                                        )

                                                )

                                        )

                                )

                        )

                )

        )

    [4] => Post Object
        (
            [_data:private] => ActiveRecordDataEntity Object
                (
                    [_data:private] => Array
                        (
                            [message] => Array
                                (
                                    [0] => wd 2 de12e12
                                )

                            [id] => Array
                                (
                                    [0] => 20
                                )

                            [posts] => Array
                                (
                                    [0] => ActiveRecordCollection Object
                                        (
                                            [container:protected] => Array
                                                (
                                                    [0] => Post Object
                                                        (
                                                            [_data:private] => ActiveRecordDataEntity Object
                                                                (
                                                                    [_data:private] => Array
                                                                        (
                                                                            [thread_id] => Array
                                                                                (
                                                                                    [0] => 9
                                                                                )

                                                                            [id] => Array
                                                                                (
                                                                                    [0] => 16
                                                                                )

                                                                            [recent_post] => Array
                                                                                (
                                                                                    [0] => 2009-05-01 16:20:14
                                                                                )

                                                                        )

                                                                )

                                                        )

                                                )

                                        )

                                )

                        )

                )

        )

)
The above object property hierarchy can be broken down into (per object):

  • id
  • message
  • posts (subquery node/derived table)
    • id
    • thread_id
    • recent_post


Now my concern is that the property representing the subquery is named posts based on the root node of that select statement. However, I feel that the name should be determined by the user or add a extra node for the subquery in which to access the data. Something along the lines of the below hierarchy.


  • id
  • message
  • subquery
    • posts (subquery node/derived table)
      • id
      • thread_id
      • recent_post


However, what would be the best way to allow the user to be able to name that property so that derived information could be accessed through that property?

PHP Code:
$posts[0]->subquery->posts[0]->recent_post 
I'm stumped because normally the property name would be determined based on the model name. However, in this instance the model is derived and doesn't have a name.

Currently the above information would be accessed as shown below.

PHP Code:
$posts[0]->posts[0]->recent_post 
However, if the outer query joins on the posts table again then a conflict occurs between property names. Which is what brings me to the problem of having the user able to name properties which subquery/derived table data would be accessed.

any ideas or recommendations appreciated.