iCheater Retaehci iCheater Retaehci - 7 months ago 16
SQL Question

Symony QueryBuilder

I am trying to use createQueryBuilder, to make valid query.
I want to get all Organizers, that have partOfSearchText and have some city_id

$qb = $em->createQueryBuilder();
$repository = $this->getDoctrine()->getRepository('AppBundle:Organizers');
$query = $repository->createQueryBuilder('o')
->where('o.name LIKE :partOfSearchText')
->andWhere('o.cities = :city_id')
->setParameter('partOfSearchText', '%'.$partOfSearchText.'%')
->setParameter('city_id', $cityId)
->getQuery();


But my query is wrong, because there is no o.cities in Organizer table. Table contains only id and name.
enter image description here

Two entiries city and organizer have 3 tables in db:
Organizer
City
organizers_cities

In fact i need just to add in createQueryBuilder something like this:

SELECT Organizer . *
FROM Organizer
LEFT JOIN organizers_cities ON Organizer.id = organizers_cities.organizer_id
WHERE organizers_cities.city_id =25


i can solve it using:

$cityId = $em->getRepository('AppBundle:City')->findOneBy(array('link' => $citySlug))->getId();
$city = $em->getRepository('AppBundle:City')->findOneBy(array(
'link' => $citySlug,
));
$organizers = $city->getOrganizers();
foreach ($organizers as $organizer){
$pos = strpos($organizer, $partOfSearchText);
if ($pos === false) {
//do some things
}
}


, but it's ugly like hell

So,
How to achieve valid query?

City.php

class City
{
/**
* @ORM\Column(type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
protected $id;
/**
* @ORM\Column(type="string", length=140)
*/
protected $name;

/**
* @ORM\ManyToMany(targetEntity="AppBundle\Entity\Organizer", mappedBy="cities")
*/
protected $organizers;
}


Organizer.php

class Organizer
{
/**
* @Groups({"jsonForCity", "questOrganizers"})
* @ORM\Column(type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
protected $id;
/**
* @ORM\Column(type="string", length=140)
*/
protected $name;


/**
* @ORM\ManyToMany(targetEntity="AppBundle\Entity\City", inversedBy="organizers")
* @ORM\JoinTable(name="organizers_cities")
*
*/
protected $cities;

public function __construct() {
$this->quests = new ArrayCollection();
}

Answer
$qb = $em->createQueryBuilder();
$repository = $this->getDoctrine()->getRepository('AppBundle:Organizers');
$query = $repository->createQueryBuilder('o')
        ->innerJoin('o.cities', 'c')
        ->where('o.name LIKE :partOfSearchText')
        ->andWhere('IDENTITY(c) = :city_id')
        ->setParameter('partOfSearchText', '%'.$partOfSearchText.'%')
        ->setParameter('city_id', $cityId)
        ->getQuery();

More info: http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/query-builder.html, http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html

Comments