Using an Entity's methods inside a query in Doctrine

Question

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?

This answer might provide some additional insight: https://stackoverflow.com/a/30054309/1307183 — Jason Roman, Jun 29 '17 at 15:33

score 1 · Accepted Answer · answered Jun 29 '17 at 15:31

1

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.

answered Jun 29 '17 at 15:31

Jason Roman

8,146
10
35
40

Thanks. The top solution is one I went for. I would agree that mixing PHP and SQL is unappealing - but could you explain why it's such a bad thing to do? – Kvothe Jul 21 '17 at 09:37
Well I wasn't saying that it was a bad thing; I meant that what you were trying to do wouldn't work as code because it tried to use PHP functionality from within raw SQL. But, SQL has no way of knowing about that `address` method. I know using Doctrine is like mixing both PHP and SQL, and abstracting it out to PHP, but what you were trying is not something that Doctrine knows how to handle. – Jason Roman Jul 21 '17 at 13:41
Ah I see, thanks for the clarification. Yeah, I didn't entirely expect what I tried originally to work, but kinda hoped that maybe Doctrine would have a way of handling it. – Kvothe Jul 21 '17 at 14:10
Right. That would definitely be an interesting feature to try to tie entities into some way of auto-converting, but Doctrine probably doesn't do that because it would have to make a lot of assumptions about what you're looking to do. – Jason Roman Jul 21 '17 at 14:28

Using an Entity's methods inside a query in Doctrine

1 Answers1