Sunday, July 12, 2009

Zend Framework SQL Joins Examples

You may have custom of using advanced queries. It often requires writing complex queries if you are working on enterprise, large scale web application(s).
The use of joins can never be ignored.
Zend Framework developers have done tremendous job by providing simple method for implementing joins.
Lets look some examples of different type of joins.
Before discussing joins lets consider we have two tables, “authors” and “books”.
These are associated with author_id.

1. Inner Join
The simplest query will be
$select = $this->_db->select()
->from('books',array('col1','col2'…..))
->joinInner('authors','books.id=authors.bks_id',array('col1','col3'…))
->where('where condition here')
->order('column name ASC/DESC');

2. Left Join
$select = $this->_db->select()
->from('books',array('col1','col2'…..))
->joinLeft('authors','books.id=authors.bks_id',array('col1','col3'…))
->where('where condition here')
->group('group by column name here')
->order('column name ASC/DESC');

3. Right Join
$select = $this->_db->select()
->from('books',array('col1','col2'…..))
->joinRight('authors','books.id=authors.bks_id',array('col1','col3'…))
->where('where condition here')
->group('group by column name here')
->order('column name ASC/DESC');

4. Full Join
$select = $this->_db->select()
->from('books',array('col1','col2'…..))
->joinFull('authors','books.id=authors.bks_id',array('col1','col3'…))
->where('where condition here')
->group('group by column name here')
->order('column name ASC/DESC');

5. Cross Join
$select = $this->_db->select()
->from('books',array('col1','col2'…..))
->joinFull('authors','books.id=authors.bks_id',array('col1','col3'…))
->where('where condition here')
->group('group by column name here')
->order('column name ASC/DESC');

Once you write these queries, you can fetch a single row or multiple rows as

$result = $this->getAdapter()->fetchRow($select);
$result = $this->getAdapter()->fetchAll($select);

The first statement fetch only one row, while the second statement fetch the entire dataset.

21 comments:

  1. Hi, can you tell me how can I use Join with my own model.?

    ReplyDelete
  2. very poor examples. It would have been very useful to know what object is this, then how to enumerate the result set :(:(:(

    ReplyDelete
  3. how to use AND operator in WHERE clause in zend framework.
    ex.
    " SELECT * FROM users
    WHERE `username`='prabha' AND `password`= 'change'";

    ReplyDelete
  4. Thank for the article. It helped me a lot.
    But I think, these "selects" are not perfect. 'books', 'authors' are tables name not the model object. So when a table names is changed, the table name has to be adapted in every file. The talbe name can be got from model object. But to read table name from model object a getName() method have to be implemented to read protected $_name.

    Is there a offical way to solve this table name problem?

    Ting

    ReplyDelete
  5. Thank for the article.

    ReplyDelete
  6. How do I turn this:

    select advertisercontest.*, advertiseraccount.advertiserid, advertiseraccount.companyname from advertisercontest left join advertiseraccount on advertiseraccount.loginid = advertisercontest.loginid where advertisercontest.golive is not NULL;

    into a left join in Zend..

    ReplyDelete
  7. Hi Prabhakar,
    Use AND operator in WHERE clause:

    $select = $this->_db->select()
    ->from('books')
    ->where('where 1st condition')
    ->where('where 2nd condition')

    ReplyDelete
  8. Nice examples.This is good when we are using only one model class but how we should write the same when we are using Zend mappers?

    ReplyDelete
    Replies
    1. what are zend mappers

      Delete
  9. Really guys, using this sort of abstracted interface to a relational database is for amateurs.
    Anyone worth anything in programming should know how to construct a SQL query, it is well known we've been doing it for decades. Putting this sort of wrapper around SQL is nonsense. What happens is that you end up with multiple methods of writing queries:
    Using Zend_Db to do simple CRUD on single tables (maybe one join OMG)
    Using PDO when things get to complex for Zend_Db
    and using native queries when you have to do anything useful.

    Why bother ???

    Using a Zend_Db type interface just clouds what is basically a simple interface.

    You will end up trying to shoehorn your data access into an abstraction layer that gives no value. Compromising design and performance all over the place.

    Don't be lazy, learn the native SQL and how to interface to it.

    And if anyone says that it makes swapping the db vendor easier - there are many many other factors far more important that an abstracted Query interface.

    ReplyDelete
  10. on the joins you might need to use AS to teh fields if they are the same in both tables

    $select = $this->_db->select()
    ->from('books',array('col1','col2'…..))
    ->joinLeft('authors','books.id=authors.bks_id',array('books.msg as msg1','authors.msg as msg1'…))
    ...

    ReplyDelete
  11. i use the inner join query but i am receiving error

    Notice: Undefined property: UsersController::$_db in C:\xampp\htdocs\myzend\application\controllers\UsersController.php on line 259

    Fatal error: Call to a member function select() on a non-object in C:\xampp\htdocs\myzend\application\controllers\UsersController.php


    Please help me out , thanks.

    ReplyDelete
  12. Hello
    Any one help me hot cache the project in zend

    ReplyDelete
  13. Any one can help me how we can cache a project in zend

    ReplyDelete
  14. Resort and Spa Logo Design
    I really hate when my time is trashed by some unimportant element in the web business sphere. But these guys provided me the optimum utilization of my time by offers me what I want in a very short time frame which helped me in building so many new clients. Thank you.

    ReplyDelete
  15. Thanks! Your tutorial solved my doubts

    ReplyDelete
  16. I like your blog which is pretty awesome because I found some good information on it. Basically I am searching regarding Offshore Software Outsourcing but found this blog which is good.

    ReplyDelete
  17. Hi admin, i get data in model with db_table Zend_Db_Table_Abstract, how can i get data from two tables with common field

    ReplyDelete
  18. It contains very good information regarding Zend framework,The way you presented is very good and simple to understand.Thanks for sharing this information for our knowledge.
    Thank you.
    oracle R12 training

    ReplyDelete