I try to wrtie a sql statement use for query the same table at three time by different condition
Here was my code
$queryObj = $this->modelsManager->createBuilder()
->from(array('s'=>'\Pkerp\Models\Sale'))
->columns(['s.saleId'])
->where("s.CustomerId={$this->referSale->CustomerId}")
->andWhere("s.lastrecord=1")
->andWhere("s.showdate='".$this->referSale->showdate."'")
->orderBy("s.saleId DESC");
if( $this->referSale->type == \Pkerp\Models\Sale::TYPE_ALLOWANCE ){
$reject_sale = $this->referSale;
}else{
$reject = $queryObj->andWhere("s.type=".\Pkerp\Models\Sale::TYPE_ALLOWANCE)->getQuery()->execute()->getFirst();
if( $reject ){
$reject_sale = \Pkerp\Models\Sale::findFirst($reject->saleId);
}
}
if( $this->referSale->type == \Pkerp\Models\Sale::TYPE_RECORD ){
$record_sale = $this->referSale;
}else{
$record = $queryObj->andWhere("s.type=".\Pkerp\Models\Sale::TYPE_RECORD)->getQuery()->execute()->getFirst();
if( $record ){
$record_sale = \Pkerp\Models\Sale::findFirst($record->saleId);
}
}
if( $this->referSale->type == \Pkerp\Models\Sale::TYPE_EXTEND ){
$extend_sale = $this->referSale;
}else{
$extend = $queryObj->andWhere("s.type=".\Pkerp\Models\Sale::TYPE_EXTEND)->getQuery()->execute()->getFirst();
if( $extend ){
$extend_sale = \Pkerp\Models\Sale::findFirst($extend->saleId);
}
}
If I dump sql from last one, I got this
string 'SELECT s.saleId FROM [\Pkerp\Models\Sale] AS [s] WHERE ((((s.CustomerId=8) AND (s.lastrecord=1)) AND (s.showdate='2015-11-02')) AND (s.type=0)) AND (s.type=1) ORDER BY s.saleId DESC' (length=181)
The problem is (s.type=0)) AND (s.type=1) ,that condition put in at second if expression and repeat at third if expression again
How to reset andWhere() or others when use $this->modelsManager->createbuilder() ?