Join statement just ignored in Dart API

[nesmrtelny]

Hi there, when trying to compose a query using Dart API, I am facing a wierd problem. Everything works well except join statement - highlighted in code below:

    final tasksQuery = database.select(database.tasks);

    // WHERE CONDITIONS - OK
    tasksQuery.where((t) => t.deletedAt.isNull());

    if (hideDoneTasks) {
      tasksQuery.where((t) => t.status.equals(0));
    }

    if (showOnlyImportantTasks) {
      tasksQuery.where((t) => t.important.equals(1));
    }


    // -------------------- ABSOLUTELY IGNORED ---------------------
    tasksQuery.join([
      innerJoin(database.peopleTasks, database.peopleTasks.taskId.equalsExp(database.tasks.id)),
    ]).where(
      database.peopleTasks.personId.equals(personId),
    );
    // -------------------- END OF ABSOLUTELY IGNORED ---------------------
 


    // LIMIT - OK
    tasksQuery.limit(tasksLimit);

    // GET TASKS
    final tasks = await tasksQuery.get();

The previous code generates query like this:
SELECT * FROM "tasks" WHERE "deleted_at" IS NULL AND "status" = ? LIMIT 10;

When i write pure sql query, there is no problem with joining.

I have also triple checked the table definition, but I dont see a problem here neither.

@DataClassName('PersonTask')
class PeopleTasks extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get personId => integer().references(People, #id, onDelete: KeyAction.cascade)();
  IntColumn get taskId => integer().references(Tasks, #id, onDelete: KeyAction.cascade)();
}

I am probably missing something important, but after two days of digging to nowhere, I have no idea what to do except of conversion to sql.

Any hint would be highly appreciated.

[simolus3]

Unlike methods like where that mutate the single-table query, join returns a new object. This is required because we can't have callback-like APIs for WHEREs on joins, so the interface of the statement is slightly different after adding the join.

So you can fix the snippet by storing the result of join in a new variable:

    final joined = tasksQuery.join([
      innerJoin(database.peopleTasks, database.peopleTasks.taskId.equalsExp(database.tasks.id)),
    ]).where(
      database.peopleTasks.personId.equals(personId),
    );

    joined.limit(tasksLimit);

    final rows = await joined.get();

For each row in rows, you can get the todo item with readTable(database.tasks) and the associated person with readTable(database.peopleTask) on the row object.

[nesmrtelny]

Oh man, I see it now. Thank you so much Simon, you just made another person happy today! :)

If someone is facing the same problem, here is the complete sample which works (in the previous simolus3 response the double dot before where() statement is missing (which is my fault from original post)).

final joined = tasksQuery.join([
        innerJoin(database.peopleTasks, database.peopleTasks.taskId.equalsExp(database.tasks.id), useColumns: false),
      ])
        ..where(database.peopleTasks.personId.equals(personId));

  joined.limit(tasksLimit);

  final rows = await joined.get();

  final tasks = rows.map((row) => row.readTable(database.tasks)).toList();