Customize DQL query

Question

Hello I has the DQL query:

'SELECT t FROM VputiTripBundle:Trip t WHERE IDENTITY(t.profile) != :profile AND (t.startCity = :param0 OR t.targetCity = :param0) AND (t.startCity = :param1 OR t.targetCity = :param1) AND ...

I ant to make it like this(something like this)

'SELECT t FROM VputiTripBundle:Trip t WHERE IDENTITY(t.profile) != :profile AND (t.startCity = :param0 OR t.targetCity = :param0) OR (t.startCity = :param1 OR t.targetCity = :param1) OR ... 

Here is my query builder func:

   public function selectRelatedTrips($assoc, $profileId)
{
    $query = $this
        ->getEntityManager()
        ->createQueryBuilder()
        ->select('t')
        ->from('VputiTripBundle:Trip', 't')
        ->where('IDENTITY(t.profile) != :profile');
    $params['profile']=$profileId;
    foreach ($assoc as $k => $v) {
        $query->andWhere('t.startCity = :param' . $k . ' OR t.targetCity = :param' . $k);
        $params['param' . $k] = $v;
    }

    return $query->setParameters($params)
        ->setMaxResults(20)
        ->orderBy('t.id', 'desc')
        ->getQuery()
        ->getResult();
}

How I can do that?

Answer 1

Try this query:

$cities = array();
foreach ($assoc as $k => $v) {
    $cities[] = $v;
}

$params = array(
    'profile' => $profileId,
    'cities'  => $cities
);

$query = $this->getEntityManager()
    ->createQueryBuilder()
    ->select('t')
    ->from('VputiTripBundle:Trip', 't');

$query->where(
    $query->expr()->andX(
        $query->expr()->neq('IDENTITY(t.profile)', ':profile'),
        $query->expr()->orX(
             $query->expr()->in('t.startCity', ':cities'),
             $query->expr()->in('t.targetCity', ':cities')
        )
    )
);

return $query->setParameters($params)
    ->setMaxResults(20)
    ->orderBy('t.id', 'desc')
    ->getQuery()
    ->getResult();