Moor: Database tables are unknown when i try to use join

[MahdiPishguy]

in this database structure i defined 3 table as User, Tickets, TicketsReply and when i try to use them in join i get error as

error: Undefined name 'tickets'. (undefined_identifier at [database_join_sample] lib\database\database.dart:76)
error: Undefined name 'tickets'. (undefined_identifier at [database_join_sample] lib\database\database.dart:76)
error: The argument type '$UserTable' can't be assigned to the parameter type 'TableInfo<$UserTable, Null>'. (argument_type_not_assignable at [database_join_sample] lib\database\database.dart:82)
error: Undefined name 'tickets'. (undefined_identifier at [database_join_sample] lib\database\database.dart:83)
error: Undefined name 'ticketReply'. (undefined_identifier at [database_join_sample] lib\database\database.dart:84)

i build more this database class and i check clean command,

compiled database content:
database.g.dart

one

two

class UserDao extends DatabaseAccessor<AppDb> with _$UserDaoMixin ,_$TicketsDaoMixin,_$TicketsReplyDaoMixin{

import 'package:moor_flutter/moor_flutter.dart';

part 'database.g.dart';

//@formatter:off
class User extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
  IntColumn get tickets => integer().nullable().customConstraint('NULL REFERENCES tickets(userId)')();
}

class Tickets extends Table {
  IntColumn get userId => integer()();
  IntColumn get ticketId => integer()();
  TextColumn get ticketSubject => text()();
  TextColumn get ticketDescription => text()();
  IntColumn get replies => integer().nullable().customConstraint('NULL REFERENCES ticketsReply(userId)')();

  @override
  Set<Column> get primaryKey => {userId};
}

class TicketsReply extends Table {
  IntColumn get parentTicket => integer()();
  IntColumn get replyId => integer()();
  TextColumn get replyMessage => text()();

  @override
  Set<Column> get primaryKey => {parentTicket};
}

class UserInformation {
  final User user;
  final List<Tickets> ticket;
  final List<TicketsReply> reply;

  UserInformation({@required this.user, @required this.ticket, @required this.reply});
}
//@formatter:on

@UseMoor( tables: [User, Tickets, TicketsReply], daos: [UserDao, TicketsDao, TicketsReplyDao], queries: {} )
class AppDb extends _$AppDb {
  AppDb() : super( FlutterQueryExecutor.inDatabaseFolder( path: 'db.sqlite', logStatements: true ) );

  @override
  int get schemaVersion => 1;

  @override
  MigrationStrategy get migration =>
      MigrationStrategy(
        onUpgrade: (m, from, to) async {},
        beforeOpen: (db, detail) async {
          await db.customStatement( 'PRAGMA foreign_keys = ON' );
        },
      );
}

@UseDao( tables: [User] )
class UserDao extends DatabaseAccessor<AppDb> with _$UserDaoMixin{
  final AppDb db;

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

  Future<UserData> getSingleUser() {
    return (select( user )
      ..orderBy( [(t) => OrderingTerm( expression: t.id, mode: OrderingMode.desc )] )
      ..limit( 1 ))
        .getSingle( );
  }

  Stream<List<UserInformation>> watchAllUserInfo() {
    return (select( user )
      ..orderBy( [(t) => OrderingTerm( expression: t.id, mode: OrderingMode.desc )] ))
        .join(
      [
        leftOuterJoin( tickets, tickets.userId.equalsExp( user.id ) ),
        leftOuterJoin( ticketsReply, ticketsReply.replyId.equalsExp( tickets.replies ) ),
      ],
    ).watch( )
        .map( (rows) =>
        rows.map( (row) {
          return UserInformation(
              user: row.readTable( user ),
              ticket: row.readTable( tickets ),
              reply: row.readTable( ticketsReply) );
        } ).toList( ) );
  }

  Future<int> insertUser(Insertable userData) => into( user ).insert( userData );

  Future updateUser(Insertable userData) => update( user ).replace( userData );

  Future deleteUser(Insertable userData) => delete( user ).delete( userData );
}

@UseDao( tables: [Tickets] )
class TicketsDao extends DatabaseAccessor<AppDb> with _$TicketsDaoMixin {
  final AppDb db;

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

  Future<int> insertTicket(Insertable ticketData) => into( tickets ).insert( ticketData );

  Future updateTicket(Insertable ticketData) => update( tickets ).replace( ticketData );

  Future deleteTicket(Insertable ticketData) => delete( tickets ).delete( ticketData );
}

@UseDao( tables: [TicketsReply] )
class TicketsReplyDao extends DatabaseAccessor<AppDb> with _$TicketsReplyDaoMixin {
  final AppDb db;

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

