Flexible finder methods

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

User avatar
inghamn
Forum Contributor
Posts: 174
Joined: Mon Apr 16, 2007 10:33 am
Location: Bloomington, IN, USA

Re: Flexible finder methods

Post by inghamn »

arborint wrote: I agree with you about not liking static finders. I like to have a real instance from a real class. And I see the trade-off you made for the simpler constructor. Yeah that makes sense. I just got the sense that there was some cruft -- which is why I wondered if you used some of that functionality much.
Curse you, arborint - you got me thinking and looking over my code in new light *shakes fist*. Especially since my latest project involves supporting multiple database servers (MySQL, Oracle, and MSSQL)

I'm still fairly sure I like having collection-style classes.
That style of programming tells me that the collections still need some sort
of base class to implement the interfaces necessary.

Code: Select all

 
// Create a collection of role objects.  The collection should be countable
// and iteratable
$roles = new RoleList(array('user_id'=>32));
echo count($roles);
foreach ($list as $role) {
    echo $role->getName();
}
 
// The collection should also be able to tell you about it's SQL
echo $roles->getSQL();
 
arborint wrote: I don't know if it hurts, but since an array of row arrays does the same thing without any additional code, it just seems unnecessary most of the time.
Suggesting that the collection classes return the results rather than being the results. This seems like it would lead to a code style like:

Code: Select all

 
$roleList = new RoleList();
$results = $roleList->find(array('user_id'=>32));
echo count($results);
foreach ($results as $role) {
    echo $role->getName();
}
 
// Seems pretty reasonable, actually - and easy to implement
// But now, how to get the SQL?
// If the results are a simple array, we can't ask them for the SQL
echo $results->getSQL(); // The $results are no longer a simple array
 
// Which means we need to ask the collection object
echo $roleList->getSQL();
 
// But if you get multiple result sets from the same collection,
// how does the collection know which result set to give you the SQL for?
echo $roleList->getSQL($results); // Do we give the results back to the collection?
echo $roleList->getSQL(); // Or can the collection only remember the last SQL it ran?
 
 
My newest project will be backed by one of three database servers (to be determined later, of course - but we have to build it now). I have to make sure the application just works on MySQL, Oracle, or MSSQL. I did another round of experimentation with Propel and Doctrine, but they didn't really match the style I've been shooting for.

Zend_DB has so far seemed like what I've been waiting for. So I've replaced my PDO instance with a Zend_DB adapter instance. Using it has been very promising: shrinking the code in my models and collection classes by half or more.

As long as I'm rewriting how my collection classes work (inluding whether to use a base ResultIterator class), I thought I'd ask how folks have worked through ideas like this
User avatar
inghamn
Forum Contributor
Posts: 174
Joined: Mon Apr 16, 2007 10:33 am
Location: Bloomington, IN, USA

Re: Flexible finder methods

Post by inghamn »

After working through this problem on my latest application, I've come to the conclusion that 1: I do, in fact, really like collection classes. and 2: Collection classes really really need to a)Iterate and b)return the SQL that created them

To me it really does make the most sense when you start actually doing stuff with your models that are written in this style.

Code: Select all

 
$users = new UserList(array('role'=>'Administrator'));
foreach ($users as $user) {
    echo $user->getUsername();
}
echo $person->getSQL();
 
For instance, you would normally load the collections in your controllers, and pass the desired collection to the view for rendering. The view can easily iterate over them and display stuff. Or, if you want to debug stuff, you can get the SQL printed out in the view.

In order to make collections act like this there's just no way around having a class act as a wrapper for the database result set. And rather than doing this wrapper stuff in each and every collection class...well, we make a base class out of it. So basically, if I want that style of code, I have to have some sort of result handler.

Zend_db however, did make the finder method of the collections much cleaner than I had been doing. I no longer have to explicitly declare each and every field in the table. I only need to declare fields that I can find on from other table (joins).

I was also able to make the model constructors optionally take an array of data for hydrating the objects. This saved a database call per row in the result set. For very large database results, my database server thanks me.

