how to build nested where on zend db

Question

Hello everyone i want to build this query on zend db (zf1.9)

SELECT `p`.*, `r`.`name` AS `retailer`, `placer`.`name` AS `placer_name`, `placer`.`contact_firstname` AS `placer_firstname`, `placer`.`contact_lastname` AS `placer_lastname`, `placer`.`role_id` AS `placer_role_id`, `editor`.`name` AS `editor_name`, `editor`.`contact_firstname` AS `editor_firstname`, `editor`.`contact_lastname` AS `editor_lastname`, `editor`.`role_id` AS `editor_role_id` FROM `pos` AS `p`
     LEFT JOIN `retailers` AS `r` ON r.id = p.retailer_id
     LEFT JOIN `members` AS `placer` ON placer.id = p.placer_id
     LEFT JOIN `members` AS `editor` ON editor.id = p.editor_id WHERE (p.designer_id=116) AND ((placer.name  LIKE   '%demo%') OR (placer.contact_firstname  LIKE   '%demo%') OR (placer.contact_lastname  LIKE   '%demo%') OR (r.name  LIKE   '%demo%')) AND (`r`.`name` LIKE  '%M%') ORDER BY `p`.`id` asc

how can i do that?my problem is only

WHERE (p.designer_id=116) AND ((placer.name  LIKE   '%demo%') OR (placer.contact_firstname  LIKE   '%demo%') OR (placer.contact_lastname  LIKE   '%demo%') OR (r.name  LIKE   '%demo%')) AND (`r`.`name` LIKE  '%M%')

simplyray · Accepted Answer · 2013-09-19 08:22:52Z

2

Simply split your nested where-clause into multiple and-statements (with nested or):

first:

(p.designer_id=116)

second:

((placer.name LIKE '%demo%') OR (placer.contact_firstname LIKE '%demo%') OR (placer.contact_lastname LIKE '%demo%') OR (r.name  LIKE '%demo%'))

third:

(`r`.`name` LIKE '%M%')

In ZEND every AND is an seperate where-clause and might look like something like this (untested! only for better understanding!):

->where("p.designer_id=116");
->where("(placer.name  LIKE ?) 
        OR (placer.contact_firstname LIKE ?) 
        OR (placer.contact_lastname LIKE ?) 
        OR (r.name LIKE ?)", '%demo%', '%demo%', '%demo%', '%demo%');
->where("r.name LIKE ?", '%M%');

(Note: I've added manual linebreaks for better readability)

answered Sep 19, 2013 at 8:22

simplyray

1,2001 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sarfaraj Sipai Over a year ago

here how to use between in where for find seleted date from to to data

Ruwantha · Accepted Answer · 2014-04-05 13:08:19Z

1

I think following is better. NEST will open the bracket UNNEST will close bracket

$where = new Sql\Where();
$where->NEST//start braket
->equalTo('col','someVal')
->OR
->equalTo('col','secondVal')
->UNNEST; //close bracet

This will produce WHRE (col=someVal OR col=secondVal) Also remember after termination (putting ;) if NEST is already there it will add NUNEST automatically eg I think following is better. NEST will open the bracket UNNEST will close bracket

$where = new Sql\Where();
$where->NEST//start braket
->equalTo('col','someVal')
->OR
->equalTo('col','secondVal');
$where->OR;
$where->NEST//start braket
->equalTo('col','someVal1')
->OR
->equalTo('col','secondVal2');

This will produce WHRE (col=someVal OR col=secondVal) OR (col=someVal2 OR col=secondVal2)

edited Apr 5, 2014 at 13:08

answered Apr 5, 2014 at 13:01

Ruwantha

2,6535 gold badges31 silver badges44 bronze badges

Collectives™ on Stack Overflow

how to build nested where on zend db

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related