I have models (simplify):
class _User {
int id;
@Relate(#users)
Currency currency;
}
class _Currency {
int id;
ManagedSet<Price> prices;
ManagedSet<User> users;
}
class _Price {
int id;
@Relate(#prices)
Currency currency;
ManagedSet<DefaultPrice> defaultPrices;
}
class _Game {
int id;
String code;
}
class _DefaultPrice {
int id;
@Relate(#defaultPrices)
Price price;
@Relate(#defaultPrices)
EventTemplate eventTemplate;
}
class _EventTemplate {
int id;
ManagedSet<DefaultPrice> defaultPrices;
@Relate(#eventTemplates)
Game game;
}
And now i want to fetch all event templates for a particular game with included default prices but only with a certain currency.
To solve it i use this code in my controller:
final String gameCode = request.path.variables['gameCode'];
final User user = request.attachments['user'];
final eventTemplatesQuery = Query<EventTemplate>(database)
..where((eventTemplate) => eventTemplate.game.code).equalTo(gameCode);
eventTemplatesQuery.join(set: (eventTemplate) => eventTemplate.defaultPrices)
..where((defaultPrice) => defaultPrice.price.currency.id).equalTo(user.currency.id);
// ..where((defaultPrice) => defaultPrice.price.currency).identifiedBy(user.currency.id); // the same result
final eventTemplates = await eventTemplatesQuery.fetch();
I expect that all event templates will be selected, filtered by the game code, after which default prices filtered by currency will be joined. But instead I get an error:
[FINE] aqueduct: Query (8ms) SELECT t0.id,t0.code,t0.description,t0.createdAt,t0.updatedAt,t0.game_id,t1.id,t1.createdAt,t1.updatedAt,t1.eventTemplate_id,t1.price_id FROM _EventTemplate t0 LEFT OUTER JOIN _DefaultPrice t1 ON (t0.id=t1.eventTemplate_id AND t3.id = @t1t3_id:int8) LEFT OUTER JOIN _Price t2 ON t1.price_id=t2.id LEFT OUTER JOIN _Currency t3 ON t2.currency_id=t3.id LEFT OUTER JOIN _Game t4 ON t0.game_id=t4.id WHERE t4.code LIKE @t0t4_code:text {t0t4_code: SM, t1t3_id: 8}
[WARNING] aqueduct: PostgreSQLSeverity.error 42P01: missing FROM-clause entry for table "t3"
[SEVERE] aqueduct: POST {{ request.toDebugString() }} PostgreSQLSeverity.error 42P01: missing FROM-clause entry for table "t3"
Looks like Aqueduct ORM can`t filter with nested JOINs But there is nothing in the documentation about limits or restrictions in this case.
Aqueduct CLI version: 3.1.0+1
Dart VM version: 2.1.0 (Tue Nov 13 18:22:02 2018 +0100) on "windows_x64"
postgres (PostgreSQL) 10.6 (Ubuntu 10.6-1.pgdg18.04+1)