Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Data Model Questions /

Moderators: martimiz, Sean, Ed, biapar, Willr, Ingo, swaiba

Unable to sub select on Left Join via ORM

Go to End

3 Posts   1163 Views


Community Member, 10 Posts

10 July 2014 at 4:56pm


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
order by
coalesce(Likes.likecount, 0)

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.



Community Member, 10 Posts

10 July 2014 at 11:10pm

Edited: 10/07/2014 11:11pm

After playing around, I fixed this locally by editing sql() function on SQLQuery to this: (lines 905 - 908)

$table = strpos(strtoupper($join['table']), 'SELECT') ? $join['table'] : "\"" . $join['table'] . "\"";
$aliasClause = ($alias != $join['table']) ? " AS \"" . Convert::raw2sql($alias) . "\"" : "";
$this->from[$alias] = strtoupper($join['type']) . " JOIN " . $table . " $aliasClause ON $filter";

Should I do a pull request for this? Im not sure this is really the best approach, feels hacky but frustrating that I can't do a left join sub-select because of the escape strings implemented by default. FYI I am on 3.1.5.


Forum Moderator, 5523 Posts

12 July 2014 at 11:34am

Submitting a PR may be your best bet with a test case to show what you would like to work. We probably don't have a test case of a subselect within a join (didn't know that was possible) so likely been overlooked in the ORM.