I need to run something like this query below so I can order Videos based on the number of Likes they get form Members. Likes are in a join table with Member.
left join (select VideoID, count(1) as likecount from Video_Likes group by VideoID) Likes on
Likes.VideoID = Video.ID
I am running this via a ->leftjoin("(SELECT \"VideoID\", count(1) as \"LikeCount\" from \"Video_Likes\" GROUP BY \"VideoID\")", "\"Likes\".\"VideoID\" = \"Video\".\"ID\"", 'Likes') but the trouble is, when the query is built in SQLQuery it is wrapping the SELECT statement in quotes causing errors.
Any advice on this? Should I subclass DataQuery and perhaps add a function such as addCustomLeftjoin() to get around this?
I would like to use the ORM for this rather than DB::query() as I am using this as part of a wider REST querying system where I would like to add various filters to the base query based on query params.