join data from 2 tables into one class

Question

I need some database assistance.

I have the following tables:

I need to pull out an Event with the data from the EventType Table to assign and Event that has an EventType linked to a User.

I am using Moor and they cater for joins, I just need some assistance with the query. Currently I have been trying this:

Stream<EventWithType> eventWithType(EventData eventTypeId) {
  final query = select(event)
    ..where((tbl) {
      return tbl.id.equals(eventTypeId.id);
    })
   ..join([
     innerJoin(eventType, eventType.id.equals(eventTypeId.eventTypeId)),
   ]);
 }

EventWithType.dart

class EventWithType {

  final EventData event;
  final EventTypeData eventTypeData;

  EventWithType(this.event, this.eventTypeData);
  
}

but I know I am doing something wrong I am just not sure how to do joins, or even if I am using the correct join?

TIA x10sion

Answer 1

Supposing we have such tables named as events, eventTypes & users, we can write the join as:

Stream<JoinedEvent> getJoinedEvent(eventId) {
  final query = (select(events)
    ..whereSamePrimaryKey(EventsCompanion(id: Value(eventId))))
      .join([
    innerJoin(eventTypes, eventTypes.id.equalsExp(events.eventTypeId)),
    innerJoin(users, users.id.equalsExp(events.userId)),
  ]);
  return query.watchSingle().map((typedResult) {
    return JoinedEvent(
      event: typedResult.readTable(events),
      user: typedResult.readTable(users),
      eventType: typedResult.readTable(eventTypes),
    );
  });
}

with:

class JoinedEvent {
  final Event event;
  final EventType eventType;
  final User user;

  JoinedEvent ({this.event, this.eventType, this.user});
}