Zend Framework Db: even more advanced database queries

You may have custom of using/writing advanced queries in mysql. It often require 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’);

1. 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’);

1. 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’…..))

->joinCross(‘authors’,’books.id=authors.bks_id’,array(‘col1’,’col3’…))

->where(‘where condition here’)

->group(‘group by column name here’)

->order(‘column name ASC/DESC’);