I have made a query to the database to get the actual availability of the rooms of several hotels but when trying to make the query with doctrine in symfony 3.4, I get an error.
This is the query:
SELECT (CASE WHEN count(_days) > 0 THEN 'yes' ELSE 'No' END)
FROM ( SELECT count(roomTypeDay2.room_type_id) as _days
FROM AppBundle:RoomTypeDayCancelationConditionAccommodation as RTDCCA2
LEFT JOIN RTDCCA2.roomTypeDay roomTypeDay2
WHERE
roomTypeDay2.date IN ('2018-06-09 00:00:00','2018-06-10 00:00:00','2018-06-11 00:00:00')
GROUP BY roomTypeDay2.room_type_id
HAVING COUNT(roomTypeDay2.room_type_id) = 3 ) as sub
The fact is that symfony returns the exception:
[Semantical Error] line 0, col 640 near '(SELECT count(roomTypeDay2.room_type_id)': Error: Class '(' is not defined.
It seems like I can not do a subquery on doctrine.
Can someone help me?
the query in sql that I want to pass to dql, is the following:
SELECT (CASE WHEN count(_days) > 0 THEN 'yes' ELSE 'No' END) as availability
FROM
(
SELECT count(rtd.room_type_id) as _days
FROM room_type_day as rtd
WHERE rtd.date IN ('2018-06-20', '2018-06-21', '2018-06-22')
GROUP BY rtd.room_type_id
HAVING COUNT(rtd.room_type_id) = 3
) as sub
Thank you very much.