There is no way to make the in condition for a query into a PostgreSQL table with a service (enum) field. Values ​​indicate existing, this is no problem. I tried to do implode, but queryBuilder escapes the entire string, I tried to pass an array, but I get an array to string conversion and an Array parameter instead of 'ACTIVE', 'ANOTHER'. How correctly in queryBuilder to make a condition in for enum?

public function query() { return $this->db()->createQueryBuilder(); } public function getActiveSubscriberServices($msisdn) { $result = $this->query() ->select('id') ->from('subscriber.service') ->where('state in (?)') ->setParameter(0, [ 'ACTIVE', 'ANOTHER', ]) ->execute(); return $result->fetchAll(); }

I received a FROM subscriber.service WHERE (state in (?)) 'With params [[\ "ACTIVE \", \ "ANOTHER \"]]: \ n \ nSQLSTATE [22P02]: Invalid text representation : 7 ERROR: invalid input value for enum subscriber.service_state: \ "Array \"

I try to do it like this

 ->where($this->query()->expr()->in('state', [ 'ACTIVE', 'ANOTHER', ]))

And I get this in: IN (ACTIVE, ANOTHER) , which does not work, because the values ​​inside in must be escaped.

Now I use the code below, but this is not flexible and a bit of a crutch.

 $services = [Service::STATE_ACTIVE, Service::STATE_ACTIVATING]; $result = $this->query() ->select('*') ->from('subscriber.service') ->where($this->query()->expr()->in('state', array_map(function($item) { return '?'; }, array_keys($services)))) ->setParameters($services) ->andWhere('msisdn = ?') ->setParameter(count($services), $msisdn) ->execute(); return $result->fetchAll();

    2 answers 2

    You can use code like this:

     $services = [Service::STATE_ACTIVE, Service::STATE_ACTIVATING]; $result = $this->query() ->select('*') ->from('subscriber.service') ->where('state in (:services)') ->setParameter('services', $services, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY); ->andWhere('msisdn = ?') ->setParameter(count($services), $msisdn) ->execute(); return $result->fetchAll();
    • Specifically, this example will not work, but the condition itself worked for me. This is the best option, thanks. - ilyaplot

    At hand, there are no means to check, go to the documentation :

    // Example - $ qb-> expr () -> in ('u.id', array (1, 2, 3))
    // Make sure you’re not using something like a $ qb-> expr () -> in ('value', array ('stringvalue')) as this will cause.
    // Instead, use $ qb-> expr () -> in ('value', array ('? 1')) and bind your parameter to? 1 (see section above)

     public function getActiveSubscriberServices($msisdn) { $result = $this->query() ->select('id') ->from('subscriber.service') ->where($this->query()->expr()->in('state', [ '?1', '?2', ])) ->setParameter(1, 'ACTIVE') ->setParameter(2, 'ANOTHER') ->execute(); return $result->fetchAll(); }
    • Invalid parameter number: mixed named and positional parameters It turns out that I cannot use this method in conjunction with the named parameters? And in general, all the beauty in disappears. I just want to do -> in ('state', [1,2,3, 'ABC', false]) and get the result. For some reason, it either does not work as it should, or it is not convenient at all. - ilyaplot
    • Used in in named parameters. So far this is the least crutch of all worked. - ilyaplot
    • @ilyaplot IN in general has always worked poorly in SQL binding. As far as I understand, this is a limitation on the part of the prepared statement. - etki
    • I first encountered the doctrine, I used to work with yii 1.x and 2.x. There have never been any problems with in. I just passed the array and it was correctly transferred. I expected about the same from the doctrine, but I was disappointed. - ilyaplot