SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Active Record Database pattern for multi-table queries?

    kyberfabrikken's post in the Implementing a data mapper thread has me wondering if there's an elegant solution for using an ARD pattern for multiple tables? I'm working on a blog and as such I have a lot of JOIN queries. For example, for a post I query these tables: posts, users, categories. I'd love to reduce the # of queries I need to write by hand, and thought someone here must have an idea of what to do.

  2. #2
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    Montreal
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have come up with a partial solution, although I will not say that it is necessarily that elegant. Here is an example of a generated query.

    Code MySQL:
    SELECT   question.id                                     AS id,
             question.course_id                              AS course_id,
             question.user_id                                AS user_id,
             question.question                               AS question,
             question.answer_key                             AS answer_key,
             question.hint                                   AS hint,
             question.edited                                 AS edited,
             question.deleted                                AS deleted,
             question.marks                                  AS marks,
             user.id                                         AS __user__id,
             user.email                                      AS __user__email,
             user.password                                   AS __user__password,
             user.name                                       AS __user__name,
             user.is_admin                                   AS __user__is_admin,
             GROUP_CONCAT(DISTINCT qt.tag_id SEPARATOR ', ') AS _tags
    FROM     question question
             INNER JOIN user user
               ON (user.id = question.user_id)
             LEFT JOIN question_tag qt
               ON (qt.question_id = Question.id)
    WHERE    deleted IS NULL
    GROUP BY Question.id
    LIMIT    0,10

    This is built from the entity definitions for Question and User:

    Code PHP:
    class Question extends FAEntity {
     
    	// Define the table here
    	public static function setTableDefinition(FATable $table) {
     
    		$table->setTableName('question');
    		$table->hasColumn('id', array(
    			'primary' => true,
    			'autoIncrement' => true,
    		));
    		$table->hasColumn('course_id', array());
    		$table->hasColumn('user_id', array());
    		$table->hasColumn('question', array());
    		$table->hasColumn('answer_key', array());
    		$table->hasColumn('hint', array());
    		$table->hasColumn('edited', array());
    		$table->hasColumn('deleted', array());
    		$table->hasColumn('marks', array());
     
    		// Define the relation here.  The syntax is very similar to Doctrine
    		// Notice the 'prefetch' that is available for single joins to fetch the
    		// joined row at the same time as the parent row.
    		$table->hasOne('user', array(
    			'class' => 'User',
    			'local' => 'id',
    			'foreign' => 'user_id',
    			'prefetch' => true,
    		));
    	}
     
    	public static function prepareSelect(FAQuery $query, $prefix = '') {
     
    		$query
    			->column("GROUP_CONCAT(DISTINCT qt.tag_id SEPARATOR ', ') AS {$prefix}_tags")
    			->leftJoin("question_tag qt", "qt.question_id=Question.id")
    			->groupBy("Question.id");
    	}
     
    	public function getTags() {
     
    		return array_filter(array_map('trim', explode(',', $this->_tags)));
    	}
     
    	public function postSave() {
     
    		// Update the question_tag table here
    	}
    }

    and User:

    Code PHP:
    class User extends FAEntity {
     
    	public static function setTableDefinition(FATable $table) {
     
    		$table->setTableName('user');
     
    		$table->hasColumn('id', array(
    			'type' => 'int',
    			'primary' => TRUE,
    		));
    		$table->hasColumn('email', array());
    		$table->hasColumn('password', array());
    		$table->hasColumn('name', array());
    		$table->hasColumn('is_admin', array());
     
    		$table->hasMany('questions', array(
    			'class' => 'Question',
    			'local' => 'id',
    			'foreign' => 'user_id',
    		));
    	}
    }

    Now, how does this work. First, we need the concept of a QueryBuilder object so that a query can be incrementally built. In my system that is the FAQuery object. When a query is built, several things happen.
    1. The Query object is created and its table is set to the base table.
    2. A function is called to add the main entity's columns to the query.
    3. A function is called to add all prefetched columns and the appropriate joins to the query while prefixing the column aliases of the prefetched join.
    4. Finally, the prepareSelect method of the base entity is called to add 'dynamic' columns and other features to the query.


    Another benefit that accrued from this approach is the flexibility in fetching record sets. There is one way of fetching an entityset, FAPersistence::findAll('Entity'). This returns an EntitySet with its associated query. Note, however, that the query hasn't been run yet. Filtering the EntitySet is simple. Here is an example from my application:

    Code PHP:
    	$response->questions = $registry->dba->findAll('Question')
    		->where('Question.user_id=?', $request->user->id);

    There are a number of helper methods to deal with this 'late binding' that make using the system a real pleasure.

    Please let me know if you have any questions/comments.

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    User Model
    Code:
    <?php
    class User extends ActiveRecord {
    
    	public static $fields = array('id','name','first_name','last_name','email','pwd','status','created','access','site');
    	
    	public static $primaryKey = 'id';
    	
    	public static $uniqueKeys = array('email','name');
    	
    	public static $transformations = array(
    		'pwd'=>array(
    			'select'=>array('AES_DECRYPT(User.pwd,SHA1(CONCAT(?,User.created)))','mysalt')
    			,'filter'=>array('AES_ENCRYPT(?,SHA1(CONCAT(\'myssalt\',User.created)))')
    			,'insert'=>array('AES_ENCRYPT(?,SHA1(CONCAT(\'mysalt\',FROM_UNIXTIME({created}))))')
    		)
    		,'created'=>array(
    			'insert'=>'FROM_UNIXTIME(?)'
    		)
    	);
    	
    	public static $requiredFields = array(
    		'name'
    		,'first_name'
    		,'last_name'
    		,'email'
    		,'pwd'
    		,'status'
    		,'created'
    		,'access'
    		,'newsletter'
    	);
    	
    	public static $hasMany = array('projects','bids','blog_entries','blog_comments','threads','posts');
    	
    	public static function find() {
    		$args = func_get_args();
    		return parent::_find(__CLASS__,$args);
    	}
    
    }
    ?>
    Project Model
    Code:
    <?php
    class Project extends ActiveRecord {
    
    	public static $fields = array('id','user_id','category_id','range_id','title','message','status','created');
    	
    	public static $transformations = array(
    		'created'=>array(
    			'insert'=>'FROM_UNIXTIME(?)'
    		)
    	);
    	
    	public static $foreignKeys = array(
    		'user_id'=>array('User','id')
    		,'category_id'=>array('Category','id')
    		,'range_id'=>array('Range','id')
    	);
    	
    	public static $dataTypes = array(
    		'id'=>'int(10) unsigned'
    		,'user_id'=>'int(10) unsigned'
    		,'category_id'=>'int(10) unsigned'
    		,'range_id'=>'int(10) unsigned'
    		,'title'=>'varchar(40)'
    		,'message'=>'text'
    		,'status'=>'tinyint unsigned'
    		,'created'=>'timestamp'
    	);
    	
    	public static $primaryKey = 'id';
    	
    	public static $requiredFields = array('user_id','category_id','range_id','title','message','status','created');
    	
    	public static $hasMany = array('bids');
    	
    	public static $belongsTo = array('user','range','category');
    	
    	public static function find() {
    		$args = func_get_args();
    		return parent::_find(__CLASS__,$args);
    	}
    
    }
    ?>
    Category Model
    Code:
    <?php
    class Category extends ActiveRecord {
    
    	public static $fields = array('id','name','weight');
    	
    	public static $primaryKey = 'id';
    	
    	public static $hasMany = 'projects';
    	
    	public static function find() {
    		$args = func_get_args();
    		return parent::_find(__CLASS__,$args);
    	}
    
    }
    ?>
    Range Model
    Code:
    <?php
    class Range extends ActiveRecord {
    
    	public static $fields = array('id','min_value','max_value');
    	
    	public static $primaryKey = 'id';
    	
    	public static $hasMany = array('projects');
    	
    	public static function find() {
    		$args = func_get_args();
    		return parent::_find(__CLASS__,$args);
    	}
    
    }
    ?>

    find Interface
    Code:
    $projects = Project::find(
    	array(
      		'include'=>array('category','user','range','bids')
      	 	'limit'=>3
    	)
    	,array(
    	)
    	,array(
    	  'deselect'=>'pwd'
    	)
    	,array(
    	)
    	,array(
      		'include'=>'user'
      		,'require'=>false
    	)
    	,array(
      		'deselect'=>'pwd'
      		,'require'=>false
    	)
    );
    Query
    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,t1.`id` AS t1_id,t1.`name` AS t1_name,t1.`weight` AS t1_weight,t2.`id` AS t2_id,t2.`name` AS t2_name,t2.`first_name` AS t2_first_name,t2.`last_name` AS t2_last_name,t2.`email` AS t2_email,t2.`status` AS t2_status,t2.`created` AS t2_created,t2.`access` AS t2_access,t2.`site` AS t2_site,t3.`id` AS t3_id,t3.`min_value` AS t3_min_value,t3.`max_value` AS t3_max_value,t4.`id` AS t4_id,t4.`user_id` AS t4_user_id,t4.`project_id` AS t4_project_id,t4.`price` AS t4_price,t4.`message` AS t4_message,t4.`status` AS t4_status,t4.`created` AS t4_created,t5.`id` AS t5_id,t5.`name` AS t5_name,t5.`first_name` AS t5_first_name,t5.`last_name` AS t5_last_name,t5.`email` AS t5_email,t5.`status` AS t5_status,t5.`created` AS t5_created,t5.`access` AS t5_access,t5.`site` AS t5_site FROM projects AS t0 INNER JOIN categories AS t1 ON t0.category_id = t1.id INNER JOIN users AS t2 ON t0.user_id = t2.id INNER JOIN ranges AS t3 ON t0.range_id = t3.id LEFT JOIN bids AS t4 ON t0.id = t4.project_id LEFT JOIN users AS t5 ON t4.user_id = t5.id LIMIT 3

    Find Result
    Code:
    Array
    (
        [0] => Project Object
            (
                [_data:private] => ActiveRecordDataEntity Object
                    (
                        [_data:private] => Array
                            (
                                [id] => Array
                                    (
                                        [0] => 1
                                    )
    
                                [user_id] => Array
                                    (
                                        [0] => 6
                                    )
    
                                [category_id] => Array
                                    (
                                        [0] => 3
                                    )
    
                                [range_id] => Array
                                    (
                                        [0] => 5
                                    )
    
                                [title] => Array
                                    (
                                        [0] => Graphic Design Needed for Project
                                    )
    
                                [message] => Array
                                    (
                                        [0] => This is some info about this post
                                    )
    
                                [status] => Array
                                    (
                                        [0] => 1
                                    )
    
                                [created] => Array
                                    (
                                        [0] => 2009-03-19 19:42:34
                                    )
    
                                [category] => Array
                                    (
                                        [0] => Category Object
                                            (
                                                [_data:private] => ActiveRecordDataEntity Object
                                                    (
                                                        [_data:private] => Array
                                                            (
                                                                [id] => Array
                                                                    (
                                                                        [0] => 3
                                                                    )
    
                                                                [name] => Array
                                                                    (
                                                                        [0] => graphic design
                                                                    )
    
                                                                [weight] => Array
                                                                    (
                                                                        [0] => 2
                                                                    )
    
                                                            )
    
                                                    )
    
                                            )
    
                                    )
    
                                [user] => Array
                                    (
                                        [0] => User Object
                                            (
                                                [_data:private] => ActiveRecordDataEntity Object
                                                    (
                                                        [_data:private] => Array
                                                            (
                                                                [id] => Array
                                                                    (
                                                                        [0] => 6
                                                                    )
    
                                                                [name] => Array
                                                                    (
                                                                        [0] => gary_zmijewski
                                                                    )
    
                                                                [first_name] => Array
                                                                    (
                                                                        [0] => gary
                                                                    )
    
                                                                [last_name] => Array
                                                                    (
                                                                        [0] => zmijewski
                                                                    )
    
                                                                [email] => Array
                                                                    (
                                                                        [0] => zmjwskl@hotmail.com
                                                                    )
    
                                                                [status] => Array
                                                                    (
                                                                        [0] => 1
                                                                    )
    
                                                                [created] => Array
                                                                    (
                                                                        [0] => 2009-03-19 18:51:40
                                                                    )
    
                                                                [access] => Array
                                                                    (
                                                                        [0] => 1
                                                                    )
    
                                                                [site] => Array
                                                                    (
                                                                        [0] => 
                                                                    )
    
                                                            )
    
                                                    )
    
                                            )
    
                                    )
    
                                [range] => Array
                                    (
                                        [0] => Range Object
                                            (
                                                [_data:private] => ActiveRecordDataEntity Object
                                                    (
                                                        [_data:private] => Array
                                                            (
                                                                [id] => Array
                                                                    (
                                                                        [0] => 5
                                                                    )
    
                                                                [min_value] => Array
                                                                    (
                                                                        [0] => 500
                                                                    )
    
                                                                [max_value] => Array
                                                                    (
                                                                        [0] => 1000
                                                                    )
    
                                                            )
    
                                                    )
    
                                            )
    
                                    )
    
                                [bids] => Array
                                    (
                                        [0] => Array
                                            (
                                                [0] => Bid Object
                                                    (
                                                        [_data:private] => ActiveRecordDataEntity Object
                                                            (
                                                                [_data:private] => Array
                                                                    (
                                                                        [id] => Array
                                                                            (
                                                                                [0] => 4
                                                                            )
    
                                                                        [user_id] => Array
                                                                            (
                                                                                [0] => 1
                                                                            )
    
                                                                        [project_id] => Array
                                                                            (
                                                                                [0] => 1
                                                                            )
    
                                                                        [price] => Array
                                                                            (
                                                                                [0] => 300
                                                                            )
    
                                                                        [message] => Array
                                                                            (
                                                                                [0] => message here
                                                                            )
    
                                                                        [status] => Array
                                                                            (
                                                                                [0] => 1
                                                                            )
    
                                                                        [created] => Array
                                                                            (
                                                                                [0] => 2009-03-20 19:46:30
                                                                            )
    
                                                                        [user] => Array
                                                                            (
                                                                                [0] => User Object
                                                                                    (
                                                                                        [_data:private] => ActiveRecordDataEntity Object
                                                                                            (
                                                                                                [_data:private] => Array
                                                                                                    (
                                                                                                        [id] => Array
                                                                                                            (
                                                                                                                [0] => 1
                                                                                                            )
    
                                                                                                        [name] => Array
                                                                                                            (
                                                                                                                [0] => kdawg
                                                                                                            )
    
                                                                                                        [first_name] => Array
                                                                                                            (
                                                                                                                [0] => kevin
                                                                                                            )
    
                                                                                                        [last_name] => Array
                                                                                                            (
                                                                                                                [0] => zmijewski
                                                                                                            )
    
                                                                                                        [email] => Array
                                                                                                            (
                                                                                                                [0] => zmije1w@gmail.com
                                                                                                            )
    
                                                                                                        [status] => Array
                                                                                                            (
                                                                                                                [0] => 1
                                                                                                            )
    
                                                                                                        [created] => Array
                                                                                                            (
                                                                                                                [0] => 2009-03-15 00:59:44
                                                                                                            )
    
                                                                                                        [access] => Array
                                                                                                            (
                                                                                                                [0] => 2
                                                                                                            )
    
                                                                                                        [site] => Array
                                                                                                            (
                                                                                                                [0] => 
                                                                                                            )
    
                                                                                                    )
    
                                                                                            )
    
                                                                                    )
    
                                                                            )
    
                                                                    )
    
                                                            )
    
                                                    )
    
                                                [1] => Bid Object
                                                    (
                                                        [_data:private] => ActiveRecordDataEntity Object
                                                            (
                                                                [_data:private] => Array
                                                                    (
                                                                        [id] => Array
                                                                            (
                                                                                [0] => 3
                                                                            )
    
                                                                        [user_id] => Array
                                                                            (
                                                                                [0] => 3
                                                                            )
    
                                                                        [project_id] => Array
                                                                            (
                                                                                [0] => 1
                                                                            )
    
                                                                        [price] => Array
                                                                            (
                                                                                [0] => 900
                                                                            )
    
                                                                        [message] => Array
                                                                            (
                                                                                [0] => I have a proven track record and would love to participate on your project.
                                                                            )
    
                                                                        [status] => Array
                                                                            (
                                                                                [0] => 1
                                                                            )
    
                                                                        [created] => Array
                                                                            (
                                                                                [0] => 2009-03-19 20:59:28
                                                                            )
    
                                                                        [user] => Array
                                                                            (
                                                                                [0] => User Object
                                                                                    (
                                                                                        [_data:private] => ActiveRecordDataEntity Object
                                                                                            (
                                                                                                [_data:private] => Array
                                                                                                    (
                                                                                                        [id] => Array
                                                                                                            (
                                                                                                                [0] => 3
                                                                                                            )
    
                                                                                                        [name] => Array
                                                                                                            (
                                                                                                                [0] => adam12
                                                                                                            )
    
                                                                                                        [first_name] => Array
                                                                                                            (
                                                                                                                [0] => adam
                                                                                                            )
    
                                                                                                        [last_name] => Array
                                                                                                            (
                                                                                                                [0] => zmijewski
                                                                                                            )
    
                                                                                                        [email] => Array
                                                                                                            (
                                                                                                                [0] => adam12@gmail.com
                                                                                                            )
    
                                                                                                        [status] => Array
                                                                                                            (
                                                                                                                [0] => 1
                                                                                                            )
    
                                                                                                        [created] => Array
                                                                                                            (
                                                                                                                [0] => 2009-03-19 18:51:40
                                                                                                            )
    
                                                                                                        [access] => Array
                                                                                                            (
                                                                                                                [0] => 1
                                                                                                            )
    
                                                                                                        [site] => Array
                                                                                                            (
                                                                                                                [0] => 
                                                                                                            )
    
                                                                                                    )
    
                                                                                            )
    
                                                                                    )
    
                                                                            )
    
                                                                    )
    
                                                            )
    
                                                    )
    
                                                [2] => Bid Object
                                                    (
                                                        [_data:private] => ActiveRecordDataEntity Object
                                                            (
                                                                [_data:private] => Array
                                                                    (
                                                                        [id] => Array
                                                                            (
                                                                                [0] => 2
                                                                            )
    
                                                                        [user_id] => Array
                                                                            (
                                                                                [0] => 4
                                                                            )
    
                                                                        [project_id] => Array
                                                                            (
                                                                                [0] => 1
                                                                            )
    
                                                                        [price] => Array
                                                                            (
                                                                                [0] => 700
                                                                            )
    
                                                                        [message] => Array
                                                                            (
                                                                                [0] => I am very interested in your project.!
                                                                            )
    
                                                                        [status] => Array
                                                                            (
                                                                                [0] => 1
                                                                            )
    
                                                                        [created] => Array
                                                                            (
                                                                                [0] => 2009-03-19 19:57:02
                                                                            )
    
                                                                        [user] => Array
                                                                            (
                                                                                [0] => User Object
                                                                                    (
                                                                                        [_data:private] => ActiveRecordDataEntity Object
                                                                                            (
                                                                                                [_data:private] => Array
                                                                                                    (
                                                                                                        [id] => Array
                                                                                                            (
                                                                                                                [0] => 4
                                                                                                            )
    
                                                                                                        [name] => Array
                                                                                                            (
                                                                                                                [0] => parrish2009
                                                                                                            )
    
                                                                                                        [first_name] => Array
                                                                                                            (
                                                                                                                [0] => greg
                                                                                                            )
    
                                                                                                        [last_name] => Array
                                                                                                            (
                                                                                                                [0] => parrish
                                                                                                            )
    
                                                                                                        [email] => Array
                                                                                                            (
                                                                                                                [0] => gparish@dvls.tv
                                                                                                            )
    
                                                                                                        [status] => Array
                                                                                                            (
                                                                                                                [0] => 1
                                                                                                            )
    
                                                                                                        [created] => Array
                                                                                                            (
                                                                                                                [0] => 2009-03-19 18:51:40
                                                                                                            )
    
                                                                                                        [access] => Array
                                                                                                            (
                                                                                                                [0] => 1
                                                                                                            )
    
                                                                                                        [site] => Array
                                                                                                            (
                                                                                                                [0] => http://www.dvls.tv
                                                                                                            )
    
                                                                                                    )
    
                                                                                            )
    
                                                                                    )
    
                                                                            )
    
                                                                    )
    
                                                            )
    
                                                    )
    
                                            )
    
                                    )
    
                            )
    
                    )
    
            )
    
    )
    [b]possible use[b]
    Code:
    echo $projects[0]->user->name;
    echo $projects[0]->range->min_value;
    echo $projects[0]->range->max_value;
    echo $projects[0]->category->name;
    
    // show all bids
    if($projects[0]->bids) {
    	foreach($projects[0]->bids as $bid) {
    		echo $bid->user->name;
    		echo $bid->created;
    		echo $bid->price;
    	}
    }
    The system and is inspired by Luke Bakers work and my talk(s) with Vali, but takes the entire idea idea of includes to the next level allowing you to essentially include on a include on a include on a include... you get the picture. This will work so long as a relationship can be resolved. Also, it gives you back objects that represent that hierarchical structure as many levels deep as you have includes for each include. The system also binds data rather then embedding requiring a PDO connection be passed to the ActiveRecord class. There are also a host of options for the finder to control the select query such as: select, deselect, filter, conditionMap, condition, group, having, sort, require and join. The one that is most useful and "new" for creating incredibly complex filters is called conditionMap. ConditionMap essentially allows you the ability to define a where clause with the added benefit of binding the condition data. This works via using keywords.

    Example:

    Code:
    $projects = Project::find(
      array(
        'conditionMap'=>'Project.status = {status} AND Project.id NOT IN (SELECT DISTINCT project_id FROM won_projects) '
        ,'condition'=>array('status'=>array('?',1))
      )
    );
    Query
    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.status = ? AND t0.id NOT IN (SELECT DISTINCT project_id FROM won_projects)

    In that example the value 1 would be bound to the {status} placeholder and the map would embedded in the query preceding any filters for that model. The system will also make sure that the proper alias replacement takes place for the word Project in the condition map.

    Also, something not quit new but useful that is included is a magical filter. Any array key supplied that is not a special keyword will become what the system deems a "magical filter". This makes it incredible easy to use for simple queries as well as complex. However, these magical filters are imploded together using AND if more then one exists. Therefore, if you need a a complex OR or Group condition its better to use a condition map, but for simple queries its time and code saving.

    Code:
    $project = Project::find(
      'one'
      ,array(
        'id'=>4
        ,'status>='=>1
        ,'limit'=>1
      )
    );
    Query
    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 = ? AND t0.status >= ? LIMIT 1

    The transformations array inside the model is another unique feature I have yet to see in other like system. Transformations may be defined and will be automatically embedded with their accompanying data being bound. A perfect example of this is the pwd field in the User. Every time a user is selected and the pwd field is present the transformation will be applied so that the pwd property of the object is actually the pwd not the encrypted data. So transformations make it possible to define a mutation in on place and have it applied for that given action ('insert','filter','select'). Something that I think is incredibly useful which no other system provides that I am aware of.

    Also, inspired by Vali on this forum there is a special method that may be called on a Active Record object which has a primary key(unique id) to bring in one of its relations. This method is mainly a router to the find method which handles some simple relationship logic.

    Code:
    $project = new Project(1);
    $bids = $project->getBids(array('include'=>'user','limit'=>2),array('deselect'=>'pwd'));
    Query
    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 = ?SELECT t0.`id` AS t0_id,t0.`user_id` AS t0_user_id,t0.`project_id` AS t0_project_id,t0.`price` AS t0_price,t0.`message` AS t0_message,t0.`status` AS t0_status,t0.`created` AS t0_created,t1.`id` AS t1_id,t1.`name` AS t1_name,t1.`first_name` AS t1_first_name,t1.`last_name` AS t1_last_name,t1.`email` AS t1_email,t1.`status` AS t1_status,t1.`created` AS t1_created,t1.`access` AS t1_access,t1.`site` AS t1_site FROM bids AS t0 INNER JOIN users AS t1 ON t0.user_id = t1.id WHERE t0.project_id = ? LIMIT 2

    result
    Code:
    Array
    (
        [0] => Bid Object
            (
                [_data:private] => ActiveRecordDataEntity Object
                    (
                        [_data:private] => Array
                            (
                                [id] => Array
                                    (
                                        [0] => 4
                                    )
    
                                [user_id] => Array
                                    (
                                        [0] => 1
                                    )
    
                                [project_id] => Array
                                    (
                                        [0] => 1
                                    )
    
                                [price] => Array
                                    (
                                        [0] => 300
                                    )
    
                                [message] => Array
                                    (
                                        [0] => message here
                                    )
    
                                [status] => Array
                                    (
                                        [0] => 1
                                    )
    
                                [created] => Array
                                    (
                                        [0] => 2009-03-20 19:46:30
                                    )
    
                                [user] => Array
                                    (
                                        [0] => User Object
                                            (
                                                [_data:private] => ActiveRecordDataEntity Object
                                                    (
                                                        [_data:private] => Array
                                                            (
                                                                [id] => Array
                                                                    (
                                                                        [0] => 1
                                                                    )
    
                                                                [name] => Array
                                                                    (
                                                                        [0] => kdawg
                                                                    )
    
                                                                [first_name] => Array
                                                                    (
                                                                        [0] => kevin
                                                                    )
    
                                                                [last_name] => Array
                                                                    (
                                                                        [0] => zmijewski
                                                                    )
    
                                                                [email] => Array
                                                                    (
                                                                        [0] => zmije1w@gmail.com
                                                                    )
    
                                                                [status] => Array
                                                                    (
                                                                        [0] => 1
                                                                    )
    
                                                                [created] => Array
                                                                    (
                                                                        [0] => 2009-03-15 00:59:44
                                                                    )
    
                                                                [access] => Array
                                                                    (
                                                                        [0] => 2
                                                                    )
    
                                                                [site] => Array
                                                                    (
                                                                        [0] => 
                                                                    )
    
                                                            )
    
                                                    )
    
                                            )
    
                                    )
    
                            )
    
                    )
    
            )
    
        [1] => Bid Object
            (
                [_data:private] => ActiveRecordDataEntity Object
                    (
                        [_data:private] => Array
                            (
                                [id] => Array
                                    (
                                        [0] => 3
                                    )
    
                                [user_id] => Array
                                    (
                                        [0] => 3
                                    )
    
                                [project_id] => Array
                                    (
                                        [0] => 1
                                    )
    
                                [price] => Array
                                    (
                                        [0] => 900
                                    )
    
                                [message] => Array
                                    (
                                        [0] => I have a proven track record and would love to participate on your project.
                                    )
    
                                [status] => Array
                                    (
                                        [0] => 1
                                    )
    
                                [created] => Array
                                    (
                                        [0] => 2009-03-19 20:59:28
                                    )
    
                                [user] => Array
                                    (
                                        [0] => User Object
                                            (
                                                [_data:private] => ActiveRecordDataEntity Object
                                                    (
                                                        [_data:private] => Array
                                                            (
                                                                [id] => Array
                                                                    (
                                                                        [0] => 3
                                                                    )
    
                                                                [name] => Array
                                                                    (
                                                                        [0] => adam12
                                                                    )
    
                                                                [first_name] => Array
                                                                    (
                                                                        [0] => adam
                                                                    )
    
                                                                [last_name] => Array
                                                                    (
                                                                        [0] => zmijewski
                                                                    )
    
                                                                [email] => Array
                                                                    (
                                                                        [0] => adam12@gmail.com
                                                                    )
    
                                                                [status] => Array
                                                                    (
                                                                        [0] => 1
                                                                    )
    
                                                                [created] => Array
                                                                    (
                                                                        [0] => 2009-03-19 18:51:40
                                                                    )
    
                                                                [access] => Array
                                                                    (
                                                                        [0] => 1
                                                                    )
    
                                                                [site] => Array
                                                                    (
                                                                        [0] => 
                                                                    )
    
                                                            )
    
                                                    )
    
                                            )
    
                                    )
    
                            )
    
                    )
    
            )
    
    )
    1
    The system itself isn't ready for any type of release yet, but its something I've been working on taking into all the disadvantages of current like systems for the ActiveRecord and dataMapper a like. Furthermore, the entire project is object-oriented and most of the actual logic to build the sql is kept out of the ActiveRecord class and the same is true for the record properties. The benefit of this system the ability to easily manage collections of related data no matter how many levels deep putting aside the pattern. I just find the Active Record pattern to be best form of delivery. Although many of the algorithms if not all could probably be applied any pattern.
    Last edited by oddz; Mar 25, 2009 at 12:02.

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    As far as patterns go though its not a pattern, but a series of algorithm(s) that allow me to accomplish all this. That algorithm essentially consists of the following steps:

    1.) Translating the find arguments to a hierarchical node structure
    2.) Building the Sql while keeping track of what selected fields belong to which node
    3.) Recursively going through outputted sql result and building each row hierarchy

    I rather not go into the details, but that is the basis of the algorithm. Everything revolves around building a tree and storing the node to field relationship so it may be rebuilt from a repetitive sql result. So that if a primary key exists multiple times in the sql result for a given node it may only be created once and this is then done recursively for every child and sibling to obtain a nice hierarchy of related data. So that if you include Users for posts you get back a array with no repeated users based on that models unique identifier.

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Another neat feature I'll touch upon here is the ability to select,deselect and add dynamic columns/fields for a model. This could be useful if you needed to count the number of bids on a project or take it further and group_concat together all the users who have bidded on the project. I've also only selected particular columns in this example, to reduce the query expense which is possible through the deselect and select options.

    Code:
    $projects = Project::find(
    	array(
    		'include'=>'bids'
    		,'group'=>'id'
    		,'dynamic'=>array(
    			'bidders'=>'GROUP_CONCAT(Bid.user_id)'
    			,'bid_count'=>'coalesce(COUNT(Bid.project_id),0)'
    		)
    		,'select'=>'title'
    	)
    	,array(
    		'include'=>'user'
    		,'select'=>array('created','price')
    		,'require'=>false
    	)
    	,array(
    		'select'=>'name'
    		,'require'=>false
    	)
    );
    Query
    SELECT t0.`title` AS t0_title,t0.`id` AS t0_id,GROUP_CONCAT(t1.user_id) AS t0_bidders,coalesce(COUNT(t1.project_id),0) AS t0_bid_count,t1.`price` AS t1_price,t1.`created` AS t1_created,t1.`id` AS t1_id,t2.`name` AS t2_name,t2.`id` AS t2_id FROM projects AS t0 LEFT JOIN bids AS t1 ON t0.id = t1.project_id LEFT JOIN users AS t2 ON t1.user_id = t2.id GROUP BY t0.id

    [b]result[b]
    Code:
    Array
    (
        [0] => Project Object
            (
                [_data:private] => ActiveRecordDataEntity Object
                    (
                        [_data:private] => Array
                            (
                                [title] => Array
                                    (
                                        [0] => Graphic Design Needed for Project
                                    )
    
                                [id] => Array
                                    (
                                        [0] => 1
                                    )
    
                                [bidders] => Array
                                    (
                                        [0] => 1,3,4,5
                                    )
    
                                [bid_count] => Array
                                    (
                                        [0] => 4
                                    )
    
                                [bids] => Array
                                    (
                                        [0] => Array
                                            (
                                                [0] => Bid Object
                                                    (
                                                        [_data:private] => ActiveRecordDataEntity Object
                                                            (
                                                                [_data:private] => Array
                                                                    (
                                                                        [price] => Array
                                                                            (
                                                                                [0] => 300
                                                                            )
    
                                                                        [created] => Array
                                                                            (
                                                                                [0] => 2009-03-20 19:46:30
                                                                            )
    
                                                                        [id] => Array
                                                                            (
                                                                                [0] => 4
                                                                            )
    
                                                                        [user] => Array
                                                                            (
                                                                                [0] => User Object
                                                                                    (
                                                                                        [_data:private] => ActiveRecordDataEntity Object
                                                                                            (
                                                                                                [_data:private] => Array
                                                                                                    (
                                                                                                        [name] => Array
                                                                                                            (
                                                                                                                [0] => kdawg
                                                                                                            )
    
                                                                                                        [id] => Array
                                                                                                            (
                                                                                                                [0] => 1
                                                                                                            )
    
                                                                                                    )
    
                                                                                            )
    
                                                                                    )
    
                                                                            )
    
                                                                    )
    
                                                            )
    
                                                    )
    
                                            )
    
                                    )
    
                            )
    
                    )
    
            )
    
        [1] => Project Object
            (
                [_data:private] => ActiveRecordDataEntity Object
                    (
                        [_data:private] => Array
                            (
                                [title] => Array
                                    (
                                        [0] => Small On going assignment
                                    )
    
                                [id] => Array
                                    (
                                        [0] => 2
                                    )
    
                                [bidders] => Array
                                    (
                                        [0] => 
                                    )
    
                                [bid_count] => Array
                                    (
                                        [0] => 0
                                    )
    
                            )
    
                    )
    
            )
    
        [2] => Project Object
            (
                [_data:private] => ActiveRecordDataEntity Object
                    (
                        [_data:private] => Array
                            (
                                [title] => Array
                                    (
                                        [0] => Looking for web designer
                                    )
    
                                [id] => Array
                                    (
                                        [0] => 3
                                    )
    
                                [bidders] => Array
                                    (
                                        [0] => 1
                                    )
    
                                [bid_count] => Array
                                    (
                                        [0] => 1
                                    )
    
                                [bids] => Array
                                    (
                                        [0] => Array
                                            (
                                                [0] => Bid Object
                                                    (
                                                        [_data:private] => ActiveRecordDataEntity Object
                                                            (
                                                                [_data:private] => Array
                                                                    (
                                                                        [price] => Array
                                                                            (
                                                                                [0] => 300
                                                                            )
    
                                                                        [created] => Array
                                                                            (
                                                                                [0] => 2009-03-20 19:47:00
                                                                            )
    
                                                                        [id] => Array
                                                                            (
                                                                                [0] => 5
                                                                            )
    
                                                                        [user] => Array
                                                                            (
                                                                                [0] => User Object
                                                                                    (
                                                                                        [_data:private] => ActiveRecordDataEntity Object
                                                                                            (
                                                                                                [_data:private] => Array
                                                                                                    (
                                                                                                        [name] => Array
                                                                                                            (
                                                                                                                [0] => kdawg
                                                                                                            )
    
                                                                                                        [id] => Array
                                                                                                            (
                                                                                                                [0] => 1
                                                                                                            )
    
                                                                                                    )
    
                                                                                            )
    
                                                                                    )
    
                                                                            )
    
                                                                    )
    
                                                            )
    
                                                    )
    
                                            )
    
                                    )
    
                            )
    
                    )
    
            )
    
        [3] => Project Object
            (
                [_data:private] => ActiveRecordDataEntity Object
                    (
                        [_data:private] => Array
                            (
                                [title] => Array
                                    (
                                        [0] => wqdwq d qw d qw d q wd  qw d
                                    )
    
                                [id] => Array
                                    (
                                        [0] => 4
                                    )
    
                                [bidders] => Array
                                    (
                                        [0] => 
                                    )
    
                                [bid_count] => Array
                                    (
                                        [0] => 0
                                    )
    
                            )
    
                    )
    
            )
    
        [4] => Project Object
            (
                [_data:private] => ActiveRecordDataEntity Object
                    (
                        [_data:private] => Array
                            (
                                [title] => Array
                                    (
                                        [0] => wqdwq d qw d qw d q wd  qw d
                                    )
    
                                [id] => Array
                                    (
                                        [0] => 5
                                    )
    
                                [bidders] => Array
                                    (
                                        [0] => 
                                    )
    
                                [bid_count] => Array
                                    (
                                        [0] => 0
                                    )
    
                            )
    
                    )
    
            )
    
        [5] => Project Object
            (
                [_data:private] => ActiveRecordDataEntity Object
                    (
                        [_data:private] => Array
                            (
                                [title] => Array
                                    (
                                        [0] => My new project
                                    )
    
                                [id] => Array
                                    (
                                        [0] => 6
                                    )
    
                                [bidders] => Array
                                    (
                                        [0] => 
                                    )
    
                                [bid_count] => Array
                                    (
                                        [0] => 0
                                    )
    
                            )
    
                    )
    
            )
    
        [6] => Project Object
            (
                [_data:private] => ActiveRecordDataEntity Object
                    (
                        [_data:private] => Array
                            (
                                [title] => Array
                                    (
                                        [0] => My new project
                                    )
    
                                [id] => Array
                                    (
                                        [0] => 11
                                    )
    
                                [bidders] => Array
                                    (
                                        [0] => 1,3
                                    )
    
                                [bid_count] => Array
                                    (
                                        [0] => 2
                                    )
    
                                [bids] => Array
                                    (
                                        [0] => Array
                                            (
                                                [0] => Bid Object
                                                    (
                                                        [_data:private] => ActiveRecordDataEntity Object
                                                            (
                                                                [_data:private] => Array
                                                                    (
                                                                        [price] => Array
                                                                            (
                                                                                [0] => 2100
                                                                            )
    
                                                                        [created] => Array
                                                                            (
                                                                                [0] => 2009-03-25 01:54:39
                                                                            )
    
                                                                        [id] => Array
                                                                            (
                                                                                [0] => 8
                                                                            )
    
                                                                        [user] => Array
                                                                            (
                                                                                [0] => User Object
                                                                                    (
                                                                                        [_data:private] => ActiveRecordDataEntity Object
                                                                                            (
                                                                                                [_data:private] => Array
                                                                                                    (
                                                                                                        [name] => Array
                                                                                                            (
                                                                                                                [0] => kdawg
                                                                                                            )
    
                                                                                                        [id] => Array
                                                                                                            (
                                                                                                                [0] => 1
                                                                                                            )
    
                                                                                                    )
    
                                                                                            )
    
                                                                                    )
    
                                                                            )
    
                                                                    )
    
                                                            )
    
                                                    )
    
                                            )
    
                                    )
    
                            )
    
                    )
    
            )
    
    )
    1
    As you should see bid_count and bidders are added to each ActiveRecord even though they are not native to the model fields. Essentially the dynamic option allows you the ability to "overload" the model.

    Lastly, although I haven't shown any belongsToAndHasMany relationships the system fully suports those as well. In that scenario the relationship or resolution(through) table/model will essentially be added to the from statement, but not the node list. Therefore, if categories belongsToAndHasMany projects through categorizations and categorizations has not been specified as being included it will be hidden.

    Code:
    $categories = Category::find(array('include'=>'projects'),array('include'=>'user'));
    
    // Yes, ease of use through table/model is left out
    $categories[0]->project[0]->title;
    
    // also possible with include on include
    // the user name of the project in the category
    $categories[0]->projects[0]->user->name
    
    // No
    $categories[0]->categorications[0]->project[0]->title
    // categorizations essentially won't exist


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •