[SOLVED] Yii2 SqlDataProvider set params conditionally

Yii2 SqlDataProvider set params conditionally

I have a huge SQL query in a SqlDataProvider. I'm using simple filtering like this (is it bad practice like this?):

!empty($_GET['Search']['A']) ? $A = $_GET['Search']['A'] : $A = "%%";

$dataProvider = new SqlDataProvider([
    'sql' => '
        SELECT * FROM ...
        WHERE
        A LIKE :A',

    'params' => [
        ':A' => $A,
    ],

It's working, but I would like to use an advanced version:

!empty($_GET['Search']['B']) ? $B = "AND B LIKE \'%" . $_GET['Search']['B'] . "%\'" : $B = "";

$dataProvider = new SqlDataProvider([
    'sql' => '
        SELECT * FROM ...
        WHERE
        A LIKE :A
        :B',

    'params' => [
        ':A' => $A,
        ':B' => $B,
    ],

But it's not working because there is always a single quote in the final SQL query at the start and at the end, and around the value a double single quote: 'AND B LIKE ''%something%'''

Is it possible to implement something like this without making the complete query conditional? (because I would like to use it multiple times like this and that would lead to a thousands of lines long command set what I would like to avoid)

Solution

First of all, yes, you should not directly access $_GET array, and instead use Yii::$app->request->get('some_param'). Second, I think parameterized query was invented for single values that need to be escaped, not for conditionaly modifying SQL statement, so there is no way I am aware to throw away mandatory quotes.

And concerning the whole idea of changing query on the fly, I would consider writing a simple function that constructs and returns an SqlDataProvider instance based on passed request parameters. This way, I think, you will retain a much needed modularity and avoid any dirty hacks.