After today, here is my new collection class style, with flexible finding. I haven't implemented the sort, limit, and groupBy support yet, but it should be easy to add back into my find() method.

Code: Select all

 
class PersonList extends ZendDbResultIterator
{
    /**
     * @param array $fields
     */
    public function __construct($fields=null)
    {
        parent::__construct();
        if (is_array($fields)) {
            $this->find($fields);
        }
    }
 
    /**
     * @param array $fields
     * @param string $sort
     * @param string $limit
     * @param string $groupBy
     */
    public function find($fields=null,$sort='lastname',$limit=null,$groupBy=null)
    {
        $this->select->from('people');
 
        if (count($fields)) {
            foreach ($fields as $key=>$value) {
                $this->select->where("$key=?",$value);
            }
        }
 
        $this->populateList();
    }
 
    /**
     * Hydrates all the objects from a database result set
     *
     * @return array An array of Role objects
     */
    protected function loadResult($key)
    {
        return new Person($this->result[$key]);
    }
}
 
Now, in order to find from other tables, you certainly have to do the joins. And without wanting to declare all the normal fields we needed some way to know what the normal fields were. Zend_db was able to provide. Which means now, I only have to add in code for the extra findable fields as I'm developing the application

Code: Select all

 
class UserList extends ZendDbResultIterator
{
    private $columns;
 
    /**
     * @param array $fields
     */
    public function __construct($fields=null)
    {
        parent::__construct();
        $this->columns = $this->zend_db->describeTable('users');
 
        if (is_array($fields)) {
            $this->find($fields);
        }
    }
 
    /**
     * @param array $fields
     * @param string $sort
     * @param string $limit
     * @param string $groupBy
     */
    public function find($fields=null,$sort='username',$limit=null,$groupBy=null)
    {
        $this->select->from(array('u'=>'users'));
 
        if (count($fields)) {
            foreach ($fields as $key=>$value) {
                if (array_key_exists($key,$this->columns)) {
                    $this->select->where("u.$key=?",$value);
                }
            }
        }
 
 
        // Finding on fields from other tables required joining those tables.
        // You can add fields from other tables to $options by adding the join SQL
        // to $this->joins here
        $joins = array();
 
        if (isset($fields['firstname'])) {
            $joins['p'] = array('table'=>'people','condition'=>'u.id=p.user_id');
            $this->select->where('p.firstname=?',$fields['firstname']);
        }
        if (isset($fields['lastname'])) {
            $joins['p'] = array('table'=>'people','condition'=>'u.id=p.user_id');
            $this->select->where('p.lastname=?',$fields['lastname']);
        }
        if (isset($fields['email'])) {
            $joins['p'] = array('table'=>'people','condition'=>'u.id=p.user_id');
            $this->select->where('p.email=?',$fields['email']);
        }
        if (isset($fields['role'])) {
            $joins['ur'] = array('table'=>'user_roles','condition'=>'u.id=ur.user_id');
            $joins['r'] = array('table'=>'roles','condition'=>'ur.role_id=r.id');
            $this->select->where('r.name=?',$fields['role']);
        }
 
        foreach ($joins as $key=>$join) {
            $this->select->joinLeft(array($key=>$join['table']),$join['condition']);
        }
 
        $this->populateList();
    }
 
    /**
     * Hydrates all the objects from a database result set
     *
     * @return array An array of objects
     */
    protected function loadResult($key)
    {
        return new User($this->result[$key]);
    }
}
 
And now, instead of my old, raw PDOResultIterator, I needed that base class to handle Zend_db results. I was able to keep all my old SPL implementations, and just swap out the populate() method. Meaning the complexity of the class dropped significantly.

Code: Select all

 
abstract class ZendDbResultIterator implements ArrayAccess,SeekableIterator,Countable
{
    protected $zend_db;
    protected $select;
    protected $result = array();
 
    private $valid = false;
    private $cacheEnabled = true;
    private $cache = array();
    private $key;
 
