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

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.

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.