2

I'm on a symfony2 project, and I've got an issue with the following query :

SELECT count(a) 
FROM CoreBundle:Agency a 
WHERE (
    SELECT count(c.id)
    FROM CoreBundle:Agency a1, CoreBundle:Client c
    WHERE a1.id = c.agency AND a1.id = a.id
) LIKE "%1%"

When I want the result, Doctrine2 returns this error :

Error: Expected =, <, <=, <>, >, >=, !=, got 'LIKE'

The same query (adapted in SQL instead of DQL) works fine.

I tried to find a solution but nothing came...

Thanks for your help !

Ahmed Salman Tahir
  • 1,783
  • 1
  • 17
  • 26
Aly
  • 458
  • 2
  • 13
  • What kinds of values do you expect the subquery to return? Perhaps we can help craft it in a way that Doctrine accepts. Using `LIKE` in that position does appear to be supported by MySQL itself. – Michael Berkowski Dec 30 '13 at 15:49
  • The subquery differs sometimes, it can return int or string values, on this example it returns only int value because it's a COUNT() – Aly Dec 30 '13 at 15:53
  • Can you convert the result of your subquery to a string? Not sure how to do it in symfony but there's got to be a way. – Reinstate Monica -- notmaynard Dec 30 '13 at 15:54
  • I don't see how without doing 2 queries (first the subquery, then the query) and I'm not sure about the result. Maybe there's a way to do that with a doctrine2 function, but I don't know which one – Aly Dec 30 '13 at 15:56
  • I don't really understand your query. You're trying to count the number of agencies which have at least 1 client right? Why using a LIKE for a count result? – Snroki Dec 30 '13 at 16:15
  • This query is used on the jQuery datatable plugin, it allows to make a search on all the column (for example, if I have id, name, and clients number column, if I type "1" in the search box the query search for agency with "1" in the name OR "1" in the id OR "1" as clients number). I hope it's clear. Since the query is used on different table and sometimes it returns a string I can't change the LIKE to the "=" operator – Aly Dec 30 '13 at 16:18

1 Answers1

0

That error tells me that the DQL parser doesn't understand your query. You have a few options in this situation.

You can extend the DQL parser so Doctrine will recognize your query as valid. You can read up on doing so in the Doctrine cookbook article DQL User Defined Functions.

The other option would be to use a Native Query.

I hope that helps. I'm sure there are other ways as well though. I would suggest extending the parser so you can keep using the Query Builder with your new functionality.

Johnny
  • 559
  • 9
  • 17
  • Indeed I will take a loot at the DQL User Defined Functions, but in this case, it's a basic query, it doesn't use any MySQL function or whatever... A bit regressive. – Aly Jan 01 '14 at 23:40