    abstract public function find($fields=null,$sort='',$limit=null,$groupBy=null);
    abstract protected function loadResult($key);
 
    public function __construct()
    {
        $this->zend_db = Database::getConnection();
        $this->select = new Zend_Db_Select($this->zend_db);
    }
 
    /**
     * Runs the query and stores the results
     */
    protected function populateList()
    {
        $this->result = array();
        $this->result = $this->zend_db->fetchAll($this->select);
    }
 
    /**
     * @return string
     */
    public function getSQL()
    {
        return $this->select->__toString();
    }
 
    // Array Access section
    /**
     * @param int $offset
     * @return boolean
     */
    public function offsetExists($offset) {
        return array_key_exists($offset,$this->result);
    }
    /**
     * Unimplemented stub requried for interface compliance
     * @ignore
     */
    public function offsetSet($offset,$value) { } // Read-only for now
    /**
     * Unimplemented stub requried for interface compliance
     * @ignore
     */
    public function offsetUnset($offset) { } // Read-only for now
    /**
     * @param int $offset
     * @return mixed
     */
    public function offsetGet($offset)
    {
        if ($this->offsetExists($offset)) {
            return $this->loadResult($offset);
        }
        else {
            throw new OutOfBoundsException('Invalid seek position');
        }
    }
 
 
 
    // SPLIterator Section
    /**
     * Reset the pionter to the first element
     */
    public function rewind() {
        $this->key = 0;
    }
    /**
     * Advance to the next element
     */
    public function next() {
        $this->key++;
    }
    /**
     * Return the index of the current element
     * @return int
     */
    public function key() {
        return $this->key;
    }
    /**
     * @return boolean
     */
    public function valid() {
        return array_key_exists($this->key,$this->result);
    }
    /**
     * @return mixed
     */
    public function current()
    {
        return $this->loadResult($this->key);
    }
    /**
     * @param int $index
     */
    public function seek($index)
    {
        if (isset($this->result[$index])) {
            $this->key = $index;
        }
        else {
            throw new OutOfBoundsException('Invalid seek position');
        }
    }
 
    /**
     * @return Iterator
     */
    public function getIterator()
    {
        return $this;
    }
 
    // Countable Section
    /**
     * @return int
     */
    public function count()
    {
        return count($this->result);
    }
}
 

Now, I'm off to go rest, but tomorrow...time to commit all this new stuff to my scaffolding!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Flexible finder methods

Post by Eran »

The Zend_Db package already has Zend_Db_Table, which returns a Zend_Db_Table_Rowset collection object. It does some of what you described and much more, you should check it out.
User avatar
inghamn
Forum Contributor
Posts: 174
Joined: Mon Apr 16, 2007 10:33 am
Location: Bloomington, IN, USA

Re: Flexible finder methods

Post by inghamn »

I looked at using Zend_Db_Table as the base class for my collections. It has a lot going for it. But I was not able to figure out a way to have it return an array of my own custom objects. For instance, I could get it to return results from the People table, but to iterate through it I would have to manually hydrate my objects using the row that was returned.

That would have led to controller code looking like:

Code: Select all

 
$people = new PersonList(array('role'=>'Administrator'));
foreach ($people as $row) {
    $person = new Person($row);
    echo $person->getEmail();
}
echo $people->getSQL();
 
It's that one $person = new Person($row); inside of the foreach that really had me hung up. And I realize I'm probably being anal...but I want my models to do the work, not the controllers or the views. And on principal, making the controller load each person object as it got results just seems wrong.

I might have overlooked some functionality of Zend_Db_Table, but I was not able to find a way to fetch the result set as custom objects. It can fetch the results as objects, but you can't provide your own class to be used - you only get th stdClass, which isn't much help.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Flexible finder methods

Post by Eran »

You can control the class of objects returned by Zend_Db_Table finder methods in your extending class. For example:

Code: Select all

class People extends Zend_Db_Table {
     protected $_rowsetClass = 'PeopleList';
     protected $_rowClass = 'Person';
     ...
}
 
