WatermelonDB icon indicating copy to clipboard operation
WatermelonDB copied to clipboard

[Question] Query in associations tables with multiply levels

Open montanhes opened this issue 2 years ago • 0 comments

I'm trying to make a query that return the clients that have a city name:

Client Model:

import { Q } from "@nozbe/watermelondb";
import { Model } from "@nozbe/watermelondb";
import { ContactType, TableNames } from "../../utils/Constants";
import { field, lazy, immutableRelation, children } from "@nozbe/watermelondb/decorators";

export default class ClientModel extends Model {
  static table = TableNames.CLIENTS;

  static associations = {
    address: { type: 'belongs_to', key: 'address_id' },
  };

  @field('name')
  name!: string;

  @field('address_id')
  address_id!: string;

  @immutableRelation(TableNames.ADDRESSES, 'address_id') address;
}

Address Model:

import { Model } from "@nozbe/watermelondb";
import { TableNames } from "../../utils/Constants";
import { field, immutableRelation } from "@nozbe/watermelondb/decorators";

export default class AddressModel extends Model {
  static table = TableNames.ADDRESSES;

  static associations = {
    "city": { type: 'belongs_to', key: 'city_id' },
  };

  @field("zip")
  zip!: string;

  @field("city_id")
  city_id!: string;
  
  @immutableRelation(TableNames.CITIES, 'city_id') city;
}

City Model:

import { Model } from "@nozbe/watermelondb";
import { TableNames } from "../../utils/Constants";
import { field } from "@nozbe/watermelondb/decorators";

export default class CityModel extends Model {
  static table = TableNames.CITIES;

  @field("name")
  name!: string;
}

Filter:

const sanitizedSearch = Q.like(`%${Q.sanitizeLikeString(search)}%`);

const filtered = await database.get<ClientModel>(TableNames.CLIENTS).query(
  Q.experimentalJoinTables([TableNames.ADDRESSES]),
  Q.experimentalNestedJoin(TableNames.ADDRESSES, TableNames.CITIES),
  Q.or(
    Q.where('name', sanitizedSearch),
    Q.where('document', sanitizedSearch),
    Q.where('nickname', sanitizedSearch),
    Q.on(
      TableNames.ADDRESSES,
      Q.on(TableNames.CITIES, Q.where('name', sanitizedSearch))
    )
  ),
  Q.where('id', Q.notEq("1")),
  Q.sortBy('name', Q.asc),
).fetch()

Everything seems to work as expected, only the query by the city name is not working.

Can someone help about this?

montanhes avatar Nov 27 '23 19:11 montanhes