Zend Framework Db: even more advanced database queries

2 Feb

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

2 Responses to “Zend Framework Db: even more advanced database queries”

  1. Luka February 16, 2009 at 9:28 am #

    Hi , i would like if you can help me , i’m new in zend framework and now i have one appartments page writen in procedural style. Now i will rewrite this using zend but i have problems with query like this :

    $q = “SELECT sp_id, tp_id, rooms,size,description, mslika, price, mail
    FROM specification LEFT JOIN material using (rooms_id)
    LEFT JOIN sizes USING (size_id)LEFT JOIN description USING (des_id) WHERE sp_id=$sp_id ORDER BY size, rooms”;
    i try like this but it;s doesn’t work:

    select()->setIntegrityCheck(false);
    $select->from(‘specification’,array(‘sp_id’, ‘tp_id’, ‘rooms’,’size’,’description’, ‘mslika’, ‘price’))
    ->joinLeft(‘material’,’specification.rooms_id=material.rooms_id’, array(‘rooms_id’,’rooms’))
    ->joinLeft(‘sizes’,’specification.size_id=sizes.size_id’, array(‘size_id’,’size’))
    ->joinLeft(‘description’,’specification.des_id=description.des_id’, array(‘des_id’,’description’))
    ->where(‘specification.tp_id =’.$id);
    return $this->fetchAll($select);
    }
    }

    Can you help me please?
    Thank you

  2. Simpl August 30, 2009 at 8:51 am #

    Hello I wonder how to limit query to last top 10 entries, I mean for instance if I want to get last ten news? thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: