Matt McCormick

How to easily create Models and Table Relationships in Zend Framework

April 24, 2010

When I first started using Zend Framework, I was so frustrated when trying to figure out how to get information from the database. I fought against the framework for the longest time. Instead of working with it, I would write the SQL and then simply query the database to get the data back as an array of objects.

Later on I learned at just how powerful Zend Framework can be when it comes to retrieving models. It is actually easier and more fun to do things in the “Zend” way.

This is the post I wish I had read before spending hours going about things the wrong way.

I’m using Zend Framework for a project which has videos, users (each user can have many videos) and tags (many tags to many videos). This example will show you how to setup your models correctly:

/application/models/DbTable/Videos.php

class Model_DbTable_Videos extends Zend_Db_Table_Abstract
{
	protected $_name = 'videos';	// database table name
	protected $_rowClass = 'Model_Row_Video';	// row class for extending
	protected $_dependentTables = array('Model_DbTable_VideoTag');	// videos depends on the many-to-many join table for tags

	protected $_referenceMap = array(
		'User' => array(
			'columns' => 'user_id',	// the column in the 'videos' table which is used for the join
			'refTableClass' => 'users',	// the users table name
			'refColumns' => 'id'	// the primary key of the users table
		)
	);
}

/application/models/DbTable/User.php

class Model_DbTable_Users extends Zend_Db_Table_Abstract
{
	protected $_name = 'users';
	protected $_rowClass = 'Model_Row_User';
	protected $_dependentTables = array('Model_DbTable_Videos');
}

/application/models/DbTable/Tags.php

class Model_DbTable_Tags extends Zend_Db_Table_Abstract
{
    protected $_name = 'tags';
    protected $_rowsetClass = 'Model_Rowset_Tags';
}

/application/models/DbTable/VideoTag.php (the join table for many-to-many relationship)

class Model_DbTable_VideoTag extends Zend_Db_Table_Abstract
{
    protected $_name = 'video_tag';
    protected $_referenceMap = array(
		'Video' => array(
			'columns' => 'video_id',
			'refTableClass' => 'Model_DbTable_Videos',
			'refColumns' => 'id'
   		),
   		'Tag' => array(
   			'columns' => 'tag_id',
   			'refTableClass' => 'Model_DbTable_Tags',
   			'refColumns' => 'id'
   		)
	);
}

The protected fields allow you to tell the framework the setup of the tables. You can also extend the Row on Rowset by simply setting the $_rowClass or $_rowsetClass field.

In Model_DbTable_Videos, you see I have specified the $_rowClass field. This means any row returned will be an object of Model_Row_Video. In the class Model_Row_Video, I have added extra methods for easily retrieving the user of the video and tags belonging to that video:

/application/models/Row/Video.php

class Model_Row_Video extends Zend_Db_Table_Row_Abstract
{
	private $tags = null;
	private $user = null;

	/**
	 * @return Model_Row_User
	 */
	public function getUser()
	{
		if (!$this->user) {
			$this->user = $this->findParentRow('Model_DbTable_Users');
		}

		return $this->user;
	}

	/**
	 * @return Model_Rowset_Tags
	 */
	public function getTags()
	{
		if (!$this->tags) {
			$this->tags = $this->findManyToManyRowset(
				'Model_DbTable_Tags',	// match table
				'Model_DbTable_VideoTag');	// join table
		}

		return $this->tags;
	}
}

Because I have all the relationships setup, I simply need to call findParentRow() or findManyToManyRowset() to get the Row or Rowset of a related record.

Here is an example for the Rowset:

/application/models/Rowset/Tags.php

class Model_Rowset_Tags extends Zend_Db_Table_Rowset_Abstract
{
	/**
	 * @return array the tags in an array
	 */
	public function getAsArray()
	{
		$tags = array();

		while ($this->valid()) {
			$tag = $this->current();
			$tags[] = $tag->name;  // the actual tag name
			$this->next();
		}

		$this->rewind();

		return $tags;
	}
}

So to get the tags of the video with ID 23 all I need to do now is simply:

$tblVideo = new Model_DbTable_Videos();
$video = $tblVideo->find(23)->current();  // returns Model_Row_Video
$tagsArr = $video->getTags()->getAsArray();

Working with Zend Framework becomes very easy and pleasurable once you follow their setup.

Refer to the Zend_Db section of the manual for more information about working with the database.

