How to Select / Join From Subquery?

[aldycool]

Hi,

I've searched the docs and couldn't find information about subqueries. Is it possible to do selects like this in code:

SELECT * FROM (SELECT * FROM A_TABLE WHERE some_condition) a

And also joins like this in code:

SELECT * FROM A_TABLE LEFT JOIN (SELECT * FROM OTHER_TABLE WHERE some_condition) ON ...snip...

Or, if those are not supported right now, is there any open classes that I can (safely) subclass or override? or like alter the final generated SQL language? Thanks!

[simolus3]

Of course, those queries are fully supported in moor files where SQL is analyzed at compile-time to generate result classes.

Unfortunately, we can't generally support those features in Dart. To be safe, we'd have to generate a new result class for each subquery since it can introduce a new result set not covered by an existing class. That's not the case in the examples you posted and we could support subqueries where we select all columns from an existing table. It's just that those subqueries aren't particularly useful in practice, since you can also write them as regular joins.

Or, if those are not supported right now, is there any open classes that I can (safely) subclass or override? or like alter the final generated SQL language? Thanks!

You could try writing a subclass of ResultSetImplementation where the entityName evaluates to the SQL of the subquery. If you only want to support subqueries for a single table, you can delegate asDslTable, $columns and map to that.

I can't guarantee that this will work, but that sounds like the most promising approach with the current design.

[aldycool]

Hi, I think I found the solution for my use case. The original table class is like this:

class Persons extends Table {
  TextColumn get id => text().withLength(max: 36).clientDefault(() => Uuid().v4())();
  TextColumn get name => text().withLength(max: 255).withDefault(Constant(""))();
  IntColumn get age => integer().withDefault(const Constant(0))();
}

Moor will generate the table class like this:

class $PersonsTable extends Persons with TableInfo<$PersonsTable, Person> {
  final GeneratedDatabase _db;
  final String? _alias;
  $PersonsTable(this._db, [this._alias]);
  
  //...snip... (all fields will be generated here)
}

Earlier you've mentioned an important point which is the entityName can actually be used to resolve to an SQL subquery, and the generated $PersonsTable itself is already implements ResultSetImplementation<T, D>, so now I can directly subclass it like this:

// NOTE: I will source-gen'ed this class later so the `entityName` won't be hard-coded
class $SyncPersonsTable extends $PersonsTable {
  $SyncPersonsTable(GeneratedDatabase _db, [String? _alias]) : super(_db, _alias);

  @override
  String get entityName => "(SELECT * FROM ${super.entityName} WHERE deleted = 0)";
}

Now the subclass is ready, I just add an extension to my database class:

extension CustomExt on MyDatabase {
  $SyncPersonsTable get syncPersons => $SyncPersonsTable(this);
}

And this is what the generated SQL looks like (the logStatements is turned on):

await select(persons).get(); // The original one
// Moor: Sent SELECT * FROM person; with args []

await select(syncPersons).get(); // The subclassed one
// Moor: Sent SELECT * FROM (SELECT * FROM person WHERE deleted = 0) person; with args []

I've tested it and it works on select()s, selectOnly()s, and join()s, they all sent the correct generated SQL. Thank you for your very helpful insights, and great work on this library. Thanks!