Query builder adds condition for field type array

Question

My question is simple: can I add where the statment is, using the doctrine and query builder in an array of type field?

Inside my essence, I have the following:

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

In the representation of the object, the array looks as follows:

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

this view after serialization and insertion into the database is as follows:

 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'm trying to achieve looks something like this:

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

usually this will lead to something similar 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')

Answer 1

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

 $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(); 

Old 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 want to structure your data a little, you can divide this field by 7, as weekdays will not 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');