CODEWITHSUNDEEP
phpselectmodelzend-framework2where-clause
GregGy
GregGy

Reputation: 11

ZF2 where expression

In mu ZF2 project I have Model using TableGateway. Inside function responsible for fetching objects based on search criteria (city, postal_code, range, type).

Generally I fetch rows of data by simple

$rowset = $this->tableGateway->select($where);

In my MySQL database I have procedure GetObjectsInCityRange(city, range) which returns all object_id in range of geocoded city coordinates

I intend to add in the where clause condition:

WHERE object_id IN (call GetObjectsInCityRange(city, range))

Is it possible with MySQL? How write correctly $where array element to make it work?

Upvotes: 1

Views: 3046

Answers (2)

kuldeep.kamboj
kuldeep.kamboj

Reputation: 2606

For MySQL it is suggested that you should use function instead of procedure as function can be used in any sql query while procedure is itself a query like statement. Ex.

call MyPrcedure();

SET var = MyFunction();

So you can call function in your query most of the time. However as per my knowledge function will return valid mysql data-type like varchar, int float etc. So query rows may not be available in your In query. Then only way to execute your query to convert your procedure logic to a subquery and pass inside IN statement. 

WHERE object_id IN (SUB_QUERY)

Upvotes: 0

Stoyan Dimov
Stoyan Dimov

Reputation: 5539

You can call a where() method in your select object and call in() on the return value or create a where statement.

E.g.

$select = new Select();
$select->from($this->tableName)->columns(array($expression));
$where = new Where();
$where->in($identifier, $valueSet);
// or
$where->addPredicate(
    new Predicate\In($identifier, $valueSet)
);
// and then
$select->where($where);

and append it to the select object.

The link below is exactly what you need I believe :) http://framework.zend.com/manual/2.1/en/modules/zend.db.sql.html#in-identifier-array-valueset-array

Hope this helps :)

Upvotes: 1

Related Questions