Zend Framework Sql Join Exaples

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.