  Future<int> insertUser(Insertable reply) => into( ticketsReply ).insert( reply );

  Future updateUser(Insertable reply) => update( ticketsReply ).replace( reply );

  Future deleteUser(Insertable reply) => delete( ticketsReply ).delete( reply );
}

[simolus3]

You're mixing in multiple daos in your second screenshot, I think that might be a problem. Can you just use

@UseDao(tables: [Users, Tickets, TicketsReply])
class UserDao extends DatabaseAccessor<AppDb> with _$UserDaoMixin {
 // ...
}

To help me take a more detailed look, can you post the snippet that causes the problem in text form? Sometimes this problem is caused because the analyzer doesn't pick up generated outputs. Can you try to restart the analysis server (Click on the "Dart Analysis" tab at the bottom of your IDE, then click on the red circle with two arrows in the top-left of the pane that opens).

[MahdiPishguy]

@simolus3 i restarted dart analysis and nothing any change

@UseDao( tables: [User] )
class UserDao extends DatabaseAccessor<AppDb> with _$UserDaoMixin {
  final AppDb db;

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

  Future<UserData> getSingleUser() {
    return (select( user )
      ..orderBy( [(t) => OrderingTerm( expression: t.id, mode: OrderingMode.desc )] )
      ..limit( 1 ))
        .getSingle( );
  }

  Stream<List<UserInformation>> watchAllUserInfo() {
    final ticket = alias( tickets, 't' );
    final ticketReply = alias( ticketsReply, 'tr' );
    return (select( user )
      ..orderBy( [(t) => OrderingTerm( expression: t.id, mode: OrderingMode.desc )] )).join(
      [
        leftOuterJoin( tickets, tickets.userId.equalsExp( user.id ) ),
        leftOuterJoin( ticketsReply, ticketsReply.replyId.equalsExp( tickets.replies ) ),
      ],
    ).watch( ).map( (rows) =>
        rows.map( (row) {
          return UserInformation(
              user: row.readTable( user ),
              ticket: row.readTable( ticket ),
              reply: row.readTable( ticketReply ) );
        } ).toList( ) );
  }
}

errors:

error: Undefined name 'tickets'. (undefined_identifier at [database_join_sample] lib\database\database.dart:72)
error: Undefined name 'ticketsReply'. (undefined_identifier at [database_join_sample] lib\database\database.dart:73)
error: Undefined name 'tickets'. (undefined_identifier at [database_join_sample] lib\database\database.dart:77)
error: Undefined name 'tickets'. (undefined_identifier at [database_join_sample] lib\database\database.dart:77)
error: Undefined name 'ticketsReply'. (undefined_identifier at [database_join_sample] lib\database\database.dart:78)
error: Undefined name 'ticketsReply'. (undefined_identifier at [database_join_sample] lib\database\database.dart:78)
error: Undefined name 'tickets'. (undefined_identifier at [database_join_sample] lib\database\database.dart:78)
error: The argument type '$UserTable' can't be assigned to the parameter type 'TableInfo<$UserTable, Null>'. (argument_type_not_assignable at [database_join_sample] lib\database\database.dart:83)

[simolus3]

I found a couple of problems. I'll close the issue because moor is working as intended here. Of course, I can still help you get the code working.
First, the ticket and ticketReply tables are not available from your dao because they're not included in the @UseDao annotation. To fix that, use

@UseDao( tables: [User, Tickets, TicketsReply] )

Second, your UserInformation model refers to the whole table, not to an individual row. Here's the fixed class below:

class UserInformation {
  final UserData user; // UserData instead of User
  final List<Ticket> ticket; // Ticket instead of Tickets
  final List<TicketsReplyData> reply; // TicketsReplyData instead of TicketsReply

  UserInformation({@required this.user, @required this.ticket, @required this.reply});
}

When you write a table class who's name ends with an "s", the correct row class is just the singular form. For instance, you declared a Tickets table and moor generates a Ticket class to hold a row in that table. When it doesn't end with an s, like in your User table, we'll generate a UserData table instead, it holds a single row.

Finally, your query is not going to work because you declare a List in your model, but each query row only contains one entry, so you can't map rows to a UserInformation class directly. I can help you rewrite the query, but I'm not sure what you want to express: The ticket field should contain every ticket written by the user? And the reply field should contain every reply sent to each ticket in ticket?

[MahdiPishguy]

@simolus3

referenced to https://github.com/simolus3/moor/issues/132

Thanks a lot, problem solved.

i want to make a simple relation ship between this tables. i created this database structure to learn how can i make relation ship between tables.

in this structure i have a user
each user can be have one or more ticket
and each ticket can be have on or more reply

then user -> can be have multiple ticket ->each ticket can be have multiple ticket reply