class PeopleList extends Zend_Db_Table_Rowset {
    ...
}
 
class Person extends Zend_Db_Table_Row {
    ...
}
 
...
 
$people = new People();
$peopleList = $people -> find( array(4,5,6,7) );
foreach( $peopleList as $person ) {
    echo $person -> getEmail();
}

Of course, you don't have to declare both (you can use just the custom person class if you are satisfied with Zend_Db_Table_Rowset as your collection class).
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Flexible finder methods

Post by allspiritseve »

inghamn wrote:After working through this problem on my latest application, I've come to the conclusion that 1: I do, in fact, really like collection classes. and 2: Collection classes really really need to a)Iterate and b)return the SQL that created them

For instance, you would normally load the collections in your controllers, and pass the desired collection to the view for rendering. The view can easily iterate over them and display stuff. Or, if you want to debug stuff, you can get the SQL printed out in the view.
Sorry, I must have missed something... why do result sets need to return the SQL that created them? The only scenario I can see that being useful is like you said, when debugging.. which probably should be logged by the gateway (RoleSet, etc.) when you execute the query. That way, you can get any error messages from the DB adapter, and in the case of using prepared statements, will still have access to bound variables. In fact, I currently have a wrapper for my DB adapter that I use when testing to output errors if any query fails for whatever reason. It's incredibly useful and requires to extra work on your part.
User avatar
inghamn
Forum Contributor
Posts: 174
Joined: Mon Apr 16, 2007 10:33 am
Location: Bloomington, IN, USA

Re: Flexible finder methods

Post by inghamn »

Ahh, I understand now. So, I would be need to override the ->find method of the Zend_Db_Table and providing my own implementation. Right now my ->find methods are building a Zend_Db_Select object, customized with all the joins I might want, depending on whatever random stuff I decide would be handy to be able to pass in.

Code: Select all

 
SELECT p.* FROM people p
LEFT JOIN users u ON p.id=u.person_id
LEFT JOIN user_roles ur ON u.id=ur.user_id
LEFT JOIN roles r ON ur.role_id=r.id
WHERE r.name='Administrator'
 
Should equate to

Code: Select all

 
$people = new PersonList(array('role'=>'Administrator'));
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Flexible finder methods

Post by Eran »

No need to overwrite the find() method, you can pass a Zend_Db_Table_Select to the fetchAll method which returns a rowset object.
http://framework.zend.com/manual/en/zen ... .fetch-all
User avatar
inghamn
Forum Contributor
Posts: 174
Joined: Mon Apr 16, 2007 10:33 am
Location: Bloomington, IN, USA

Re: Flexible finder methods

Post by inghamn »

allspiritseve wrote: Sorry, I must have missed something... why do result sets need to return the SQL that created them? The only scenario I can see that being useful is like you said, when debugging
Actually a common use I have for the SQL is as a subquery in another collection. For example, it's how I've implemented tagging up til now. If I have a collection of Media objects that are tagged, and I want to get only the set of tags used in that collection...

Code: Select all

 
$media = new MediaList(array('year'=>2009));
$tags = new TagList(array('mediaList'=>$media));
foreach ($tags as $relatedTag) {
    echo $tag;
}
 
I've implented this by added support in the TagList->find() method. If you pass in a MediaList, it uses the mediaList->getSQL() as a subquery which is used to do the inner join.
Here's the stripped-down version with only the relevant portions....

Code: Select all

 
class TagList extends PDOResultIterator
{
    public function find($fields=null,$sort='name',$limit=null,$groupBy=null)
    {
        if (isset($fields['mediaList']))
        {
            $media = $fields['mediaList']->getSQL();
 
            $this->joins.= ' join media_tags mt on t.id=mt.tag_id';
            $this->joins.= " join ($media)related_media on mt.media_id=related_media.id";
        }
        $this->populateList($options);
    }
}
 
User avatar
inghamn
Forum Contributor
Posts: 174
Joined: Mon Apr 16, 2007 10:33 am
Location: Bloomington, IN, USA

Re: Flexible finder methods

Post by inghamn »

