Casting column value? Using RAW withing where clause with multiple conditions on same column

[marcusdiy]

Information

• Version of Medoo: latest
• Type of Database (MySQL, MSSQL, SQLite...): MySQL
• System (Liunx\Windows\Mac): Linux

Describe the Problem I want to select repeating events from table. I need to cast the dates to months to check which yearly events are to include too. Fatal error: Uncaught TypeError: PDO::quote(): Argument #1 ($string) must be of type string, Raw given in /var/www

Detail Code The detail code you are using causes the problem.

$db->select('calendar', '*', [
    'OR' => array(
        'AND' => array(
            'start_date{>=}' => $params['from_date'],
            'start_date{<=}' => $params['to_date']
        ),
        'AND' => array(
            'repeat_interval' => 'every_year',
            'start_date' => [
                Database::raw('month(start_date) >= month(:from)', [':from' => $params['from_date']]),
                Database::raw('month(start_date) <= month(:to)', [':to' => $params['to_date']])
            ]
        )
    )
]);

Not working either

           'repeat_interval' => 'every_year',
           Database::raw('month(start_date) >= month(:from)', [':from' => $params['from_date']]),
           Database::raw('month(start_date) <= month(:to)', [':to' => $params['to_date']])

           'repeat_interval' => 'every_year',
           'month(start_date) >=' => Database::raw('month(:from)', [':from' => $params['from_date']]),
           'month(start_date) <=' => Database::raw('month(:to)', [':to' => $params['from_date']])

           'repeat_interval' => 'every_year',
           Database::raw('month(start_date) >=') => Database::raw('month(:from)', [':from' => $params['from_date']]),
           Database::raw('month(start_date) <=') => Database::raw('month(:to)', [':to' => $params['from_date']])

Expected output

SELECT * FROM `calendar` WHERE (
  start_date >= '2021-09-27'
  AND start_date <= '2021-11-08'
) OR (
  `repeat_interval` = 1 
  AND month(start_date) >= month('2021-09-27') 
  AND month(start_date) <= month('2021-11-08')
)

[marcusdiy]

Solvable by a RAW Where query. But i was wondering if there were any way to do witouth it

Database::raw('WHERE (
    `start_date` >= :from 
    AND `start_date` <= :to
) OR (
    repeat_interval = "every_year"
    AND DayOfYear(`start_date`) BETWEEN DayOfYear(:from) AND DayOfYear(:to)
)', [
    ':from' => $params['from_date'],
    ':to' => $params['to_date']
]);

[logicalor]

The SELECT array is just a plain old associative PHP array so any elements with identical keys (i.e. your AND elements) will be overridden by the last declared element.

You need to make your AND elements uniquely keyed. In your code, add comments to the AND keys

$db->select('calendar', '*', [
    'OR' => array(
        'AND #clause 1' => array(
            'start_date{>=}' => $params['from_date'],
            'start_date{<=}' => $params['to_date']
        ),
        'AND #clause 2' => array(
            'repeat_interval' => 'every_year',
            'start_date' => [
                Database::raw('month(start_date) >= month(:from)', [':from' => $params['from_date']]),
                Database::raw('month(start_date) <= month(:to)', [':to' => $params['to_date']])
            ]
        )
    )
]);

This should solve the problem.

[marcusdiy]

cant make it to work with this part AND DayOfYear(start_date) BETWEEN DayOfYear(:from) AND DayOfYear(:to) it doesnt even work if i try to put in the raw query and try to pass the arguments instead