use field as recursive function in where condition in yii2

Question

I want to send content of date1 to exp function in where() condition and return the result.

Note: Actually I want to compare two date, that I need to change one of dates to explode

Here is my code:

function exp($date){
            $date = explode('/', $date);
            $Y = $date[0];
            $m = $date[1];
            $d = $date[2];

            return $Y;
        }

$promise = new ActiveDataProvider([
            'query' => Post::find()
            ->where('status = "show"')
            ->andWhere(['<=', exp('date1'), 'date2'])// date1 is: 2018/02/03
            ->orderBy('id'),
            ]);

Is there any way else to do this?

do you have the raw SQL prepared ? as it is confusing what are you trying to achieve — Muhammad Omer Aslam, May 31 '18 at 14:22
I trying to send content of `date1` to `exp` function, but here in my code send `date1` as a string — Mohammad Aghayari, May 31 '18 at 20:03

score 2 · Answer 1 · answered May 31 '18 at 22:53

2

exp is a PHP function, in andWhere you prepare an SQL query that will run on the SQL server. The SQL parser on the SQL server can not execute PHP functions. You should use MySQL DATE_FORMAT function here:

$promise = new ActiveDataProvider([
      'query' => Post::find()
         ->where('status = "show"')
         ->andWhere(['<=', 'DATE_FORMAT(date1, "%Y")', 'date2'])
      ->orderBy('id'),
   ]);

Please change the name of the fields date1 and date2 to make them more informative. For ex. order_date, delivery_date etc.

If status values are often used in your code, you should replace them with a constants of the Postclass.

If date2 contains only year values, it is better to use the YEAR type.

answered May 31 '18 at 22:53

camelsWriteInCamelCase

1,655
1
10
15

I nee to send `date1` to `exp`, because `date1` is Persian date, and I need to convert it to Gregorian, but i removed the code in question. – Mohammad Aghayari Jun 01 '18 at 08:28
As you said it is impossible to use php function in `where`? do you have any idea to convert `date1` to Gregorian date? – Mohammad Aghayari Jun 01 '18 at 08:32
You can write a user defined function for convert MySQL date from Persian to Gregorian, which can take quite a long time. Or you can select rows from database without filtering by date and then convert Persian date in PHP using `IntlDateFormatter::parse` and `IntlDateFormatter::format` methods as in this [example](https://stackoverflow.com/a/24092191/7663972). Then compare dates with `DateTime::diff` method. – camelsWriteInCamelCase Jun 01 '18 at 16:09
unfortunately no, because as said, in this way is imposibble, I used with `foreach` loop for instead of `activeDataProvider`. – Mohammad Aghayari Jun 08 '18 at 16:15

use field as recursive function in where condition in yii2

1 Answers1