pytrin wrote: No need to overwrite the find() method, you can pass a Zend_Db_Table_Select to the fetchAll method which returns a rowset object.
I'll definitely have to take another look at extending Zend_Db_Table.

Although it still leaves the ->getSQL() method, which I can't really call on a returned set. Although allspiritseve is certainly making a case against the need for that.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Flexible finder methods

Post by Eran »

The ZF has a built-in profiler that does just that and more - http://framework.zend.com/manual/en/zen ... filer.html
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Flexible finder methods

Post by allspiritseve »

inghamn wrote:Actually a common use I have for the SQL is as a subquery in another collection.
Hmm... what would you do if the query for the medialist had some other tables joined? (I'm not familiar with the way you're using JOIN-- you only need the table name, don't you?)
User avatar
inghamn
Forum Contributor
Posts: 174
Joined: Mon Apr 16, 2007 10:33 am
Location: Bloomington, IN, USA

Re: Flexible finder methods

Post by inghamn »

allspiritseve wrote: Hmm... what would you do if the query for the medialist had some other tables joined? (I'm not familiar with the way you're using JOIN-- you only need the table name, don't you?)
Actually you probably already popluated the MediaList using a TagList as a subquery. So you get all the media that are tagged ('car' and 'blue') but you still want to see all the related tags from that set. The tags that are used in the set of media that are in the tags car,blue.

Code: Select all

 
SELECT DISTINCT
  t.id AS id
FROM
  tags t
  JOIN media_tags mt ON t.id=mt.tag_id
  JOIN (
    SELECT media_id
    FROM media_tags
    WHERE mt.tag_id IN (6,15)
    GROUP BY media_id HAVING count(*)=2
   )related_media_ids ON mt.media_id = related_media_ids.media_id
 
It seems like, for tagging, you need collections that can be used as parameters for other collections. You can follow my trail of discovery for this SQL stuff here:
viewtopic.php?f=2&t=85671

Code: Select all

 
$oldMedia = new MediaList(array('year'=>2007));
$tags = new TagList(array('mediaList'=>$oldMedia));
$relatedMedia = new MediaList(array('tagList'=>$tags));
 
And, thus you can tell, ...I have gone insane.
I think I need to go to bed before I spout off even more nonsense.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Flexible finder methods

Post by allspiritseve »

inghamn wrote:Actually you probably already popluated the MediaList using a TagList as a subquery. So you get all the media that are tagged ('car' and 'blue') but you still want to see all the related tags from that set. The tags that are used in the set of media that are in the tags car,blue.
It seems like, for tagging, you need collections that can be used as parameters for other collections.
Right, I meant the medialist query would probably have joins to media_tags and tags in its query, which would be unnecessary in a query that only needed the two media ids. I guess what I'm trying to say overall is that a query that is meant to get certain data-- media items from a tag-- might not necessarily be the best SQL fragment to use when retrieving tags from media items. My preference would be a separate finder method called getTagsForMedia ($media) so the subquery could be optimized.

I will check out that thread and see if I'm not thinking about this in the right way.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Flexible finder methods

Post by allspiritseve »

Ah, I see what you're trying to do. These quotes were particularly illuminating.
inghamn wrote:Why the sub-select? Because I'm trying to avoid imploding an array of 20,000 media elements.
inghamn wrote:So my challenge has been to write a query to give me only the set of tags that appear in each and every photo of a set....a set that has been defined by a couple tags that the user has already chosen. My attempts at this query have not been too successful. The only accurate one that I've got takes 20 seconds or so to run.
That rules out my first instinct, to pass in objects for the media elements. Is there a reason why this wouldn't work?:

Code: Select all

SELECT rt.* FROM tags AS lt
INNER JOIN media_tags AS lmt ON (lt.id = lmt.tag_id)
INNER JOIN media_tags AS rmt ON (lmt.media_id = rmt.media_id)
INNER JOIN tags AS rt ON (rmt.tag_id = rt.id)
WHERE rt.id IN (5, 16)
Post Reply