CODEWITHSUNDEEP
symfonysearchdoctrine-orm
Theodore Enderby
Theodore Enderby

Reputation: 642

Can I search an entity's child properties in Symfony/ORM?

I'm creating a search function for my Company entity and putting my logic in a custom entity repository using Symfony 2 and Doctrine ORM.

The Company entity has a child collection Locations, as comapnies can have multiple locations, and a Company Name field.

I'm wondering if it's possible to search by Company Name and Location[1].address, Location[2].address, etc. using the Query Builder.

This is what I have so far and can't find a good resource for learning this.

*/ AppBundle/Repository/CompanyRepository.php */

class CompanyRepository extends EntityRepository
{

    public function findBySearch($options)
    {
        $query = $this->createQueryBuilder('c');

        foreach ($options as $key => $value) {
            $query->where('c.' . $key . ' LIKE :' . $key);
            $query->setParameter($key, '%' . $value . '%');
        }

        return $query->getQuery()->getResult();
    }
}

It looks like this doesn't work: 

 $query->where('c.locations.city LIKE $city);

An example for the $options argument in findBySearch() would be:

[ 
    'companyName' => 'Mikes Company',
    'locations.city' => 'New York',
    'locations.state' => 'New York'
]

Any advice or links to resources are much appreciated.

Upvotes: 0

Views: 1292

Answers (2)

Bakhtier Gaibulloev
Bakhtier Gaibulloev

Reputation: 159

You selected c, after that you want to find company with some location, it mean that in company entity you should have (with type Location)field location. Location entity shoud have field (string)city. Field location in Company entity should have oneToMany association with LocationEntity. After that when you will add company's name you also will add company's location. And now you can write:

$query->leftJoin('c.locations', 'locations');
$query->where('locations.city LIKE :city');
$query->setParameter('city', '%'.$city.'%')

If you want to use c.locations.city property of location object you should call there association. Like leftJoin, innerJoin. But I think innerJoin here will be more better and take just two line.

$query->innerJoin('c.locations', 'locations', 'WITH', 'locations.city LIKE (:city)')
$query->setParameter('city', '%'.$city.'%')

Upvotes: 1

Vamsi Krishna B
Vamsi Krishna B

Reputation: 11490

try changing $query->where to $query->andWhere

->andWhere() can be used directly, without any ->where() before

Upvotes: 0

Related Questions