Zend Framework 1 Db Multiple where statements

Question

Having following variable $select where all necessary configurations are made and works fine. There is task for filtering data on same table like following:

For example table values with fields:

id object_type object_value
1-----------8------------1
1-----------5------------300
2-----------8------------0
2-----------5------------500
3-----------8------------1
3-----------5------------400
4-----------8------------1
4-----------2------------10 ...

Values must be sorted like following:

When object_type = 8 chose only object_value = 1
AND
When object_type = 5 chose only object_value >= 400 For example.

Tried using Zend_Db_selectunion() but that doesn't seem to work. Any ideas will be very helpfull. Also with joins doesn't seem to be an option.Couldn't implement case.

Update Applying query should sort data and output :

id object_type object_value
3-----------8------------1
3-----------5------------400

Explanation For example id=1 has 2 rows

object_type=8
object_value=1

AND

object_type=5
object_value=300

Query checks if id with number when object_type = 8 the object_value must be = 1 and when object_type=5 then object_value must be >= 400 so in following case id with number 1 and it's values won't be displayed. Only id with number 3 matched searched query. Hope a little explanation made thinks clear if not i will add later experimental values on concrete objects.

It's not really clear what you're trying to achieve. You want to do a filtered select (showing object_type 8 and object_type 5)? Or you have some parameter to give into your query that let you choose if you want to see object_type = 8 or object_type = 5? — Gounemond, Oct 20 '14 at 13:41
@Gounemond **Updated** How output should look. Hope thinks become more clear. — Igor, Oct 20 '14 at 13:49
possible duplicate of [Zend-framework DB: OR instead of AND operator](http://stackoverflow.com/questions/3536807/zend-framework-db-or-instead-of-and-operator) — Niko, Oct 20 '14 at 14:10
@Igor Could you then try to explain a bit better what you want to achieve? For example, why is there only one row with object_type 8 in your example result? — Niko, Oct 20 '14 at 14:18

Gounemond · Accepted Answer · 2014-10-20T14:57:16.307

1

SQL speaking, what you want is something like this:

select a.*, b.*  
from MyTable a
join MyTable b on b.id = a.id
where a.object_type = 8 and a.object_value = 1
and b.object_type = 5 and b.object_value >= 400

If you want to achieve a result like this one, you just need to get a good where condition, plus a join condition.

//your program

$select = $this->_table->select()
               ->from(array('a' => 'MyTable'),
               ->joinLeft(array('b' => 'MyTable'), 'b.id = a.id', array())

$_where = array()
$_where[] = array('object_type= ' . "8", 'object_value = '. "1");
$_where[] = array('object_type= ' . "5", 'object_value >= '. "400");

// you can do a loop after the first element if 
// you have more conditions to put in OR
$select->where($_where[0]);
$select->orWhere($_where[1]);

//...

or, if you're doing an update

 _dbTable->update($_data, $_where);

(Put in $_data whatever you're going to update) It may be nice to add an

$select->order(...);

depending on what you want to do!

edited Oct 20 '14 at 14:57

answered Oct 20 '14 at 14:03

Gounemond

353
1
9

I post in `Update` what output should be based on query. – Igor Oct 20 '14 at 14:09
So, instead of $_where and $_where2, just declare $_where = array(); $_where[] = //first condition; $_where[] = //second condition. Then ->update($_data, $_where); (on $_data you will put columns to modify and new values). Check updated answer! – Gounemond Oct 20 '14 at 14:40
check `Update` block part in my question. – Igor Oct 20 '14 at 14:43
I understood now, you want to output a couple with the same ID, that respect both conditions on type - value. Am I right? – Gounemond Oct 20 '14 at 14:48
Yes.Already found solution using joins by setting different alias. Checked your answer and is similar to what i have done. – Igor Oct 21 '14 at 07:28

Zend Framework 1 Db Multiple where statements

1 Answers1