public function findUsersWithAllAddressesIn(Country $country) { $subQuery = $this->getEntityManager()->createQuery("\n SELECT address\n FROM MainBundle:Address address\n LEFT JOIN address.user user\n LEFT JOIN address.city city\n LEFT JOIN city.province province\n LEFT JOIN province.country country\n WHERE country.id != :country_id AND user.id = u.id\n "); $queryBuilder = $this->createQueryBuilder('u'); $queryBuilder->where($queryBuilder->expr()->not($queryBuilder->expr()->exists($subQuery->getDql())))->setParameter('country_id', $country->getId()); return $queryBuilder->getQuery()->getResult(); }
public function findRandomCitiesByCountry(Country $country, $limit = null) { $queryCityIds = $this->getEntityManager()->createQuery("\n SELECT city.id\n FROM MainBundle:City city\n LEFT JOIN city.province province\n LEFT JOIN province.country country\n WHERE country.id = :country\n ")->setParameter('country', $country->getId()); $getId = function ($value) { return $value['id']; }; $cityIds = array_map($getId, $queryCityIds->getArrayResult()); if (0 === count($cityIds)) { return array(); } shuffle($cityIds); if (null !== $limit && count($cityIds) >= $limit) { $cityIds = array_slice($cityIds, 0, $limit); } $queryCities = $this->getEntityManager()->createQuery("\n SELECT city\n FROM MainBundle:City city\n WHERE city.id IN (:cityIds)\n ")->setParameter('cityIds', $cityIds); return $queryCities->getResult(); }