CODEWITHSUNDEEP
symfonydoctrine-orm
Kvothe
Kvothe

Reputation: 1837

Using an Entity's methods inside a query in Doctrine

I have a table of Customers with fields name, addressLine1, addressLine2 and postcode. The corresponding entity also has a method called address that returns the 2 address lines and the postcode concatenated together with comma/space separation, ignoring any empty fields.

I want to return a list of customers sorted by name and then address (for any customers with the same name). Currently I try

$this->getEntityManager()->createQuery(
    'SELECT c FROM AppBundle:Customer c ORDER BY c.name ASC, c.address ASC'
)->getResult();

but I cannot use the method Customer::address() in the query like this. I get the error

Error: Class AppBundle\Entity\Customer has no field or association named address

Is there a way I can use an Entity's methods inside a query like this?

Upvotes: 1

Views: 1303

Answers (1)

Jason Roman
Jason Roman

Reputation: 8276

Short answer - no, and you don't really want to. You're conflating PHP logic with SQL logic. Your address() function is a pure PHP function. Even though it is using relationships within your entity, Doctrine itself has no way of knowing about it. Your function is literally returning a string, so how would it know how to convert that to SQL for your WHERE clause?

Just change your original query to this:

$this->getEntityManager()->createQuery('
    SELECT c
    FROM AppBundle:Customer c
    ORDER BY c.name ASC, c.addressLine1 ASC, c.addressLine2 ASC, c.postcode ASC
')->getResult();

I suppose you could pseudo-do what you want like this:

Customer Entity:

public static function addressSort()
{
    return ' c.addressLine1 ASC, c.addressLine2 ASC, c.postcode ';
}

and then do

$this->getEntityManager()->createQuery('
    SELECT c
    FROM AppBundle:Customer c
    ORDER BY c.name ASC, ' . Customer::addressSort()
)->getResult();

However, now you're mixing PHP and SQL even further and I very highly recommend that you do NOT do this.

Upvotes: 1

Related Questions