Nested joins not on original table

kicks66

I am trying to setup this query, which requires nested joins to gather the data (which I then want to SUM)

The query looks like this:

Editor::inst( $db, 'users' )
->fields(
Field::inst( 'users.username' ),
Field::inst( 'users.email' ),
Field::inst( 'users.region' ),
Field::inst( 'users.last_posted' ),
Field::inst( 'users.id' )
->getFormatter( function ( $val, $data ) {
return strval($val);
}),
Field::inst( 'SocialProfiles.externalId' )
)
->leftJoin(
'SocialProfiles',
'SocialProfiles.externalId',
'=',
'users.id'
)
->join(
Mjoin::inst('CommunityVideoSubmissions')
->link('SocialProfiles.id', 'CommunityVideoSubmissions.socialProfileId')
)
->debug(true)
->process( $_POST )
->json();

But its giving me the error:

{
sError: "Join was performed on the field 'socialProfileId' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance."
}

Is it possible to do nested joins like this? or is there another way this should happen (especially if im not bothered about allowing those fields to be edited?)

allan

Currently no, with Editor's API abstraction that is no possible. However, you can use a VIEW with Editor which allows for any arbitrary SQL to be used as a data source.

Allan