JOIN with nested SELECT statement

[nathanielconroy]

First off, this is an awesome component! I'm hoping that I can use it in a project of mine.

However, I'm having trouble generating a statement similar to the following:

SELECT a.fullname, a.ID, a.Score FROM my_table a INNER JOIN ( SELECT fullname, MAX(Score) Score FROM my_table GROUP BY fullname ) b ON a.fullname = b.fullname AND a.Score = b.Score

Is this something that is currently supported? I don't quite understand how to nest a SELECT statement inside of a JOIN like above. Thanks!

[shadowhand]

You can do this with an Expression:

$joinTable = e::make($selectScore->sql());
$select = SelectQuery::make(...)->innerJoin($joinTable, c::make(...));

[nathanielconroy]

Thank you. Actually, when I do this I get a type error:

Argument 1 passed to Latitude\QueryBuilder\SelectQuery::innerJoin() must be of the type string, object given

Of course I can pass it as a string by doing the following:

$joinTable = e::make($selectScore->sql()); $select = SelectQuery::make(...)->innerJoin($joinTable->sql(), c::make(...));

However, in that case I get a different error:

Uncaught Latitude/QueryBuilder/IdentifierException: Invalid SQL identifier: fullname

[shadowhand]

Ah yes, this is much more complicated now that I review the code in question. I think the best way to resolve this will be to rework some of the internals of Latitude. Doing so will probably result in a new major version bump. This would actually be a good thing, as there are a number of areas where the internals could be simplified and/or made more type safe.

[nathanielconroy]

That makes sense. I figure you could adjust any method of SelectQuery that takes a string to be able to take an expression instead. (I'm not totally clear on the options for overloading functions in PHP as I'm coming from C++.) Anyway, thanks for your quick replies!