23 Responses to “How to easily create Models and Table Relationships in Zend Framework”

  1. Steven Wei April 24, 2010

    You might want to look into using a PHP ORM (object-relational mapper) like Doctrine:

    http://www.doctrine-project.org/

  2. Matt April 24, 2010

    Thanks Steven. I had previously come across Doctrine but didn’t really look into it. Perhaps I will take another look.

  3. Ben April 25, 2010

    Thanks for this great post! It’s always nice to see that other people are doing the things the same way! Thanks man.

  4. Ivan August 4, 2010

    Hallo. I need to show all videos with user_name:
    video_id, video_name, username
    video_id, video_name, username
    video_id, video_name, username
    …..
    video_id, video_name, username

    How?

  5. Matt August 7, 2010

    Hi Ivan,

    You do not give enough information to help you. I would suggest asking your question on http://stackoverflow.com – a Q & A site for programming.

  6. Vijay September 15, 2010

    Hey Matt,

    Look awesome… and I was looking for something like this…. I thought Zend never implement ORM… and in the current project I am mixing doctrine with Zend to get the power of ORM… Do you think Zend_Db_Table classes can serve like or much better than Doctrine…..?

  7. Matt September 15, 2010

    Hi Vijay, I haven’t used Doctrine so I can’t comment if it is better to use it or not.

  8. Midhun Girish September 16, 2010

    Hey Matt, tht was a good one……. Simple and clear….. Do put up more tuts….. Congrats on this one….

  9. Cristian November 18, 2010

    Hey Matt, thank you for sharing this. It’s the best post I’ve found on how to use table relations in Zend.

  10. Matt November 18, 2010

    Thank you for your kind comments Cristian.

  11. Mike January 19, 2011

    Thanks, this helped a lot. Very clear.
    I have a slight twist to this and wondered if there’s any way to solve my problem.

    If you did not use the join table but stored the video ids as a csv column per user row, is there any way to make the correlation.

    In example: User with ‘id’ of 1, has the ‘videoIds’ column set to ’1,2,3′ which directly relate to three separate videos in the video table.

    When calling $users->fetchAll()->getVideos(), I would like the method to return an array of 3 videos.

    In mysql, this could be done using FIND_IN_SET(), does Zend_Db_Table have anything similar?

    I will eagerly await a reply, and thanks again :)

  12. Matt January 22, 2011

    Hi Mike,

    I’m glad you found it useful.

    For your question, you can add a getVideos() method to the Model_Row_User row class. In the method you can do something like the following (you will need to test yourself):

    public function getVideos() {
    $result = array();
    if ($videos) {
    $tblVideos = new Model_DbTable_Videos();
    $result = $tblVideos->fetchAll('id IN (' . $this->videoIds . ')')->toArray();
    }

    return $result;
    }

    Then you can call:

    $user->getVideos();

    which will return an array of their videos.

  13. Rylie April 25, 2011

    Hi, Matt. Thanks for sharing this tutorial. Very helpful. Was wondering if you could also make available for downloading an sql file that defines the database tables you used to test your code above.

  14. Matt April 30, 2011

    Thanks for the comments Rylie. This post was based off an existing database I was using for a project so the database contained many more tables not relevant to this post. I didn’t create a database specifically for this example. If you create one, you can send it to me and I can attach it to the post.

  15. David Weinraub May 24, 2011

    Hi Matt,

    Excellent example an tutorial. Very clear, especially for getting a single entity (video) and related entities
    (user, tags, etc)

    Several questions about issues with which I always seem to struggle.

    1. Given a single video $video, the call to $video->getTags() performs anothr query, riht? So this would not be optimal if I were looping through videos, right? I imagine I would want a join in there. But where should that join query reside?

    2. Selecting multiple videos: If I want to get all videos (or videos with tag ‘xxx’or videos with date later than ‘xxxx’), then where should that query go?

    3. Pagination: If some query produces many videos, I imagine I would want to paginate those results. Where would pagination – getting a count and limiting the number of results – appear under this approach.

    Again, many thanks for a very clear tutorial. Cheers!

  16. Matt May 24, 2011

    Hi David, thanks for your comments. Per your questions,

    1) Yes, if you want to get many videos with their tags this would result in many calls. If you want to get a list of videos with all tags I would create a specific method for it in the Model_DbTable_Videos class.

    2) Same thing, I would create a separate method in Model_DbTable_Videos that could be called. Look up Zend_Db_Table_Select in the Zend documentation if you are not familiar with how to create SQL queries using Zend.

    3) Zend also offers a Paginator class for this exact approach. Check out the documentation for it at http://framework.zend.com/manual/en/zend.paginator.html

  17. David Weinraub May 24, 2011

    Hi Matt,

    Thanks for the quick answers. A few followups if I may.

    1 + 2: To get many videos, a custom method that includes the join. So, in that method, the custom query would not use the referenceMap or the dependentTables. just a custom query built up using standard Zend_Db_Select, right?

    3. Sure, I know about Zend_Paginator and the view helper, etc. But how would the method signatures in your Videos models be affected? Something like $tblVideos->getAll($page = 1, $itemsPerPage = 10)?

    I have done all this before, actually using Doctrine and Zend_Paginate. It all worked, but I felt like I was hacking it in. I guess the core question becomes: Should these model methods accept pagination params as above and then internally use Z_P and related adapters to modify the queries?

    Many thanks for your excellent article and for your advice.

  18. Matt May 27, 2011

    David, unfortunately, I’m unable to provide extended advice on my blog especially since your questions are getting away from the point of the article. If you still need assistance feel free to contact me to inquire about hiring me for consultation advice.

  19. Jan October 3, 2011

    Where should I put this one?

    $tblVideo = new Model_DbTable_Videos();
    $video = $tblVideo->find(23)->current(); // returns Model_Row_Video
    $tagsArr = $video->getTags()->getAsArray();

    And how does like the ShowMeUserOfVideo Function?

    many thanks in advance

  20. Jan October 6, 2011

    can you provide the MySQL dump?

  21. Matt October 13, 2011

    Hi Jan,

    The code you referred to can go in your controller. I’m not sure what you mean by the ShowMeUserOfVideo function. There is no SQL dump for this code as I based this tutorial on a site I was developing. You should be able to figure out the tables and column information based on the Model classes. It’s left as an exercise for the reader :)


Leave a Reply