Is that one queryOriginally Posted by oddz
?Code:UPDATE blogs SET status = 0 WHERE user_id = 2
Because that would be quite cool.
| SitePoint Sponsor |
Is that one query
?Code:UPDATE blogs SET status = 0 WHERE user_id = 2
Because that would be quite cool.
Good thing we have that handy ActiveRecord class, sure is saving us from writing some complex SQL
If I did a unique update to every single one of those 1000 blog posts and he managed to update them all with one query, now THAT would be cool.
currently it would look more like:
UPDATE blogs SET status = 0 WHERE id IN (1,56,47,89,0,45,23,109,876,...)
The update mechanism is based on primary keys. However, that would be a nice addition.
The only code I hate more than my own is everyone else's.
Just toying with that idea, seems fairly trivial to implement... using a Proxy so work out which fields are accessed, and which are changed...
Outputs..PHP Code:<?php
class RowProxy
{
public $__row;
public $__conditions;
public $__changes;
function __setRow(array &$row) { $this->__row = $row; $this->__conditions = $this->__changes = array(); }
function __get($name) { return $this->__conditions[$name] = $this->__row[$name]; }
function __set($name, $value) { $this->__changes[$name] = $this->__row[$name] = $value; }
}
class Collection
{
protected $rows;
function __construct($rows)
{
$this->rows = $rows;
}
function forEvery($fn)
{
$updates = array();
$proxy = new RowProxy();
foreach($this->rows as $row)
{
$proxy->__setRow($row);
$fn($proxy);
if ($proxy->__changes)
{
$k1 = json_encode($proxy->__changes);
$k2 = json_encode($proxy->__conditions);
if (!isset($updates[$k1.$k2]))
$updates[$k1.$k2] = sprintf('UPDATE rows SET %s WHERE %s', $k1, $k2);
}
}
var_dump($updates);
}
}
$collection = new Collection(array(
array('status' => 1),
array('status' => 2),
array('status' => 3),
array('status' => 1),
array('status' => 2),
array('status' => 3)
));
$collection->forEvery(
function($row)
{
switch ($row->status)
{
case 1:
$row->status = 0;
break;
case 3:
$row->status = 99;
break;
default:
$row->status *= $row->status;
}
}
);
Code:array(3) { ["{"status":0}{"status":1}"]=> string(47) "UPDATE rows SET {"status":0} WHERE {"status":1}" ["{"status":4}{"status":2}"]=> string(47) "UPDATE rows SET {"status":4} WHERE {"status":2}" ["{"status":99}{"status":3}"]=> string(48) "UPDATE rows SET {"status":99} WHERE {"status":3}" }
Upon generating the SQL all items of that update branch could be looped through and if they all share a foreign key in common perhaps update based on that. However, what if only half the blogs are saved? Then that would result in all blogs being updated that are related to the user rather than just those in memory. What would be the way around that?
PHP Code:$blogs = Blog::find(array('user_id'=>2,'id NOT IN'=->array(1,4,5,7,23,45,678,21)));
foreach($blogs as $blog) $blog->status=0;
$blogs->save(); // only update the unique blogs rather the user=>blog relationship
The only code I hate more than my own is everyone else's.
Especially if the finder uses table joins, so..
SELECT * FROM blogs INNER JOIN users ON poster = users.id WHERE u.status = 0
gets translated too..
UPDATE blogs INNER JOIN users ON poster = users.id SET blog.status = 0 WHERE u.status = 0
See my PHP above, if there are no changes (no calls to __set on the proxy) then nothing has been changed... so no update
The PHP aboves assumes iterating a whole table though, and not subset... would have to add a conditions based on what was using the find() method.
Update and Insert are linked lists which makes it possible.
The entire process is fairly complex… its by no means simple.
The only code I hate more than my own is everyone else's.
If your looking for something simple then the below isn't it. This is the update class which is reusable and can be used by itself. However, its very smart in determining whether objects are compatible with each other and deciding whether they can be inserted together or not. Specifically the add() method holds most the responsibility.
UsagePHP Code:<?php
require_once('active_record_model_config.class.php');
class ActiveRecordUpdate {
const updateTransform = 'save';
protected $record;
protected $records;
protected $sibling;
protected $data;
protected $structure;
public function __construct(ActiveRecord $pRecord=null) {
if($pRecord) $this->record = $pRecord;
$this->records = array();
$this->data = array();
$this->structure = array();
}
public function getSibling() {
return $this->sibling;
}
public function toSql() {
if(is_null($this->record)) return '';
$this->collectData();
$set = ' SET '.implode(',',$this->structure);
$config = ActiveRecordModelConfig::getModelConfig(get_class($this->record));
$keys = $this->collectPrimaryKeys();
$pk = $config->getPrimaryKey();
$where = ' WHERE '.$pk.' = '.implode(' OR '.$pk.' = ',$keys);
$limit = ' LIMIT '.count($keys);
return 'UPDATE '.$config->getTable().$set.$where.$limit;
}
public function getData() {
return $this->data;
}
public function collectData() {
$config = ActiveRecordModelConfig::getModelConfig(get_class($this->record));
if($config->hasFields()===true) {
foreach($config->getFields() as $field) {
if($this->record->hasChanged($field)===true) {
$this->collectFieldData($field);
}
}
}
}
public function collectFieldData($field) {
$config = ActiveRecordModelConfig::getModelConfig(get_class($this->record));
$allTransforms = $config->hasTransformations()?$config->getTransformations():array();
$fieldTransform = !empty($allTransforms) && array_key_exists($field,$allTransforms) && array_key_exists(self::updateTransform,$allTransforms[$field])?$allTransforms[$field][self::updateTransform]:array();
if(!empty($fieldTransform)) {
$this->structure[] = $field.' = '.$this->applyFieldTransform($this->record,$field,$fieldTransform,$allTransforms);
} else {
$this->data[] = $this->record->getProperty($field);
$this->structure[] = $field.' = ?';
}
}
public function setSibling(ActiveRecordUpdate $pSibling) {
$this->sibling = $pSibling;
}
public function hasSibling() {
return is_null($this->sibling)?false:true;
}
public function add(ActiveRecord $pRecord,$changed=false) {
if($changed===false && $this->recordHasChanged($pRecord)===false) return;
if(is_null($this->record)) {
$this->record = $pRecord;
} else if($this->isCompatible($pRecord)===true) {
$this->records[] = $pRecord;
} else {
if($this->hasSibling()) {
$this->getSibling()->add($pRecord,true);
} else {
$this->setSibling(new ActiveRecordUpdate($pRecord));
}
}
}
public function recordHasChanged(ActiveRecord $pRecord) {
$config = ActiveRecordModelConfig::getModelConfig(get_class($pRecord));
if($config->hasFields()===true) {
foreach($config->getFields() as $field) {
if($pRecord->hasChanged($field)===true) {
return true;
}
}
}
return false;
}
public function isCompatible(ActiveRecord $pRecord) {
if($this->compatibleClassName($pRecord)===false) {
return false;
}
if($this->compatibleStructure($pRecord)===false) {
return false;
}
return true;
}
public function compatibleClassName(ActiveRecord $pRecord) {
$class = get_class($this->record);
return $pRecord instanceof $class;
}
public function compatibleStructure(ActiveRecord $pRecord) {
if($this->compatibleFields($pRecord)===false) {
return false;
}
return true;
}
public function compatibleFields(ActiveRecord $pRecord) {
$config = ActiveRecordModelConfig::getModelConfig(get_class($this->record));
if($config->hasFields()===true) {
foreach($config->getFields() as $field) {
$changed = false;
if($this->record->hasChanged($field) && $pRecord->hasChanged($field)) {
$changed = true;
} else if($this->record->hasChanged($field) && !$pRecord->hasChanged($field)) {
return false;
} else if($pRecord->hasChanged($field) && !$this->record->hasChanged($field)) {
return false;
}
if($changed && !($this->record->getProperty($field)==$pRecord->getProperty($field))) {
return false;
}
}
}
return true;
}
public function collectPrimaryKeys() {
$config = ActiveRecordModelConfig::getModelConfig(get_class($this->record));
$primaryKey = $config->getPrimaryKey();
$placeholders = array();
$records = $this->records;
$records[] = $this->record;
foreach($records as $record) {
$this->data[] = $record->getProperty($primaryKey);
$placeholders[] = '?';
}
return $placeholders;
}
public function applyFieldTransform(ActiveRecord $pRecord,$pField,$pFieldTransform,$pAllTransform) {
$statement = is_array($pFieldTransform)?$pFieldTransform[0]:$pFieldTransform;
$transform = is_array($pFieldTransform)?$pFieldTransform:array();
$matches = array();
preg_match_all('/\$[1-9][0-9]*?|\{.*?\}/',$statement,$matches,PREG_OFFSET_CAPTURE);
if(array_key_exists(0,$matches) && !empty($matches[0])) {
$offset = 0;
foreach($matches[0] as $match) {
if(strcmp(substr($match[0],0,1),'$')==0) {
$index = (int) substr($match[0],1);
$index;
if(array_key_exists($index,$pFieldTransform)) {
$this->data[] = $pFieldTransform[$index];
$statement = substr_replace($statement,'?',($match[1]+$offset),strlen($match[0]));
$offset-= (strlen($match[0])-1);
}
} else {
$property = substr($match[0],1,(strlen($match[0])-2));
if(strcmp($property,'this')==0) {
$property = $pField;
}
if($pRecord->hasProperty($property)===true) {
if(strcmp($pField,$property)!=0 && array_key_exists($property,$pAllTransform) && array_key_exists(self::updateTransform,$pAllTransform[$property])) {
if($pRecord->hasChanged($property)===false) {
$statement = substr_replace($statement,$pRecord->hasChanged($property)?'?':$property,($match[1]+$offset),strlen($match[0]));
$offset-= (strlen($match[0])-1);
} else {
$nestedStatement = $this->applyFieldTransform($pRecord,$property,$pAllTransform[$property][self::updateTransform],$pAllTransform);
$statement = preg_replace('/\{'.$property.'\}/',$nestedStatement,$statement,1);
$offset+= (strlen($nestedStatement)-strlen($match[0]));
}
} else {
if($pRecord->hasChanged($property)===true) {
$this->data[] = $pRecord->getProperty($property);
}
$statement = substr_replace($statement,$pRecord->hasChanged($property)?'?':$property,($match[1]+$offset),strlen($match[0]));
$offset-= (strlen($match[0])-1);
}
}
}
}
}
return $statement;
}
public function getRecords() {
$records = $this->records;
$records[] = $this->record;
return $records;
}
}
?>
The save module amounts to a class that uses a insert and update. However, it parses the entire relational tree and determines whether to add each object to a update, insert or ignore entirely. In the case that a object hasn't been saved yet has children it will also create individual inserts that allow it grab the primary key and bind it to those children in a automated fashion.PHP Code:$user = new User(3);
$user->pwd = 'new_pass';
$blog = new Blog(4);
$blog->user_id = $user->id;
$update = new ActiveRecordUpdate();
$update->add($user);
$update->add($blog);
dump($update);
function dump($update) {
$update->toSQL(); // sql
$update->getData(); // binding array
if($update->hasSibling()===true) dump($update->getSibling());
}
So for instance if you create a new user that hasn't been saved yet and attach some blogs to that user before its been saved the system will know better then to optimize the insert. Instead it will insert that user then bind the primary keys that it has been related to each blog.
PHP Code:$user = new User();
$user->name = 'whatever';
$user->blogs[] = new Blog(array('title'=>'one'));
$user->blogs[] = new Blog(array('title'=>'two'));
$user->blogs[] = new Blog(array('title'=>'three'));
$user->blogs[] = new Blog(array('title'=>'four'));
$user->save(); // inserts user grabs primary key and applied to blogs before inserting all blogs together as well.
I wouldn't consider this "lightweight" as I've already stated though. However, implementing the algorithm to make this all function in unison required a more "complex" approach then I'm sure most would agree with. Especially considering my specific need to deal with optimization and each object tree itself. If you don't need those types of things though then sure it can pretty straight forward. Once you start talking about saving items that may be related via has one, has many, belongs to and belongs to and has many relationships it becomes much more involved. Thus leading to a much more involved solution.
The only code I hate more than my own is everyone else's.
Would be my equivalent.PHP Code:$user = new User();
$user->name = 'whatever';
$unitOfWork->registerNew($user);
$unitOfWork->registerNew(new Blog($user, 'one'));
$unitOfWork->registerNew(new Blog($user, 'two'));
$unitOfWork->registerNew(new Blog($user, 'three'));
$unitOfWork->registerNew(new Blog($user, 'four'));
$unitOfWork->commit();
I'm just spit balling ideas here, but how about a way to update the a table through another object? In this case one could change all the blogs status to 0 that are related to the user. Then you wouldn't even have to bring them into memory. All you would need is the single user.
perhaps another option could be create a empty container, set the status then apply that status to all blogs.PHP Code:$user = new User(2);
$user->change('blogs',array('status'=>0));
That would make mass updates possible with one trip to the db and without clogging up memory.PHP Code:$user = new User(2);
$imaginaryBlog = new Blog(array('status'=>0));
$user->apply($imaginaryBlog);
This could be a problem with lazy loading, but if it could be avoided and a empty collection could have data maybe a proxy collection could be used.
The user would then be able to treat that collection as its "blogs" and save those "blogs" on behalf of itself.PHP Code:$user->blogs->status=0;
$user->save();
Last edited by oddz; Jun 17, 2009 at 19:52.
The only code I hate more than my own is everyone else's.
One of the problem implementing ActiveRecord in PHP is the absence of class methods. ActiveRecord in ruby has a semantic of using Class method to perform table wide operations and class attribute to store table wide information.
Class Methods (similar to static method in PHP) - table wide operations
Instance methods - row specific operationCode:Person::find(1) Person::exists(1)
static method in PHP is really static, it's statically bind to the class on compile time, so its quite different from ruby's class method.Code:$person->updateAttribute('status', 'active') $person->find('all', array('conditions'=>"status = 'active'")); $person->exists() // true
When i develop ActiveResource class in php (consume rails resource in php) i faced similar prolbme, i added a backward compatible function "get_called_class" to get late static binding for PHP 5.1/5.2
http://github.com/speedmax/activeresource-php/tree
Code:Class Article extends ActiveResource { var $site = 'http://user:pass@myrailsapp.com'; } Article::find(1); // false Article::exists() // false $a = Article::create(array('title'=>'something', 'body'=>'article body')); $a->title = 'changed title'; $a->save(); // true $a->exists; //true
4 lines of code in each model resolves that problem for the time being. Not really a argument in my opinion.
PHP Code:public static function find() {
$args = func_get_args();
return parent::_find(__CLASS__,$args);
}
The only code I hate more than my own is everyone else's.
Hey guys, any chance you could move your ActiveRecord discussions to another thread? I'd like to get back to discussing ORMs.
apologize
I still think this is a valid idea.
Basically, change all the blogs status to 0 that are related to user with a primary key of one. This could be done without bringing any of the blogs into memory in an 1:m or m:n relationship.PHP Code:$m = new UserMapper();
$u = $m->get(1);
$m->change('blogs',$u,array('status'=>0));
The only code I hate more than my own is everyone else's.
Really crude proof of concept....
Outputs...PHP Code:<?php
class SqlObject
{
protected $parent;
protected $table;
protected $onExpression;
protected $conditions = array();
protected $set = array();
function __construct($table, SqlObject $parent = null, $onExpression = '')
{
$this->table = $table;
$this->onExpression = $onExpression;
$this->parent = $parent;
}
function addCondition($name, $value)
{
$this->conditions[$name] = $value;
}
function getConditions()
{
$r = $this->parent ? $this->parent->getConditions() : '';
foreach($this->conditions as $name => $value)
{
if ($r)
$r .= ' AND ';
$r .= $this->table.'.'.$name.' = '.$value;
}
return $r;
}
function getTables()
{
if ($this->parent)
return $this->parent->getTables().' INNER JOIN '.$this->table.' ON '.$this->onExpression;
else
return $this->table;
}
function __isset($name) { return isset($this->conditions[$name]); }
function __get($name)
{
if (isset($this->conditions[$name]))
return $this->conditions[$name];
// Would have to execute select()... to find out the value of an field.
}
function __set($name, $value)
{
$this->set[$name] = $value;
}
function select()
{
return 'SELECT '.$this->table.'.* FROM '.$this->getTables().' WHERE '.$this->getConditions();
}
function update()
{
if ($this->set)
{
$r = array();
foreach($this->set as $name => $value)
$r[] = $this->table.'.'.$name.' = '.$value;
echo 'UPDATE '.$this->getTables().' SET '.join(', ', $r).' WHERE '.$this->getConditions();
}
}
function delete()
{
echo 'DELETE FROM '.$this->getTables().' WHERE '.$this->conditions;
}
}
class User
{
static function find($id)
{
$r = new SqlObject('users');
$r->addCondition('id', $id);
return $r;
}
static function findByName($name)
{
$r = new SqlObject('users');
$r->addCondition('name', $name);
return $r;
}
}
class Blog
{
static function findByUser($user)
{
if (isset($user->id)) // if we know if the user id, dont need to table join...
{
$r = new SqlObject('blogs');
$r->addCondition('poster', $user->id);
return $r;
}
return new SqlObject('blogs', $user, 'users.id = blog.poster');
}
}
$user = User::find(23);
$blogs = Blog::findByUser($user);
$blogs->status = 2;
echo $blogs->update(), "\n";
$bob = User::findByName('bob');
$blogs = Blog::findByUser($bob);
$blogs->status = 99;
echo $blogs->update(), "\n";
Code:UPDATE blogs SET blogs.status = 2 WHERE blogs.poster = 23 UPDATE users INNER JOIN blogs ON users.id = blog.poster SET blogs.status = 99 WHERE users.name = bob
Something you can look into is creating a relational object hierarchy from a custom query. I'm not sure how this would be possible, but it would be quit cool. I haven't come across a solution that was able to achieve this yet. They allow you to run custom queries, but lack a sophisticated multiple level hydration mechanism for those types of queries.
The only code I hate more than my own is everyone else's.
Could you give an example of what you're talking about?
Being able to map a handwritten query into objects, so for instance,
If you know the table each column comes from, (PDO::ATTR_FETCH_TABLE_NAMES or something but driver specific) and what are primary keys, should be able to work what goes where.Code:SELECT * FROM users INNER JOIN blogs ON users.id = blogs.user
Hmm currently thinking how to implement m-n relationships.
One part of this is, the deletion, before you delete this, delete these other rows in the MN table first.
Just pondering on the insertion/update. Not a natural event to hook into, as relying on two mappers to have completed insertions first, so autoincrements etc are resolved.
Well the association should be defined somewhere. If the association is defined somewhere then its just a matter of deleting the items in the association table by matching the primary key value to the foreign key for that association table. The implemention could vary based on the way the association is set up though. I have it like:
PHP Code:class Project {
public static $belongsToAndHasMany = array('tags','project_tags');
}
class ProjectTag {
public static $foreignKeys = array(
'project_id'=>'Project'
,'tag_id'=>'Tag'
);
}
class tag {
public static $belongsToAndHasMany = array('projects','project_tags');
}
primary problem especially if the tables share field names. I think you would almost have to require aliases. Aliases would make it possible even in a custom query to resolve fields to the table which they belong. Otherwise there isn't any way of knowing.
If the relationship is many to many then you know to insert both items then insert the association.
The only code I hate more than my own is everyone else's.
Not sure if this is going to be much use to you, but this is what my solution amounts to:
Given a collection of records that method will look through each one and any one has a many to many object as a property send it to be processed in the below method.PHP Code:protected function saveManyToMany($record,$parent=null) {
$config = ActiveRecordModelConfig::getModelConfig(get_class($record[0]));
if($config->hasBelongsToAndHasMany()) {
foreach($config->getBelongsToAndHasMany() as $models) {
$model = is_array($models)?$models[0]:$models;
$modelClass = Inflector::classify($model);
foreach($record as $ar) {
if($ar->hasProperty($model) && !is_null($ar->getProperty($model))) {
$through = ActiveRecordModelConfig::getModelConfig(Inflector::classify($models[1]));
$this->handleManyToManySave($ar->getProperty($model),$ar,$through);
}
}
}
}
}
Might help might not…PHP Code:protected function handleManyToManySave($records,$parent,$through) {
if(count($records)==0) return;
$parentConfig = ActiveRecordModelConfig::getModelConfig(get_class($parent));
$config = ActiveRecordModelConfig::getModelConfig(get_class($records[0]));
$throughClass = $through->getClassName();
$r1 = $through->getRelatedField($parentConfig);
$r2 = $parentConfig->getRelatedField($through);
$method = 'get'.Inflector::pluralize($throughClass);
$parentCopy = $this->makeFlatCopy($parent);
foreach($records as $ar) {
if($ar->hasProperty($config->getPrimaryKey())===false) {
$ar->save();
$middleRecord = new $throughClass();
$middleRecord->setProperty(Inflector::underscore($config->getClassName()),$this->makeFlatCopy($ar));
$middleRecord->setProperty(Inflector::underscore($parentConfig->getClassName()),$parentCopy);
$this->save(array($middleRecord));
} else {
// check to make sure record isn't already associated
// if it isn't then we insert the middle record.
// otherwise we continue to save the current record ar
$find = array('limit'=>1);
$find[$r1] = $parent->getProperty($r2);
$record = $ar->$method($find);
if(count($record)==0) {
$middleRecord = new $throughClass();
$middleRecord->setProperty(Inflector::underscore($config->getClassName()),$this->makeFlatCopy($ar));
$middleRecord->setProperty(Inflector::underscore($parentConfig->getClassName()),$parentCopy);
$this->save(array($middleRecord));
}
$this->save(array($ar),array($parent));
}
}
}
The only code I hate more than my own is everyone else's.
Think the column names from the select are prefixed with the table names.
Yeah, its just how to implement it neatly.
You would think… but doesn't work if the same tables are being brought together.
The only code I hate more than my own is everyone else's.
Ah yes, can't remember when I tested if the column names came back with the aliased table name, or the physical table.
Or if PDO::FETCH_NAMES worked with table names.
Posting Permissions
Bookmarks