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


Reply

3 Posts   219 Views

Avatar
senorgeno

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

Hi,

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.

select
video.*
from
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.

Thanks

Avatar
senorgeno

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.

Avatar
Willr

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.