Moor: Selecting the last records in a one-to-many relationship

[Holofox]

Hello! I have two tables with messages and chats. How can I get a list of chats with the last message in each of them?

At the moment, I almost manage to do this through the sequence identifier that comes in the model, but I don’t like this solution (which also does not work).

  @override
  Stream<List<MoorChatWithLastMessage>> watchChatList() {
    return (select(moorChatsEntity).join([
      leftOuterJoin(
        moorMessagesEntity,
        moorMessagesEntity.seq.equalsExp(moorChatsEntity.received),
      ),
    ])..where(moorChatsEntity.id.equalsExp(moorMessagesEntity.streamID))
      ..orderBy([
        OrderingTerm.desc(moorChatsEntity.pinned),
        OrderingTerm.desc(moorMessagesEntity.createdAt),
      ])
    ).watch().map((rows) => rows.map((row) =>
      _mapToChatStreamWithLastMessage(row)).toList()
    );
  }

  MoorChatWithLastMessage _mapToChatStreamWithLastMessage(TypedResult row) {
    final chat = row.readTable(moorChatsEntity);
    final message = row.readTable(moorMessagesEntity);

    return MoorChatWithLastMessage(
      chat: chat,
      message: message
    );
  }

[Holofox]

@DataClassName('MoorChat')
class MoorChatsEntity extends Table {
  /// Chat ID
  TextColumn get id => text()();
  /// Message Number Delivered (seq)
  TextColumn get received => text().nullable()();
  /// Is the channel / group docked in the chat list
  BoolColumn get pinned => boolean().withDefault(const Constant(false))();

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

@DataClassName('MoorMessage')
class MoorMessagesEntity extends Table {
  /// Sequence ID
  TextColumn get seq => text()();
  /// Message ID
  TextColumn get messageID => text()();
  /// Chat ID
  TextColumn get streamID => text()();
  /// Message Creation Date
  DateTimeColumn get createdAt => dateTime()();

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

[simolus3]

So you want the message with the highest seq value for each chat, right?

At the moment, I'd recommend to use a sql query for this. By using nested results, you can get moor to generate exactly what you need:

chatList: SELECT
  chat.**, last_msg.**
FROM moor_chats_entity AS chat
LEFT OUTER JOIN moor_messages_entity AS last_msg ON last_msg.message_i_d = 
  (SELECT MAX(seq) FROM moor_messages_entity WHERE stream_i_d = chat.id);

This generates a Selectable<ChatListResult> chatList() method, where the class is defined as follows:

class ChatListResult {
  final MoorChat chat;
  final MoorMessage lastMsg;
}

That's what you need, right? At the moment subqueries aren't available in the Dart query builder.

[Holofox]

@simolus3, it looks cool, but I get the error with the "MAX" aggregate function:

DatabaseException(Error Domain=FMDatabase Code=1 "misuse of aggregate: MAX()" UserInfo={NSLocalizedDescription=misuse of aggregate: MAX()})

Full request:

chatList: SELECT
  chat.**, last_msg.**
FROM moor_chats_entity AS chat
LEFT OUTER JOIN moor_messages_entity AS last_msg ON last_msg.message_i_d =
  (SELECT MAX(last_msg.seq) FROM moor_messages_entity WHERE last_msg.stream_i_d = chat.id)
ORDER BY chat.pinned DESC, last_msg.created_at DESC;

I also tried to do this with the created_at field.

[simolus3]

Ah, sorry - of course we can't use last_msg in an inner aggregate. It should work if you just remove the last_msg prefix:

chatList: SELECT
  chat.**, last_msg.**
FROM moor_chats_entity AS chat
LEFT OUTER JOIN moor_messages_entity AS last_msg ON last_msg.message_i_d =
  (SELECT MAX(seq) FROM moor_messages_entity WHERE stream_i_d = chat.id)
ORDER BY chat.pinned DESC, last_msg.created_at DESC;