Moor: [Question] - join three tables

[angel1st]

Hi @simolus3 ,
Here are my table descriptions:

enum LoggedActivityEnum {
  watering,
  soaking,
  mist,
  fertilize,
  repot,
  location,
  rotate,
  note,
  image
}

@DataClassName("LoggedActivity")
class LoggedActivities extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get plantId =>
      integer().customConstraint('REFERENCES plants(id) ON DELETE CASCADE')();
  DateTimeColumn get created => dateTime().withDefault(currentDateAndTime)();
  IntColumn get activityType =>
      intEnum<LoggedActivityEnum>().withDefault(Constant(0))();
}

class Notes extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get loggedActivityId =>
      integer().customConstraint('REFERENCES logged_activities(id) ON DELETE CASCADE')();
  TextColumn get note => text()();
}

class Images extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get loggedActivityId =>
      integer().customConstraint('REFERENCES logged_activities(id) ON DELETE CASCADE')();
  TextColumn get image => text().map(const ImageConverter()).nullable()();
  TextColumn get thumbnail => text().map(const ImageConverter()).nullable()();
}

few things to clarify:

• as you can see, activityType might be an image, note, or some other type from LoggedActivityEnum
• each LoggedActivities record might have one : one relation with a record from either Images or Note tables
• each LoggedActivities record might have a relation with one image and one note record as well
• each LoggedActivities record might have a relation with neither Images nor Notes tables

My questions:

1. How the inner joins should look like if I need to join LoggedActivities, Notes, and Images? I guess, looking at your example if I have to join LoggedActivities and Note only, it should be something like that:

class ActivityWithNote {
  ActivityWithNote (this.loggedActivity, this.note);

  final LoggedActivity loggedActivity;
  final Note note;
}

Stream<List<ActivityWithNote>> entriesWithCategory() {
  final query = select(loggedActivities).join([
    leftOuterJoin(notes, notes.loggedActivityId.equalsExp(loggedActivities.id)),
  ]);
}

It is not clear to me however in case I have one more table - Images, where to place the next join?

1. How can I select from the Images table only thumbnail field, but not and image one? I would like to make select smaller and open the real image only in case the user clicks on the thumbnail.

[simolus3]

You can just add another join:

class ActivityWithNote {
  ActivityWithNote (this.loggedActivity, {this.note, this.image});

  final LoggedActivity loggedActivity;
  final Note note;
  final Image image;
}

Stream<List<ActivityWithNote>> entriesWithCategory() {
  final query = select(loggedActivities).join([
    leftOuterJoin(notes, notes.loggedActivityId.equalsExp(loggedActivities.id)),
    leftOuterJoin(images, images.loggedActivityId.equalsExp(loggedActivities.id)),
  ]).map((row) {
    return ActivityWithNote(row.readTable(loggedActivities), note: row.readTable(notes), image: row.readTable(images));
  });
}

How can I select from the Images table only thumbnail field, but not and image one?

When used inside a join, you can set useColumn: false:

Stream<List<ActivityWithNote>> entriesWithCategory() {
  final thumbnail = images.thumbnail;

  final query = select(loggedActivities).join([
    leftOuterJoin(notes, notes.loggedActivityId.equalsExp(loggedActivities.id)),
    leftOuterJoin(images, images.loggedActivityId.equalsExp(loggedActivities.id), useColumns: false),
  ]);
 query.addColumns([thumbnail]);
}

In this query, you can't use row.readTable(images) anymore - use row.read(thumbnail) instead.

[angel1st]

Thanks @simolus3! I really appreciate your prompt and swift response!

[angel1st]

@simolus3 - re selecting thumbnail only from Images table, I have one additional implication - as you can see from the table definition, I use ImageConverter for this column: TextColumn get thumbnail => text().map(const ImageConverter()).nullable()();,
so basically, final GeneratedTextColumn thumbnail = images.thumbnail; has different type than row.read(thumbnail), where it expects Uint8List. So how to convert GeneratedTextColumn to Uint8List?

[simolus3]

At the moment, type converters only work for full tables since they're applied in generated code.

You could replace row.read(thumbnail) with const YourTypeConverter().mapToDart(row.read(thumbnail)) to apply it manually.