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