Using RdbPredicates for Complex Queries in RDB Store

#database #typescript #mobile #tutorial

Read the original article：Using RdbPredicates for Complex Queries in RDB Store

Requirement Description

Developers often need to perform complex database queries such as filtering, sorting, pagination, and pattern matching.

Instead of writing raw SQL strings, HarmonyOS provides RdbPredicates, a builder class that simplifies query construction in ArkTS for the RDB Store.

This improves readability, reduces errors, and enhances maintainability.

Background Knowledge

RDB Store: HarmonyOS’s relational database solution, built on top of SQLite.
RdbPredicates: A query builder API that allows developers to construct WHERE, ORDER BY, LIMIT, and other clauses programmatically.
Key APIs:
- equalTo, greaterThan, lessThan, between → filtering conditions
- in, like, glob → set membership & pattern matching
- orderByAsc, orderByDesc → sorting
- limitAs, offsetAs → pagination
- groupBy, distinct, indexedBy → aggregation and optimization

Reference: HarmonyOS Data Persistence by RDB Store

Implementation Steps

Initialize RdbStore with a configuration.
Create RdbPredicates for the target table.
Chain conditions such as equality, ranges, and pattern matching.
Add sorting and pagination when required.
Pass the predicates to query, update, or delete.
Process the result set accordingly.

Code Snippet / Configuration

import { relationalStore } from '@kit.ArkData';
import { BusinessError } from '@kit.BasicServicesKit';

const SQL_CREATE_TABLE = `CREATE TABLE IF NOT EXISTS Trips (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  template TEXT,
  start_date INTEGER,
  days INTEGER
);`;

export interface TripModel {
  id: number,
  template: string;
  startDate: string;
  days: number;
}

export class AppDatabase {
  private database?: relationalStore.RdbStore;

  private async initializeRDB(context: Context): Promise<void> {
    const store = await relationalStore.getRdbStore(context, {
      name: 'trips_database.db',
      securityLevel: relationalStore.SecurityLevel.S1,
    });

    // When the RDB store is created, the default version is 0.
    if (store.version === 0) {
      await store.executeSql(SQL_CREATE_TABLE);
      store.version = 1;
    }
    this.database = store;
  }

  constructor(context: Context) {
    this.initializeRDB(context).then(() => {
      console.info(`Succeeded in getting RdbStore, version:${this.database?.version}`);
    }).catch((err: BusinessError) => {
      console.error(`Failed to get RdbStore. Code:${err.code}, message:${err.message}`)
    })
  }

  /**
   * Async method to get Latest Night trip type, that lasted for 2 days, in the current month.
   * 1- Get Night trip type: "Movie Night", "Party Night", etc.
   * 2- that lasted for 2 days.
   * 3- during current month period.
   * @returns { Promise<TripModel> } if successfully found trip or `undefined` if none found.
   */
  async getLatestTwoDayNightTypeTrip(): Promise<TripModel | undefined> {
    if (!this.database) {
      console.error('Failed to get trips, RdbStore is not initialized.');
      return;
    }
    try {
      const now = Date.now();
      const oneMonthAgo = now - 30 * 24 * 60 * 60 * 1000; // last 30 days
      const predicates = new relationalStore.RdbPredicates('Trips')
        .like('template', '%night%') // match templates containing "night", for example: "Movie Night" & "Party Night"
        .and()
        .equalTo('days', 2) // only 2-day trips
        .and()
        .between('start_date', oneMonthAgo, now) // trips started within last 30 days
        .orderByDesc('id') // order by recent fields based on 'id' values
        .limitAs(1); // latest inserted first

      const resultSet = await this.database.query(predicates, ['id', 'template', 'start_date', 'days']);
      if (!resultSet || resultSet.rowCount === 0) {
        return;
      }

      let trip: TripModel | undefined;
      while (resultSet.goToNextRow()) {
        const startDate = new Date(resultSet.getLong(resultSet.getColumnIndex('start_date')));
        trip = {
          id: resultSet.getLong(resultSet.getColumnIndex('id')),
          template: resultSet.getString(resultSet.getColumnIndex('template')),
          startDate: startDate.toLocaleDateString(),
          days: resultSet.getLong(resultSet.getColumnIndex('days')),
        };
      }
      return trip;
    } catch (err) {
      console.error(`Failed to query with RdbPredicates. Code:${err.code}, message:${err.message}`);
      return;
    }
  }
}

Example: Update multiple rows using IN clause

const valueBucket: relationalStore.ValuesBucket = { days: 10 };
await this.database.update(valueBucket, new relationalStore.RdbPredicates('Trips').in('id', [101, 102, 103]));

Example: Deleting records older than the cutoff timestamp

const oneMonthAgo = Date.now() - 30 * 24 * 60 * 60 * 1000;
await this.database.delete(
  new relationalStore.RdbPredicates('Trips')
    .lessThan('start_date', oneMonthAgo)
);

Test Results

Verified that queries return filtered, sorted, and paginated results as expected.
Confirmed in predicates update only the target rows.
Verified delete with range predicates removes only rows matching the condition.

Limitations or Considerations

Performance: Pattern matching with like '%term%' on large tables can be slow without indexes.
Pagination: Always use orderBy... when applying limitAs/offsetAs to ensure deterministic results.
Distributed RDB: Use inDevices() or inAllDevices() if querying across devices to avoid unexpected scans.
Maintainability: Prefer in([...]) over chaining many equalTo conditions.