yii2 how to create and condition in another one

Question

I want to add filter condition with yii2 framework, and this condition have to generate the following request :

select count(*) from v_ressource
where 
(
    (
        str_to_date('27/04/2016', '%d/%m/%Y')
        between str_to_date(erDateDebut, '%d/%m/%Y')
        and str_to_date(erDateFin, '%d/%m/%Y')
    )
    and erDateFin is not null
)
or
(
    (
        str_to_date('27/04/2016', '%d/%m/%Y') 
        between str_to_date(erDateDebut, '%d/%m/%Y') 
        and now()
    )
    and erDateFin is null
);

As you can see there are 'and' conditions into the 'or' condition.

I have the following code in my ModelSearch :

    $query
        ->andFilterWhere([
            'between',
            'str_to_date(\'' . $this->dateRecherche . '\', \'%d/%m/%Y %H:%i\')',
            new Expression('str_to_date(erDateDebut, \'%d/%m/%Y %H:%i\')'),
            new Expression('str_to_date(erDateFin, \'%d/%m/%Y %H:%i\')'),
        ])
        ->andFilterWhere([
            'not', ['erDateFin' => null],
        ]);

    $query
        ->orFilterWhere([
            'between',
            'str_to_date(\'' . $this->dateRecherche . '\', \'%d/%m/%Y %H:%i\')',
            new Expression('str_to_date(erDateDebut, \'%d/%m/%Y %H:%i\')'),
            new Expression('now()'),
        ])
        ->andFilterWhere([
            'is', 'erDateFin', null,
        ]);

Both of is null and is not null conditions does not appear in the generated request and there isn't "nested" conditions (and condition in the or conditions)

Thanks for your help

Answer 1

andFilterWhere and orFilterWhere doesn't allow nesting this way since they operate on the query object - not on a condition object. You could define your query this way:

$query->where(
    ['and',
        [
            'between',
            'str_to_date(\'' . $this->dateRecherche . '\', \'%d/%m/%Y %H:%i\')',
            new Expression('str_to_date(erDateDebut, \'%d/%m/%Y %H:%i\')'),
            new Expression('str_to_date(erDateFin, \'%d/%m/%Y %H:%i\')'),
        ],
        [
            'not', ['erDateFin' => null],
        ]
    ]);

$query->orWhere(
    ['and',
        [
            'between',
            'str_to_date(\'' . $this->dateRecherche . '\', \'%d/%m/%Y %H:%i\')',
            new Expression('str_to_date(erDateDebut, \'%d/%m/%Y %H:%i\')'),
            new Expression('now()'),
        ],
        [
            'is', 'erDateFin', null,
        ]
    ]);

You can even put it all into one where method call:

$query->where(
    ['or',
        ['and',
            [
                'between',
                'str_to_date(\'' . $this->dateRecherche . '\', \'%d/%m/%Y %H:%i\')',
                new Expression('str_to_date(erDateDebut, \'%d/%m/%Y %H:%i\')'),
                new Expression('str_to_date(erDateFin, \'%d/%m/%Y %H:%i\')'),
            ],
            [
                'not', ['erDateFin' => null],
            ]
        ],
        ['and',
            [
                'between',
                'str_to_date(\'' . $this->dateRecherche . '\', \'%d/%m/%Y %H:%i\')',
                new Expression('str_to_date(erDateDebut, \'%d/%m/%Y %H:%i\')'),
                new Expression('now()'),
            ],
            [
                'is', 'erDateFin', null,
            ]
        ]
    ]);

I have used the where method, not the filterWhere methods, because you probably don't want to remove empty operands from the query. More information about filtering can be found here. where also documents the and and or operators.

As you probably already know, count can be done with

$count = $query->count();