CODEWITHSUNDEEP
zend-frameworkzend-dbzend-db-table
Bajrang
Bajrang

Reputation: 8629

left join in zend framework

I am new in ZF and i would like to left join a table named country on country.id=firm_dtl.firm_country

$firmobj->fetchAll($firmobj->select($this)->where("firm_name like '$alpha%'")->order('firm_name'));

How can i do this. I am trying with this code :- 

$firmobj->select($this)->joinLeft(array("c"=>"country"), "c.id = firm_dtl.firm_country","c.name")->where("firm_name like '$alpha%'")->order('firm_name');

Upvotes: 1

Views: 5395

Answers (2)

Richard Ayotte
Richard Ayotte

Reputation: 5080

Here are some things that you can try to get the left join working and also to improve security.

I usually build my select statements across many lines and so I like to put it in a variable. To debug, I simply comment out the lines that I don't need.

$select = $firmobj->select()->from('country');

You'll want to setIntegrityCheck(false) because you probably won't be changing and committing the results from the query. Here's a quote from the ZF documentation about it.

The Zend_Db_Table_Select is primarily used to constrain and validate so that it may enforce the criteria for a legal SELECT query. However there may be certain cases where you require the flexibility of the Zend_Db_Table_Row component and do not require a writable or deletable row. for this specific user case, it is possible to retrieve a row or rowset by passing a FALSE value to setIntegrityCheck().

$select->setIntegrityCheck(false);

Here is where you join. You can replace field1, field2, fieldn with the fields in the firm_dtl table that you want to see in the results.

$select->joinLeft(array('c' => 'country'), 'c.id = firm_dtl.firm_country', array('field1', 'field2', 'fieldn'));

Use parameter substitution to avoid SQL injection attacks. 

$select->where('firm_name LIKE ?', "$alpha%");

And finally order the results and fetch the row set.

$select->order('firm_name');
$rowSet = $firmobj->fetchAll($select);

Upvotes: 2

M. Hryszczyk
M. Hryszczyk

Reputation: 1204

The 3rd parameter of joinLeft function should be an array of columns you want to fetch. 

$firmobj->select($this)
        ->joinLeft(array("c"=>"country"), "c.id = firm_dtl.firm_country", array("c.name"))
        ->where("firm_name like '$alpha%'")
        ->order('firm_name');

Additionally, the better way is to use where function this way:

->where("firm_name like ?", $alpha . "%")

This way is the safer solution.

Upvotes: 1

Related Questions