i want to make one to many relation ship between theme

[simolus3]

I see. Your use-case is not trivial to express in sql. You would probably need three different queries and merge them together (for instance by using rxdart). We have a example here, but it only uses two queries. So it could probably look like this:

1. Create a stream of all users
2. switchMap all users to the tickets they have created
3. switchMap all tickets to all replies that they created
4. Finally map all of that (single user, list of tickets and replies) into a single UserInformation class.

[MahdiPishguy]

@simolus3

i'm not sure how can implementing switchMap in this join. i simplified tables to two table as

each user can be have one or more ticket. ( hasMany relation ship)

how can i solve join part of code?

import 'package:moor_flutter/moor_flutter.dart';

part 'database.g.dart';

//@formatter:off
class User extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
  IntColumn get tickets => integer().nullable().customConstraint('NULL REFERENCES tickets(userId)')();
}

class UserTicket extends Table {
  IntColumn get userId => integer()();
  IntColumn get ticketId => integer()();
  TextColumn get ticketSubject => text()();
  TextColumn get ticketDescription => text()();

  @override
  Set<Column> get primaryKey => {userId};
}

class UserInformation {
  final UserData user;
  final List<UserTicketData> ticket;

  UserInformation({@required this.user, @required this.ticket});
}
//@formatter:on

@UseMoor( tables: [User, UserTicket], daos: [UserDao, TicketsDao], queries: {} )
class AppDb extends _$AppDb {
  AppDb() : super( FlutterQueryExecutor.inDatabaseFolder( path: 'db.sqlite', logStatements: true ) );

  @override
  int get schemaVersion => 1;

  @override
  MigrationStrategy get migration =>
      MigrationStrategy(
        onUpgrade: (m, from, to) async {},
        beforeOpen: (db, detail) async {
          await db.customStatement( 'PRAGMA foreign_keys = ON' );
        },
      );
}

@UseDao( tables: [User, UserTicket] )
class UserDao extends DatabaseAccessor<AppDb> with _$UserDaoMixin {
  final AppDb db;

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

  Future<UserData> getSingleUser() {
    return (select( user )
      ..orderBy( [(t) => OrderingTerm( expression: t.id, mode: OrderingMode.desc )] )
      ..limit( 1 ))
        .getSingle( );
  }

  Stream<List<UserInformation>> watchAllUserInfo() {
    return (select( user )
      ..orderBy( [(t) => OrderingTerm( expression: t.id, mode: OrderingMode.desc )] )).join(
      [
        leftOuterJoin( userTicket, userTicket.userId.equalsExp( user.id ) ),
      ],
    ).watch( ).map( (rows) =>
        rows.map( (row) {
          return UserInformation(
              user: row.readTable( user ),
              //ticket: row.readTable( userTicket.map((data)=>) ));
        } ).toList( ) );
  }

  Future<int> insertUser(Insertable userData) => into( user ).insert( userData );

  Future updateUser(Insertable userData) => update( user ).replace( userData );

  Future deleteUser(Insertable userData) => delete( user ).delete( userData );
}

@UseDao( tables: [UserTicket] )
class TicketsDao extends DatabaseAccessor<AppDb> with _$TicketsDaoMixin {
  final AppDb db;

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

  Future<int> insertTicket(Insertable ticketData) => into( userTicket ).insert( ticketData );

  Future updateTicket(Insertable ticketData) => update( userTicket ).replace( ticketData );

  Future deleteTicket(Insertable ticketData) => delete( userTicket ).delete( ticketData );
}

[simolus3]

If you add rxdart to your pubspec and import package:rxdart/rxdart.dart, you should be able to write

Stream<List<UserInformation>> watchAllUserInfo() {
  // start by selecting all users
  final usersQuery = select(user)
    ..orderBy(
        [(t) => OrderingTerm(expression: t.id, mode: OrderingMode.desc)]);
  final usersStream = usersQuery.watch();

  // we now turn a stream of users into a stream of UserInformation by loading
  // all relevant tickets
  return Observable(usersStream).switchMap((users) {
    final relevantIds = users.map((u) => u.id);
    final ticketQuery = select(userTicket)
      ..where((t) => isIn(t.userId, relevantIds));

    return ticketQuery.watch().map((tickets) {
      // at this point, we have all users and we have all tickets that belong
      // to a user. All that's left to do is merging them together!

      return users.map((u) {
        return UserInformation(
          user: u,
          ticket: tickets.where((t) => t.userId == u.id).toList(),
        );
      }).toList();
    });
  });
}

It's not very easy to express this in sql because there are two queries here, but here's the approach:

1. watch all users
2. when the list of users changes, load all tickets that are relevant
3. for each user we found, merge them with all tickets belonging to that user