Can't make experimentalJoinTables to Work

[ansyori28]

I have these 3 models:

export default class Note extends Model {
  static table = 'notes';

  static associations = {
    note_tags: {type: 'has_many', foreignKey: 'note_id'},
    tags: {type: 'has_many', through: 'note_tags', foreignKey: 'tag_id'},
  };

  @field('title') title!: string;
  @field('content') content?: string;
  @field('color') color?: string | null;
  @readonly @date('created_at') createdAt!: number;
  @readonly @date('updated_at') updatedAt!: number;
  @children('note_tags') noteTags?: NoteTag[];
}

export default class NoteTag extends Model {
  static table = 'note_tags';

  @relation('notes', 'note_id') note!: Note;
  @relation('tags', 'tag_id') tag!: Tag;
  @readonly @date('created_at') createdAt!: Date;
  @readonly @date('updated_at') updatedAt!: Date;
}

export default class Tag extends Model {
  static table = 'tags';

  @field('name') name!: string;
  @children('note_tags') noteTags?: NoteTag[];
  @readonly @date('created_at') createdAt!: Date;
  @readonly @date('updated_at') updatedAt!: Date;
}

I created a function to filter the notes by title and content, and it works. Then I added a column to filter (tags.name):

const { searchQuery } = get();
const notesCollection = database.collections.get<Note>("notes");
const noteTagCollection = database.collections.get<NoteTag>("note_tags");
let notesQuery = notesCollection.query(
  Q.experimentalJoinTables(["note_tags"]),
  Q.sortBy("updated_at", Q.desc)
);
// Apply search query filter for title and content
if (searchQuery) {
  const sanitizedQuery = Q.sanitizeLikeString(searchQuery);
  notesQuery = notesQuery.extend(
    Q.or(
      Q.where("title", Q.like(`%${sanitizedQuery}%`)),
      Q.where("content", Q.like(`%${sanitizedQuery}%`)),
      Q.on(
        "note_tags",
        Q.on('tags', Q.where('name', Q.like(`%${sanitizedQuery}%`))),
      )
    )
  );
}
const notes = await notesQuery.fetch();

When I try to add tags.title into the filter, I'm getting this error: Diagnostic error: To nest Q.on inside Q.and/Q.or you must explicitly declare Q.experimentalJoinTables at the beginning of the query

Then I added 'tags' into experimentalJoinTable list: Q.experimentalJoinTables(["note_tags", "tags"])

Now I'm getting this error: android.database.sqlite.SQLiteException: no such column: tags.tag_id (code 1 SQLITE_ERROR[1]): , while compiling: select distinct "notes".* from "notes" left join "note_tags" on "note_tags"."note_id" = "notes"."id" left join "tags" on "tags"."tag_id" = "notes"."id" where ("notes"."title" like '%Test%' or "notes"."content" like '%Test%' or (("tags"."name" like '%Test%' and "tags"."_status" is not 'deleted') and "note_tags"."_status" is not 'deleted')) and "notes"."_status" is not 'deleted' order by "notes"."updated_at" desc

I didn't know what do I do wrong here, can somebody help me?

Any help will be much appreciated, thanks

[KrisLau]

These issues seem quite similar and might be helpful:

• https://github.com/Nozbe/WatermelonDB/issues/950
• https://github.com/Nozbe/WatermelonDB/issues/1111

I havent used nested Q.on so I can't really give any code examples but you can try bouncing your questions off ChatGPT, it gives some decent advice sometimes (pinch of salt it also gives completely nonexistent methods sometimes 😆 ). I pasted your question and it gave this:

The error you're encountering indicates a problem with the way WatermelonDB is generating the SQL query for your join. Specifically, the issue arises because WatermelonDB is trying to join the tags table using a foreign key (tag_id) that is not present in the notes table directly. Instead, it should be joining through the note_tags table.

Here's how to address this:

1. Correct the Join Path: To join notes to tags through note_tags, you need to make sure your join conditions reflect this. The tags table should be joined through note_tags, not directly with notes.

2. Modify the Query: Adjust your query to ensure that the tags table is joined correctly through the note_tags table.

Here’s an adjusted version of your query:

const { searchQuery } = get();
const notesCollection = database.collections.get<Note>("notes");

let notesQuery = notesCollection.query(
  Q.experimentalJoinTables(["note_tags"]), // Only need to join with note_tags explicitly
  Q.experimentalNestedJoin("note_tags", "tags"), // Nested join to get tags through note_tags
  Q.sortBy("updated_at", Q.desc)
);

// Apply search query filter for title, content, and tag name
if (searchQuery) {
  const sanitizedQuery = Q.sanitizeLikeString(searchQuery);
  notesQuery = notesQuery.extend(
    Q.or(
      Q.where("title", Q.like(`%${sanitizedQuery}%`)),
      Q.where("content", Q.like(`%${sanitizedQuery}%`)),
      Q.on(
        "note_tags",
        Q.on(
          "tags",
          Q.where("name", Q.like(`%${sanitizedQuery}%`))
        )
      )
    )
  );
}

const notes = await notesQuery.fetch();

Key Changes:

• Q.experimentalJoinTables(["note_tags"]): This specifies that you want to join the note_tags table with notes. You only need to directly specify the join with note_tags.
• Q.experimentalNestedJoin("note_tags", "tags"): This tells WatermelonDB to join tags through the note_tags table, which is what your relationship setup implies.

By using Q.experimentalNestedJoin, WatermelonDB correctly understands that tags is a nested join through note_tags rather than a direct join with notes. This should resolve the SQL error you encountered and allow your query to execute correctly.