Zend_Db_Select EXISTS

Question

One part of my query is EXISTS condition:

$select->where(
  'EXISTS(' .
  'SELECT `price_property_id` FROM `property_price` ' .
    'WHERE `price_property_id` = `pu_property_id`' .
      ' AND `price_value` >= ' . $params['price_min'] .
      ' AND `price_value` <= ' . $params['price_max'] .
   ')'
);

How it writes in right way in Zend Framework?

score 2 · Answer 1 · answered Sep 10 '15 at 17:08

I create my sub queries and sub selects as new Zend_Db_Select objects. This make the code slightly cleaner as I can potentially reuse that query elsewhere, it also helps with debugging as I can echo (string)$subQuery to review just that part of the SQL.

$subQuery = new Zend_Db_Select();
$subQuery->from(array('price' => 'property_price'))
     ->where('price_property_id = pu_property_id')
     ->where('price_value >= ?', $params['price_min'])
     ->where('price_value <= ?', $params['price_max']);

$select->where('EXISTS('.$subQuery.')');

`$select->where('EXISTS('.$subQuery.')');` is failing because $subQuery is not a string (in ZF2 at least) — Andriy F., Oct 26 '18 at 07:43

score 1 · Answer 2 · edited Oct 02 '12 at 12:39

1

I believe this is what you're looking for!:

<?php

// $select is instance of Zend_Db_Select
// $db is instance of Zend_Db_Adapter

$select->where('exists (?)', new Zend_Db_Expr(
    $db->quoteInto('select * from your_table where id = ?', $id, Zend_Db::PARAM_INT)
));

?>

edited Oct 02 '12 at 12:39

Andrew Barber

39,603
20
94
123

answered Aug 03 '12 at 05:15

Robb MacLean

19
1

score 0 · Accepted Answer · answered Nov 16 '11 at 09:27

As far as I know, Zend_Db_Select doesn't have specific methods to include subqueries in the where clause. I think you cannot improve it any further, the way it's written.

You might rewrite the subquery as an OUTER JOIN, and you'd be able to leverage some more Zend_Db_Select methods, but I'm not sure about the advantages of doing it, as far as your code is working and clear to read.

Hope that helps,

Zend_Db_Select EXISTS

3 Answers3