query builder add condition on field type array

Question

My question is simple : is it possible to add a where statment using doctrine and the query builder on a field type array ?

Inside my entity I have the following :

/**
 * @var array
 *
 * @ORM\Column(name="weekDays", type="array")
 */
private $weekDays;

In the object view the array look like this:

array(
  1 => false,
  2 => false,
  3 => true,
  4 => false,
  5 => false,
  6 => false,
  7 => false
);

an it's representation once serialize and inserted into the database look like this :

a:7:{i:1;b:0;i:2;b:0;i:3;b:1;i:4;b:0;i:5;b:0;i:6;b:0;i:7;b:0;}

What I try to achieve is something like this :

   $q = $this->em->getRepository('AcmeBundle:Notification')
        ->createQueryBuilder('n')
        ->andWhere('e.weekDays = :day')    <-- This is wrong
        ->setParameter('day', date('N'))
    ;

typically this would result to something like this in SQL

SELECT * FROM notification WHERE weekDays LIKE '%i:1;b:1%' -- if date('N') = 1 (monday)
SELECT * FROM notification WHERE weekDays LIKE '%i:7;b:1%' -- if date('N') = 7 (sunday)

and SELECT * FROM notification WHERE weekDays LIKE '%i:1;b:0%' in case I want to set ->andWhere('e.weekDays != :day')

Evident

Answer 1

Using version 2.5 of doctrine and 3.0+ of symfony it might look something like:

$qb = $this->em->getRepository('AcmeBundle:Notification')
    ->createQueryBuilder('n');
$qb->where($qb->expr()->like('n.weekDays',':weekDays'))
    ->setParameter('weekDays','%i:'.date('N').';b:1%');
$results = $qb->getQuery()->getResult();

Older methods that may also work:

$q = $this->em->getRepository('AcmeBundle:Notification')
    ->createQueryBuilder('n')
    ->andWhere("e.weekDays LIKE '%:day%'")
    ->setParameter('day', 'i:'.date('N').';b:1')
;

If you wanted to structure your data a little different you could separate that field into 7 since weekdays are not going to change:

//Entity
/**
 * @ORM\Column(type="boolean")
 */
 protected $monday;

/**
 * @ORM\Column(type="boolean")
 */
 protected $tuesday;

//And so on

Then something like:

$weekday = strtolower(date('l')); //Lowercase "L"
$q = $this->em->getRepository('AcmeBundle:Notification')
    ->createQueryBuilder('n')
    ->andWhere('n.'.$weekday. '= 1');

Greenstein

I was thinking about something more object-oriented, but I don't even know if doctrine implement something like this. I'll let others have a chance to propose something before accepting your answer but it definitively help :) – Evident

I added another possibility – Greenstein