ambiguous column name with flutter moor

Issue

I have the following structure:

import 'package:moor_flutter/moor_flutter.dart';
export 'package:moor_flutter/moor_flutter.dart' show Value;

part 'moor_database.g.dart';

@DataClassName('Story')
class Stories extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text().withLength(max: 20).nullable()();
  TextColumn get storyText => text().nullable()();
  TextColumn get feeling => text().withLength(max: 1).nullable()();
  DateTimeColumn get date => dateTime()();
  DateTimeColumn get createdAt => dateTime()();
}

class Images extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get storyId =>
      integer().nullable().customConstraint('NULL REFERENCES stories(id)')();
  TextColumn get path => text()();
}

class CompleteStory {
  final Story story;
  final List<Image> images;

  CompleteStory({
    this.story,
    this.images,
  });
}

@UseMoor(tables: [Stories, Images], daos: [StoryDao, ImageDao])
class AppDatabase extends _$AppDatabase {
  AppDatabase()
      : super(FlutterQueryExecutor.inDatabaseFolder(
          path: 'db.sqlite',
          logStatements: true,
        ));

  @override
  int get schemaVersion => 2;

  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      beforeOpen: (details) async {
        await customStatement('PRAGMA foreign_keys = ON');
      },
    );
  }
}

@UseDao(tables: [Stories, Images])
class StoryDao extends DatabaseAccessor<AppDatabase> with _$StoryDaoMixin {
  final AppDatabase db;

  StoryDao(this.db) : super(db);

  Future<List<Story>> getAllStories() => select(stories).get();

  Stream<List<CompleteStory>> watchAllStories() {
    return (select(stories)
          ..orderBy([
            (s) => OrderingTerm(expression: s.date, mode: OrderingMode.asc),
          ]))
        .join([
          leftOuterJoin(stories, stories.id.equalsExp(images.storyId)),
        ])
        .watch()
        .map((rows) {
          final groupedData = <Story, List<Image>>{};

          for (final row in rows) {
            final story = row.readTable(stories);
            final image = row.readTable(images);

            final list = groupedData.putIfAbsent(story, () => []);
            if (image != null) list.add(image);
          }

          print(groupedData.entries);

          return [
            for (final entry in groupedData.entries)
              CompleteStory(story: entry.key, images: entry.value)
          ];
        });
  }

  Future insertStory(Insertable<Story> story) => into(stories).insert(story);
  Future updateStory(Insertable<Story> story) => update(stories).replace(story);
  Future deleteStory(Insertable<Story> story) => delete(stories).delete(story);
}

@UseDao(tables: [Images])
class ImageDao extends DatabaseAccessor<AppDatabase> with _$ImageDaoMixin {
  final AppDatabase db;

  ImageDao(this.db) : super(db);

  Stream<List<Image>> watchImages() => select(images).watch();
  Future insertImage(Insertable<Image> image) => into(images).insert(image);
}

But when I try to use on my streamBulder it never reaches the snap.hasData, my guess it’s because of the following warning I get on terminal:

E/SQLiteLog( 7229): (1) ambiguous column name: stories.id

I tried removing the app, didn’t work.

Solution

I needed to invert the join terms, using the select stories on the right side:

select(stories)
        .join([
          leftOuterJoin(storyImages, storyImages.id.equalsExp(stories.id)),
        ])
        .watch()

Answered By – Felipe Augusto

Answer Checked By – Terry (FlutterFixes Volunteer)