SitePoint Sponsor

User Tag List

Page 3 of 6 FirstFirst 123456 LastLast
Results 51 to 75 of 128
  1. #51
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by allspiritseve View Post
    Another question: I'm trying to get the minimum config use case worked out. That is:
    PHP Code:
    $mapper = new A_Orm_DataMapper('Post''posts'); 
    This will allow you to interact with a Post object in which its properties map 1:1 to fields in the posts table. No more config needed than that, unless you want more

    Can I assume a field named 'id' is the primary key for this table? Or would you guys prefer I did a DESCRIBE query every time the mapper was loaded?
    If you uses the INFORMATION_SCHEMA tables, should be able to re-use the mapper infrastructure?

    Quote Originally Posted by allspiritseve View Post
    I'm trying to assume as little as possible about these objects, but it's tough because I'm forced to check everywhere and see if mappings exist, otherwise handle the 1:1 scenario. I may end up extracting all of that code into a class that extends the main class, so the core stays light and the developer can choose whether to allow no-config mapping by which class they instantiate or extend.
    I'd put it in separate class entirely.

    PHP Code:
    $mapperFactory = new MapperFactory(array(
                                
    'User' => function()  { return new UserMapper(); },
                                
    'Post' => function() use ($mapperBuilder) { return $mapperBuilder->buildMapper('post''Post'),
    )); 

  2. #52
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ren View Post
    If you uses the INFORMATION_SCHEMA tables, should be able to re-use the mapper infrastructure?
    I think that would be the same information as DESCRIBE TABLE, no?

    Quote Originally Posted by Ren View Post
    I'd put it in separate class entirely.
    Hmm... not sure if we're talking about the same thing here. In my base mapper, I have tons of checks for whether or not mappings exist. If there aren't mappings, then it assumes 1:1 and does a different set of actions for nearly every method. That's the code I wanted to break out into a separate class.

  3. #53
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by allspiritseve View Post
    I think that would be the same information as DESCRIBE TABLE, no?
    Yeah, but can eat your own dogfood, so to speak....

    class Column
    {
    }

    class ColumnMapper
    {
    }

    class Table
    {
    }

    class TableMapper
    {
    }

    $userMapper = buildMapper(Table::find('users'), 'User');

    And INFORMATION_SCHEMA is more portable SQL, as its defined by the standard.

  4. #54
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,047
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    My configuration module is relatively simple.

    The configuration module consists of a interface called IConfig and class Config.

    IConfig consists of several getters for the table name, fields, etc. Upon creation the Config class reads a class and sets the appropriate properties for the Config object. Those properties can then be accessed using the IConfig interface.

    PHP Code:
    class User {

       public static 
    $table 'users';

    }

    $u = new Config('User');
    $u->getTable(); // 'users' 
    A benefit you have that I don't is that your configuration objects can actually be the mappers/gateways. This because you don't need to deal with the late static binding problem.

    PHP Code:
    abstract class Mapper implements IConfig {
     
        protected 
    $table;

        public function 
    getTable() {
           return 
    $this->table
        }
    }

    class 
    UserMapper extends Mapper {

       protected 
    $table 'users';

    }

    $mapper = new UserMapper();
    $mapper->getTable(); // 'users' 
    Looking at the problem in reverse using a ActiveRecord:

    PHP Code:
    abstract class ActiveRecord {

       protected static 
    $table '';

       public static function 
    find() {
          return 
    self::$table;
       }

    }
    class 
    User extends ActiveRecord {

       protected static 
    $table 'users';

    }

    echo 
    User::find(); // '' 
    Thankfully though you don't need to deal with that issue.
    The only code I hate more than my own is everyone else's.

  5. #55
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    IConfig consists of several getters for the table name, fields, etc. Upon creation the Config class reads a class and sets the appropriate properties for the Config object. Those properties can then be accessed using the IConfig interface.
    So you actually specify the fields that you want as properties in your objects? Or just retrieve that information using the table name?

    Quote Originally Posted by oddz View Post
    A benefit you have that I don't is that your configuration objects can actually be the mappers/gateways. This because you don't need to deal with the late static binding problem.
    Well, at the level I'm talking, I wouldn't even need any saved configuration, just the class name+table name when I instantiate the mapper. That way it's totally dynamic. Maybe we shouldn't be supporting that type of use case though, as AR or TDG is a better fit in those scenarios.

    Quote Originally Posted by Ren View Post
    INFORMATION_SCHEMA is more portable SQL, as its defined by the standard.
    Cool, I'll look into it.

  6. #56
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,047
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    I posted some code in the previous post just to give you a simple idea of what I'm talking about implementation wise if your interested.
    The only code I hate more than my own is everyone else's.

  7. #57
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,047
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    simple example:

    PHP Code:
    <?php
    abstract class A_Mapper {

        protected 
    $table;
        protected 
    $primaryKey;
        protected 
    $fields;

        public function 
    getTable() {
            return 
    $this->table;
        }
        
        public function 
    getPrimaryKey() {
            return 
    $this->primaryKey;
        }
        
        public function 
    getFields() {
            return 
    $this->fields;    
        }
        
        public function 
    findById($id) {
        
            
    $sql 'SELECT '.implode(',',$this->getFields()).' FROM '.$this->getTable().' WHERE '.$this->getPrimaryKey().' = ?';
            return 
    $sql;
        
        }

    }

    class 
    UserMapper extends A_Mapper {

        protected 
    $table 'users';
        protected 
    $primaryKey 'id';
        protected 
    $fields = array('id','name','pwd','email');

    }

    $mapper = new UserMapper();
    echo 
    $mapper->findById(3);
    ?>
    You can then either create a module to generate those classes or have the developer create them.
    The only code I hate more than my own is everyone else's.

  8. #58
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, cool. An equivalent example for us would be:

    PHP Code:
    class User    {

        public 
    $id;
        public 
    $name;
        public 
    $pwd;
        public 
    $email;

    }

    $mapper = new A_Orm_DataMapper($db'User''users');
    $mapper->getById(3); 
    Though actually I would prefer:
    PHP Code:
    class UserMapper extends A_Orm_DataMapper    {

        function 
    __construct($db)    {
            
    parent::__construct($db,'User','users');
            
    $this->map('id');
            
    $this->map('name');
            
    $this->map('pwd');
            
    $this->map('email');
        }


    Because then the class is there to customize as needed. They do the same thing though.

  9. #59
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by allspiritseve View Post

    Though actually I would prefer:
    PHP Code:
    class UserMapper extends A_Orm_DataMapper    {

        function 
    __construct($db)    {
            
    parent::__construct($db,'User','users');
            
    $this->map('id');
            
    $this->map('name');
            
    $this->map('pwd');
            
    $this->map('email');
        }


    Because then the class is there to customize as needed. They do the same thing though.
    PHP Code:
    class UserMapper extends A_Orm_DataMapper    {

        function 
    __construct($db$class 'User'$table 'users')          
    {
            
    parent::__construct($db,$class$table);
            
    $this->map('id');
            
    $this->map('name');
            
    $this->map('pwd');
            
    $this->map('email');
        }

    So if ever need to extended User class than can also extend UserMapper, and just override the constructor defaults.

  10. #60
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ren View Post
    So if ever need to extended User class than can also extend UserMapper, and just override the constructor defaults.
    Good idea! Hadn't thought of that. In that situation, do you think mappings should be able to be overwritten? For instance, if you subclassed that UserMapper and called $this->mapMethods('getId','setId')->toColumn('id'); it would try to set a property $id (from the original mapping) AND try to call setId($id) which I don't think is expected behaviour. I'm not sure how overwriting would work, though.

  11. #61
    SitePoint Guru bronze trophy TomB's Avatar
    Join Date
    Oct 2005
    Location
    Milton Keynes, UK
    Posts
    988
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)
    Thought I'd share my ORM.

    It works in active record. Ignore the fact that I'm using a custom DB class, you can see how it works.

    PHP Code:
    <?php
    class ActiveRecord {
        public 
    $table;
        public 
    $properties = array();
        public 
    $primaryKey 'id';
        public 
    $key;
        public 
    $db;
        public 
    $fullyFetched false;
        public static 
    $defaults;
        public 
    $relations = array();
        
        public static function 
    setDefaults($defaults) {
            
    self::$defaults $defaults;
        }
        
        public function 
    isNewRecord() {
            return 
    $this->properties[$this->primaryKey] === null;    
        }
        
        public function 
    __set($value$data) {
            if (
    method_exists($this'_set' $value)) $this->{'_set' $value}($data);
            else {
                    if (
    $data == (string) (double) $data$data = (double) $data;
                    else if (
    is_numeric($data)) $data = (integer) $data;
                    
    $this->properties[$value] = $data;
            }
        }
        
        public function 
    __toString() {
            return (string) 
    $this->properties[$this->primaryKey];
        }
        
        public function 
    __get($value) {
            if (
    array_key_exists($value$this->properties)) return $this->properties[$value];
            else if (isset(
    $this->relations[$value])) return $this->relations[$value];
            else if (
    method_exists($this'_get' $value))    return $this->relations[$value] = $this->{'_get' $value}();
            else return 
    null;
        }
        
        public function 
    __construct($id null$field null) {
            foreach (
    self::$defaults as $property => $value$this->$property $value;
            
            if (
    $this->db == null) throw new Exception('Trying to create an ActiveRecord object with no database connection set.');
                    
            if (
    $id !== null$this->load($id$field);
            
    //Make sure the PK property is always set. This ensures the __toString() method doesn't give a notice.
            
    if (!array_key_exists($this->primaryKey$this->properties)) $this->properties[$this->primaryKey] = null;
            
            
    $this->init();
        }
        
        public function 
    init() {
            
        }
        
        public function 
    unlinkRecord($recursive true) {
            
    $this->properties[$this->primaryKey] = null;
                    
            if (
    $recursive) foreach ($this as &$value$this->unlinkAll($value);
        }
        
        public function 
    unlinkAll($obj) {
            if (
    $obj !== $this && $obj !== $this->properties) {
                if (
    $obj instanceof ActiveRecord$obj->unlinkRecord(true);
                else if (
    is_array($obj)) {
                    foreach (
    $obj as &$value$this->unlinkAll($value);
                }
            }
        }
        
        public function 
    save($recursive true) {
            
    $query $this->_buildQuery();
            if (
    $this->properties[$this->primaryKey] == null$new true;
            else 
    $new false;
            
            
    $this->db->query('INSERT INTO ' $this->table ' SET ' $query ' ON DUPLICATE KEY UPDATE ' $query);
            if (
    $new$this->properties[$this->primaryKey] = $this->db->insertId;        
            if (
    $recursive) foreach ($this as &$value$this->saveAll($value);
        }
        
        private function 
    saveAll($obj) {
            if (
    $obj !== $this && $obj !== $this->properties) {
                if (
    $obj instanceof ActiveRecord$obj->save(true);
                else if (
    is_array($obj)) {
                    foreach (
    $obj as &$value$this->saveAll($value);
                }
            }
        }
        
        public function 
    delete() {
            if (
    $this->properties[$this->primaryKey] != null) {
                
    $this->db->query('DELETE FROM ' $this->table ' WHERE `' $this->primaryKey '` = %0%'$this->properties[$this->primaryKey]);
            }
        }
        
        public function 
    setForeignKey($fieldActiveRecord $obj) {
            
    $this->properties[$field] = &$obj;
        }
        
        public function 
    load($id$field null) {
            if (
    $field === null$field $this->primaryKey;
            
    $res $this->db->query('SELECT * FROM ' $this->table ' WHERE ' $field ' = %0%'$id);
            
    $this->fill($res);
        }
            
        protected function 
    fill($result) {
            if (
    $row $this->db->fetchObject($result)) {
                foreach (
    $row as $key => $value$this->properties[$key] = $value;
                
    $this->fullyFetched true;
            }
        }

        public function 
    _buildQuery() {
            
    $tmp = array();
            foreach (
    $this->properties as $field => $value) {
                if (
    $value === null$tmp[] = '`' $field '` = NULL';
                else {
                    if (
    is_object($value)) $value $value->__toString(); //PHP 5.1 support                
                    
    $tmp[] = '`' $field '` = ' $this->db->escape($value);
                }
            }
            return 
    implode(', '$tmp);
        }
    }
    ?>

    Admittedly it uses magic methods to avoid some of the problems. It allows having references in the $properties array.

    You could add a "Map" function if you wanted to explicitly define all the columns in the model.

    Some example models:


    Simple load/update/save:
    PHP Code:
    class User extends ActiveRecord {
        public 
    $table 'user';
    }

    $user = new User(123);
    $user->name 'Tom';
    $user->save(); 



    What if the user has an address in a separate table?

    It takes the idea of PHP's magic __get() and extends it. I now have magic functions for getting the related records.

    PHP Code:
    class User extends ActiveRecord {
        public 
    $table 'user';
        
        private function 
    _getAddress() {
            
    $address = new ActiveRecord();
            
    $address->setTable('Address')->load($this->id'userId');
            
    $address->setForeignKey('userId'$this);
            return 
    $address;
        }
        private function 
    _setAddress(ActiveRecord $address) {
            
    $address->setForeignKey('userId'$this);
            
    $this->relations['address'] = $address;
        }
    }


    $user = new User(123);
    $user->address->city 'London';
    $user->save(); 

    New records can be done in the same way.

    PHP Code:
    $user = new User();
    $user->name 'Tom';
    $user->address = new Address(); //$user->address->setForeignKey() is called from _setAddress
    $user->address->city 'London';
    $user->save(); 
    In this example $user->address is mapped to $user->_getAddress(); but this will only happen the first time. This way the database is accessed on the fly only when it's needed.

    One to many relationships can be handled in the same way:

    PHP Code:
    class User extends ActiveRecord {
        public 
    $table 'user';
        
        private function 
    _getPets() {
            
    $result $this->db->query('SELECT * FROM PETS WHERE userId = ' $this->db->escape($this->id));
            
    $pets = array();
            while (
    $pet $result->fetch_object('DbRecord')) {
                
    $pet->setTable('pet');
                
    $pet->setForeignKey('userId'$this);
                
    $pets[] = $pet;
            }
            return 
    $pets;
        }

    }


    $user = new User(123);
    if (
    count($user->pets) > 0) {
        echo 
    $user->pets[0]->name;



    The real magic in this system comes from the __toString() function. It allows objects to be placed in the $properties array, but when the query is built the object is automatically converted to it's primary key value meaning automatic foreign key resolution for contained objects

  12. #62
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by TomB View Post
    Thought I'd share my ORM.
    Tom,

    Thank you for posting your code. The use case we're targeting is when objects and their relations become complex and there isn't a 1:1 relation between properties <-> columns, classes <-> tables, object relations <-> foreign keys. In these situations, domain objects often vary quite a bit from the database schema, and a complex system is required to map between the two. We are using the DataMapper pattern, which in a nutshell involves a strict separation between domain logic and persistence logic. If you are familiar with Martin Fowler's book Patterns of Enterprise Application Architecture, DataMapper is another of his datasource patterns along with ActiveRecord and TableDataGateway (among others). I highly recommend the book, even if you prefer AR over DataMapper.

  13. #63
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,047
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by TomB
    PHP Code:
    class User extends ActiveRecord {
        public 
    $table 'user';
        
        private function 
    _getPets() {
            
    $result $this->db->query('SELECT * FROM PETS WHERE userId = ' $this->db->escape($this->id));
            
    $pets = array();
            while (
    $pet $result->fetch_object('DbRecord')) {
                
    $pet->setTable('pet');
                
    $pet->setForeignKey('userId'$this);
                
    $pets[] = $pet;
            }
            return 
    $pets;
        }


    I don't understand how this supports relational hierarchies when it requires they be written. One might as well be writing straight SQL. Supports doesn't mean I can extend the class and do it myself – that is worthless in my opinion.

    Support is more or less the system does it for you:

    PHP Code:
    $user = new User(2);
    $pets $user->getPets(); 
    or to grab the user and that users pets in one shot:

    PHP Code:
    $user User::find(array('include'=>'pets','id'=>2)); 
    or how about recursive relational hierarchies where the user, its pets, and those pets types are pulled in.

    PHP Code:
    $user User::find(array('include'=>'pets','id'=>2),array('include'=>'pet_types')); 
    Also, something not many systems support is aggregates. You pretty much need to write it yourself. The last thing I want to do is grab 100 users then gets the numbers of blogs each user has via lazy loading. That would 1+100 queries before everything is cached and that is if a identity map exists. I actually have a system that easily supports aggregates and its great not needing to write any SQL when these cases come up:

    PHP Code:
    User::find(array('include'=>'blogs','group'=>'id','dynamic'=>array('num_blogs'=>'COUNT(*)')));
    foreach(
    $user as $user) echo $user->getProperty('num_blogs'); 
    The same thing is true for subqueries. In the case that we only want to count the blogs that have a status of 1 yet include all users.

    PHP Code:
    $blogs Blog::find(
        
    'select'
        
    ,array(
            
    'dynamic'=>array('blog_num'=>'count(*)')
            ,
    'status'=>1
           
    ,'group'=>'user_id'
        
    )
    );

    User::find(array(
         
    'include'=>$blogs
    ),array(
        
    'association'=>array(
            
    'id'=>'t0_user_id'
         
    )
         ,
    'propertyType'=>'one'
         
    ,'rename'=>'blog_subquery'
         
    ,'require'=>false
    ));

    foreach(
    $users as $user) { if($user->blog_subquery) echo $user->blog_subquery->blog_num; } 
    Granted that is a considerable amount of cod its still much less then I would need to write if doing it with straight SQL. Besides I get the added benefit of hydration and the proper relational hierarchy.

    That is what I consider support… not this oh well… you can extend the class and do anything you want… well duh.
    The only code I hate more than my own is everyone else's.

  14. #64
    SitePoint Guru bronze trophy TomB's Avatar
    Join Date
    Oct 2005
    Location
    Milton Keynes, UK
    Posts
    988
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by oddz View Post
    I don't understand how this supports relational hierarchies when it requires they be written. One might as well be writing straight SQL. Supports doesn't mean I can extend the class and do it myself – that is worthless in my opinion.

    Support is more or less the system does it for you:

    PHP Code:
    $user = new User(2);
    $pets $user->getPets(); 
    or to grab the user and that users pets in one shot:

    PHP Code:
    $user User::find(array('include'=>'pets','id'=>2)); 
    My system does this, it just automates the getPets() part. I could add some functions to the ActiveRecord class make that generic and access them like:

    $table = 'pet';
    $fk = 'userId';

    $pets = $user->getRelatedRecords($table, $fk);

    Which gets all the related records.



    or how about recursive relational hierarchies where the user, its pets, and those pets types are pulled in.
    PHP Code:
    $user User::find(array('include'=>'pets','id'=>2),array('include'=>'pet_types')); 
    My ORM supports limitless heirachy $user->pets[0]->favouriteToys[1]->manfuactuerer->name;

    All that initially needs to be created is the $user object. Provided the relevant _get* functions exist in the models this works seamlessley and fetches the data when it's needed.

    Of course a single query with lots of joins will be faster 99% of the time (depending on how much has been fetched already).

    (I did start working on a mapper class which stores a list of tables and classes, you set up the mappings then pass it a query and it uses fetch_field() to find which table it comes from and create the relevant object and heirachy but I have yet to finish it.)


    Also, something not many systems support is aggregates. You pretty much need to write it yourself. The last thing I want to do is grab 100 users then gets the numbers of blogs each user has via lazy loading. That would 1+100 queries before everything is cached and that is if a identity map exists. I actually have a system that easily supports aggregates and its great not needing to write any SQL when these cases come up:

    PHP Code:
    User::find(array('include'=>'blogs','group'=>'id','dynamic'=>array('num_blogs'=>'COUNT(*)')));
    foreach(
    $user as $user) echo $user->getProperty('num_blogs'); 
    The same thing is true for subqueries. In the case that we only want to count the blogs that have a status of 1 yet include all users.

    PHP Code:
    $blogs Blog::find(
        
    'select'
        
    ,array(
            
    'dynamic'=>array('blog_num'=>'count(*)')
            ,
    'status'=>1
           
    ,'group'=>'user_id'
        
    )
    );

    User::find(array(
         
    'include'=>$blogs
    ),array(
        
    'association'=>array(
            
    'id'=>'t0_user_id'
         
    )
         ,
    'propertyType'=>'one'
         
    ,'rename'=>'blog_subquery'
         
    ,'require'=>false
    ));

    foreach(
    $users as $user) { if($user->blog_subquery) echo $user->blog_subquery->blog_num; } 
    Granted that is a considerable amount of cod its still much less then I would need to write if doing it with straight SQL. Besides I get the added benefit of hydration and the proper relational hierarchy.

    That is what I consider support… not this oh well… you can extend the class and do anything you want… well duh.
    [/quote]

    The problem with this is you may as well just write the SQL and have your ORM work out whether the field is dynamic or exists in the table using fetch_field(), you dont even need to declare it as dynamic then and can do any sort of calculation you want.

    Heres's how I'd have it look:

    $users = $this->db->query('SELECT *, COUNT(*) FROM users ...')->fetchAll('User');


    All you have done here is written a function which generates the SQL from an array, pretty much the same idea as "criteria objects".

    These are pointless for several reasons:
    -They can never support the full set of SQL commands unless they're rediculously complicated. In your example how do I do a union? A more advanced aggregate?
    -All the extra processing involved. Is it worth it? Why not just write the SQL and save a lot of looping through objects/arrays
    -Maintenance. Another developer can't just pick up your code and edit it without learning your new SQL syntax.
    -They get ugly, fast;

    Try doing this query in your criteria object:
    Code:
    SELECT sum(question_answered) as questions_answered, count(*) as num_questions, sum(possible_score) as possible_score, sum(score) as score
    FROM (
    	SELECT
    	survey.id as surveyid,
    	count(distinct result.id) as question_answered,
    	max(answer.score) as possible_score,
    	sum(ifnull(answered.score,0)) as score
    	FROM job
    	Inner Join survey ON survey.id = job.surveyId
    	Inner Join question ON question.surveyId = job.surveyId
    	Inner Join answer ON answer.questionId = question.id
    	Left Outer Join result ON result.answerId = answer.id AND result.jobId = job.id
    	Left Outer Join answer answered  ON answered.id = result.answerId
    	WHERE job.id = 1
    	GROUP BY job.id, survey.id, question.id
    ) sur
    GROUP by sur.surveyid

    This is for a very basic survey system. Each survey is made up of questions, which have answers. The user creates a "job" which is them filling out one survey, it then saves which questions they answered in the results table. Questions can be multiple choice.

    This query is used on a job page. It gets the total score, the total possible score, the survey title and number of questions answered out of the total number of questions.

    In my system, this goes in the Job model in a function called _getStats and works seamlessley using $job->stats->score whereever it's needed.

    I guarantee that query will be overly complex using your ::find() query builder method.

    The result of these types of queries shouldn't be handled by an ActiveRecord ORM. They don't represent a record (or set of records) in the database.

    I have still yet to find anyone who will give a real answer to the question: What's so 'bad' about having non CRUD SQL in your code? There are so many overly complex systems out there written seemingly just to avoid this.

  15. #65
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think it's a mistake to conflate query objects with orm. For me, the crucial part of an orm is the part that tries to translate relationships from the relational model to a reference based in-memory model. This is fundamentally impossible to do without cutting corners.

    Query objects usually come with orms, but you can have query objects without orm. For example, I have written plenty of applications without orm-mapping, but using query objects to build queries. The idea behind using query objects is mostly that you can create the query conditionally. The typical use case is in frontend code; Say that you have a search-form, that takes two inputs. If input 1 is filled out, you need to join table A on the query, if input 2 is filled out, you need to join table B, and if they both are filled out, you have to join both. Without a query abstraction layer, you would have to have a query for each permutation, or you would have to resort to string manipulation, which is kind of messy and error-prone.

  16. #66
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,047
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by TomB
    Code:
        public function save($recursive = true) {
            $query = $this->_buildQuery();
            if ($this->properties[$this->primaryKey] == null) $new = true;
            else $new = false;
            
            $this->db->query('INSERT INTO ' . $this->table . ' SET ' . $query . ' ON DUPLICATE KEY UPDATE ' . $query);
            if ($new) $this->properties[$this->primaryKey] = $this->db->insertId;        
            if ($recursive) foreach ($this as &$value) $this->saveAll($value);
        }
    This is wrong to me as well. If 100 objects are being saved and each object relates to the same table and contains the same fields to update then only one query needs to be generated. Saving each individually while easy is not very efficient. Its much better to take a single trip to the database rather then X x (number of objects to save).

    Seems like if I were to actual use this system on my current project I would be writing a lot of SQL and custom hydration… not something I really want to do. I much rather have the system be smart enough to handle almost all likely scenarios, not just simple ones. I don't believe there is one point in my application where I'm only selecting items from one table or not running some type of aggregate calculation.
    The only code I hate more than my own is everyone else's.

  17. #67
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool

    Quote Originally Posted by kyberfabrikken View Post
    For me, the crucial part of an orm is the part that tries to translate relationships from the relational model to a reference based in-memory model. This is fundamentally impossible to do without cutting corners.
    Exactly.

  18. #68
    SitePoint Member
    Join Date
    May 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try Doctrine an offshoot from the Symphony project, this will save you having to re-invent the wheel with the data mappers, active record classes, exception handling and focus on your business logic.

    Since its a wrapper around , a lot of the functionality is easily migrated to other applications.

    I have found that the schema generation functionality helps me keep my database and models in sync since I generate the models weekly and compare the two code bases, update the models until its in sync with the database etc.

  19. #69
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ssmusoke View Post
    Try Doctrine an offshoot from the Symphony project, this will save you having to re-invent the wheel with the data mappers, active record classes, exception handling and focus on your business logic.
    Well, for one thing Doctrine implements the ActiveRecord pattern. While AR has its uses, we prefer the DataMapper pattern, with a strict separation between business logic and persistence logic (though it sounds like Doctrine 2 is headed in that direction). Doctrine is also a large library, and may be overkill for small to medium projects. We want something lightweight that stays out of your way, but allows you to uncouple your Domain Model from the DB. Ambitious, sure, but there's a big gap between Doctrine and the 100-line AR classes that pop up around here occaisonally.

  20. #70
    SitePoint Member
    Join Date
    May 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did not find Doctrine to make my models more complciated. I created a base class which extends the Doctrine_Record, then added overrode their _set and _get methods to use Java style setters and getters (which call their methods internally) and then added a custom method to inject the data from my form into the Model class.

    After the injection, I use the Doctrine validators to validate the data (they have an excellent collection of basic validators). For more complex validation I can either extend their validate function or write my own validators which I can use for other projects (thats where I spend my time).

    While the library is large, 2.47MB, I consider it a necessary evil since it simplifies the processing of data into and out of the database. Now what I do then is make the data from the UI match what Doctrine expects.

    Also if I stay out of the framework code, I can easily upgrade.

  21. #71
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ssmusoke View Post
    I did not find Doctrine to make my models more complciated. I created a base class which extends the Doctrine_Record, then added overrode their _set and _get methods to use Java style setters and getters (which call their methods internally) and then added a custom method to inject the data from my form into the Model class.
    Well, just for comparison, with our ORM you will not need to extend a base class, which frees you up to extend other domain classes. You will then have nothing to need to override. In other words, you have complete freedom with your domain objects.

  22. #72
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    If 100 objects are being saved and each object relates to the same table and contains the same fields to update then only one query needs to be generated. Saving each individually while easy is not very efficient. Its much better to take a single trip to the database rather then X x (number of objects to save).
    Still not sure its worth the effort and code for performing optimizations like this for the majority of web apps, given the rare(?) conditions required for being able to do it.

    Also don't you have to contend with MySQL max_allowed_packet type stuff?

    I'm still scratching with the code I posted previously, experimenting with removing the commit algorithm out of the UnitOfWork into a strategy pattern, with the idea of being able to shard/partition data across databases.

  23. #73
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Ren View Post
    I'm still scratching with the code I posted previously, experimenting with removing the commit algorithm out of the UnitOfWork into a strategy pattern, with the idea of being able to shard/partition data across databases.
    Did I miss this code? Can you link to the post?

  24. #74
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,047
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Ren
    Still not sure its worth the effort and code for performing optimizations like this for the majority of web apps, given the rare(?) conditions required for being able to do it.
    That may be true, but I have created a system with a goal of minimizing the trips to the db putting aside the use of a identity map. So x objects ever need to be saved they can be as one rather than several. This also carries over to update module. If 100 objects belong to the same table, and have the same updates (field value pair) then that will be ran as a single query using a filter.

    So in the below case given a collection of 1000 blogs one query will be executed upon saving the status change rather then 1000.

    PHP Code:
    $blogs Blog::find(array('user_id'=>2));
    foreach(
    $blogs as $blog$blog['status']= 0;
    $blogs->save(); // one query 
    Collections of records can be saved and deleted as easily as it is to do so with a single record. The queries are always optimized to least amount of possible based on the single save regardless of the number of objects or depth of those objects (relational hierarchy). While it is a considerable amount of code there isn't any noticeable drop in performance. I'm sure though this would change given perhaps a huge number of objects with a very complex individual hierarchy of related objects. However, its only as complex and involved as the developer makes it.
    The only code I hate more than my own is everyone else's.

  25. #75
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by allspiritseve View Post
    Did I miss this code? Can you link to the post?
    Heres the latest ....

    Removed the odd prepared statements I had previously, and gone with standardised on PDO.

    Shifted ideas on the Mapper details, and they still require abit of code... but not to bothered with that atm.

    Has some unrelated stuff in there, like a DI container (D), for configuring the whole lot.
    Attached Files Attached Files


Tags for this Thread

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
  •