Propel WHERE IN clause and MySQL function

Question

Recently I have started coding with Symfony and Propel 2.x, and I came across a problem with WHERE IN clause.

I want to get clients that was born in 1993 and 1988.

So I have written this Propel Query code fragment:

$query = ClientQuery::create()
    ->where('YEAR(Client.Birthdate) IN ?', [1993, 1988])
    ->find();

... and the ORM mapped these integers as DateTime object, so final query look like:

SELECT clients.id, clients.user_id, clients.first_name, clients.last_name, clients.birthdate, clients.document_id, clients.street, clients.postal_code, clients.city, clients.country 
FROM clients 
WHERE YEAR(clients.birthdate) IN ('1970-01-01','1970-01-01')

Is there way to use Propel to build query like below, without using RAW SQL query?

SELECT clients.id, clients.user_id, clients.first_name, clients.last_name, clients.birthdate, clients.document_id, clients.street, clients.postal_code, clients.city, clients.country 
    FROM clients 
    WHERE YEAR(clients.birthdate) IN (1993, 1988)

I have tried add YEAR(clients.birthdate) to SELECT with alias but also I can't get expected query.

What type of column is birthdate in the clients table? Can you show some data stored it int? — m1n0
@m1n0 this column is DATE type. Propel thinks that i'm comparing DATEs instead of INT, so it cast my values to DateTime object. — lepsus

W0rma W0rma · Accepted Answer · 2017-06-09T13:28:31

~~You could try to specify the binding type: ->where('YEAR(Client.Birthdate) IN ?', [1993, 1988], PDO::PARAM_INT)~~

Edit:

Yes, you are right. This solution will result in a PropelException, because Propel/PDO can not bind an array to int.

Alternatively you could use OR conditions:

  $years = [1993, 1988];
  // Get the key of the first element
  $firstKey = key($years);
  $query = ClientQuery::create();
  foreach ($years as $key => $year) {
      // Add _or() call for all elements except the first one
      if ($key !== $firstKey) {
          $query->_or();
      }
      // Add where condition and binding type
      $query->where('YEAR(Client.Birthdate) = ?', $year, PDO::PARAM_INT);
  }
  $query = $query->find();

I would agree that this solution does not look very nice, but it works.

Propel WHERE IN clause and MySQL function

2 Answers