Use MySQL function and column as conditional value

[copostic]

Hello,

I tried to use the clause grouping as mentioned in the doc PDO/docs/Clause/CONDITIONAL.md:

 // ... WHERE col_1 = ? AND (col_2 = ? OR col_3 = ?)
 $statement->where(
     new Clause\Grouping("AND", array(
         new Clause\Conditional("col_1", "=", "val_1"),
         new Clause\Grouping("OR", array(
             new Clause\Conditional("col_2", "=", 'val_2'),
             new Clause\Conditional("col_3", "=", 'val_2')
         )
     ));

Here's my code:

 $prizesStmt = $db->select(['id', 'value', 'text', 'isLosing', '_maxWinner', '_maxWinnerPerPeriod', '_maxWinnerPeriod', '_minDate', '_maxDate', 'totalWon'])
             ->from('game_prize_types')
             ->where(
                 new Grouping("AND",
                 [
                     new Grouping("OR",
                         new Conditional("totalWon", ">", "_maxWinner"),
                         new Conditional("_maxWinner", "IS", "NULL")
                     ),
                     new Grouping("OR",
                         new Conditional("_minDate", "<=", "NOW()"),
                         new Conditional("_minDate", "IS", "NULL")
                     ),
                     new Grouping("OR",
                         new Conditional("_maxDate", ">=", "NOW()"),
                         new Conditional("_maxDate", "IS", "NULL")
                     ),
                 ])
             );

But I have an error Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?) AND (_minDate <= ? OR _minDate IS ?) AND (_maxDate >= ? OR _maxDate IS ?)' at line 1. Should I specify with a parameter that the value is a SQL function or a column name ?

Same for an update, I would like to increment column value but it doesn't work..

Thanks in advance,

[copostic]

I also have an error Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number on this code:

$countStmt = $db->select(['count' => new Method('COUNT', 'id')])
                            ->from('game_prize_won')
                            ->where(new Conditional('creationDate', '>', date('c', strtotime('today midnight'))));

[kwhat]

Can you try with version 2.1 and let me know if you still have this problem.

[copostic]

Can you try with version 2.1 and let me know if you still have this problem.

Yep, still have both issues..

[copostic]

So, any idea of the issue @kwhat ?

[kwhat]

Sorry, I've been out of town for a few days and things have been a bit busy at work.

There are a couple of problems with the first select statement. The arguments for the Grouping do not require an array, the constructor should be veradic. The Conditional statements for the data will need either Raw or Method to work correctly due to the statement being prepared.

    $db->select([
        'id',
        'value',
        'text',
        'isLosing',
        '_maxWinner',
        '_maxWinnerPerPeriod',
        '_maxWinnerPeriod',
        '_minDate',
        '_maxDate',
        'totalWon',
    ])
    ->from('game_prize_types')
    ->where(
        new Grouping(
            'AND',
            new Grouping(
                'OR',
                new Conditional('totalWon', '>', '_maxWinner'),
                new Conditional('_maxWinner', 'IS', 'NULL')
            ),
            new Grouping(
                'OR',
                new Conditional('_minDate', '<=', new Raw('NOW()')),
                new Conditional('_minDate', 'IS', 'NULL')
            ),
            new Grouping(
                'OR',
                new Conditional('_maxDate', '>=', new Method('NOW')),
                new Conditional('_maxDate', 'IS', 'NULL')
            )
        )
    )

The second is a little more subtle. The issue is because you cannot prepare values for columns, so you need to use Raw instead of Method. Method was really only conceived for calling stored procedures but will also work for functions called within conditionals. I know, its a bit confusing and I am working on making it a bit more flexible with 3.x among other things.

$db->select(['count' => new Raw('COUNT(id)')])
        ->from('game_prize_won')
        ->where(new Conditional('creationDate', '>', date('c', strtotime('today midnight'))))

[copostic]

No problem, thanks for answering ! It indeed works better thanks :)