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

Unable to sub select on Left Join via ORM


3 Posts   219 Views


10 July 2014 at 4:56pm Community Member, 10 Posts


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.



10 July 2014 at 11:10pm (Last edited: 10 July 2014 11:11pm), Community Member, 10 Posts

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.


12 July 2014 at 11:34am Forum Moderator, 5511 Posts

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.