We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Is it possible to reset part of createBuilder?

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() ?



43.9k

Hi,

are you sure that all your "if" conditions are working as expected ?