CODEWITHSUNDEEP
mysqldatabaseactiverecordyii2query-builder
Thiyaga Raj
Thiyaga Raj

Reputation: 105

In Yii2 how to perform a sql function inside andFilterWhere() method? or What is the alternative method?

 ->andFilterWhere(['=>',"invoiceDate - dueDate",$this->overDueLimit])

i need this filter to extract overDue invoices in gridview. "invoiceDate - dueDate" should be a sql function which returns a number. but Yii2 takes this as a string(as a field name). How to perform the same task in a correct way? Thanks

Upvotes: 0

Views: 1141

Answers (2)

Thiyaga Raj
Thiyaga Raj

Reputation: 105

Thanks Scais Edge.I found the solution based on your suggestion. I added the following lines to the search method and it solved my problem.

if(!empty($this->overDue))
{
$terms=MyFunc::validate($this->overDue);// sanitize and get number
$query->andFilterWhere(['>=',"DATEDIFF(CURDATE(),`dueDate`)",$terms])
      ->andFilterWhere(['=', 'status', self::$pending]);
}

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

seems the filterWhere don't allow calculated value so you can use instead andWhere() that admit literal where condition. So you can create your where condition for the query you need .. in this case you must check if the value of $this->overDueLimit is setted .. 

 if ( isset($this->overDueLimit ) ) {

 $query->andWhere("invoiceDate - dueDate  => " . $this->overDueLimit);
 }

check obviusly for a proper sanitaze of the var $this->overDueLimit

Upvotes: 0

Related Questions