I needed help in understanding the ORM API.

[Vedsaga]

So, we have below SQL table

CREATE TABLE IF NOT EXISTS route_time_tracking_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    profile_id TEXT NOT NULL,
    route_path TEXT NOT NULL, 
    date DATE NOT NULL, 
    time_spent_seconds INTEGER NOT NULL DEFAULT 0 CHECK (time_spent_seconds >= 0),
    UNIQUE(profile_id, route_path, date)
);

and below is query

SELECT 
    parent.id,
    parent.date,
    parent.profile_id,
    parent.route_path,
    COALESCE(parent.time_spent_seconds, 0) + 
    COALESCE(SUM(CASE 
        WHEN child.route_path LIKE parent.route_path || '%' 
        AND child.route_path != parent.route_path 
        THEN child.time_spent_seconds 
        ELSE 0 
    END), 0) as total_time
FROM route_time_tracking_table parent
LEFT JOIN route_time_tracking_table child 
    ON child.profile_id = parent.profile_id 
    AND child.date = parent.date
    AND child.route_path LIKE parent.route_path || '%'
    AND child.route_path != parent.route_path
WHERE 
    parent.date BETWEEN :start_date AND :end_date
    AND parent.profile_id = :profile_id
    AND parent.route_path = :parent_route
GROUP BY 
    parent.id,
    parent.date,
    parent.profile_id,
    parent.route_path
ORDER BY parent.date;

I am wondering how could we write in DART ORM? I have attempted something like below but have no success.

  Future<List<RouteTimeTrackingTableData>> getRouteTimeTracking({
    required String profileId,
    required DateTime startDate,
    required DateTime endDate,
    required String parentRoute,
  }) async {
    // Using Drift's DSL (Domain Specific Language)
    final parent = alias(db.routeTimeTrackingTable, 'parent');
    final child = alias(db.routeTimeTrackingTable, 'child');

    final result = await (select(parent)
          ..addColumns(
            [
              coalesce([parent.timeSpentSeconds, const Constant(0)]) +
                  coalesce(
                    [
                      child.timeSpentSeconds.sum(
                        filter: child.routePath.equalsExp(parent.routePath) &
                            child.profileId.equalsExp(parent.profileId) &
                            child.date.equalsExp(parent.date),
                      ),
                      const Constant(0),
                    ],
                  ),
            ],
          )
          ..join(
            [
              leftOuterJoin(
                child,
                child.profileId.equalsExp(parent.profileId) &
                    child.date.equalsExp(parent.date) &
                    child.routePath.like('${parent.routePath}/%') &
                    child.routePath.isNotExp(parent.routePath),
              ),
            ],
          ).groupBy(
            [parent.date],
            having: (parent.routePath.equals(parentRoute) &
                parent.profileId.equals(profileId) &
                parent.date.isExp(child.date)),
          )
          ..where(
            (tbl) =>
                tbl.date.isBetween(Variable(startDate), Variable(endDate)) &
                tbl.profileId.equals(profileId) &
                tbl.routePath.equals(parentRoute),
          )
          ..orderBy([
            (t) => OrderingTerm(expression: t.date, mode: OrderingMode.desc),
          ]))
        .get();

    return result;
  }

The dart ORM seems to be converted into the ( have intercepted this SQL using the break point.

SELECT * FROM "route_time_tracking_table" "parent" WHERE JULIANDAY("date") BETWEEN JULIANDAY(?) AND JULIANDAY(?) AND "profile_id" = ? AND "route_path" = ? ORDER BY "date" DESC;

Apologizes for tagging, could you please give some hint ?
cc:
@simolus3

[Vedsaga]

For time being using custom statement, while it works for the single path using async, it does seems to fail for stream.

below is stream statement,

  Stream<List<RouteTimeSpentRecord>> listenTodayRoutesTimeSpent({
    required String profileId,
    required Set<String> routePaths,
    required bool shouldCountTimeSpentChildren,
  }) async* {
    final today = DateTime.now();
    final todayDate = DateTime(today.year, today.month, today.day);

    final customSQL = customSelect(
      '''
      SELECT 
          parent.id,
          parent.date,
          parent.profile_id,
          parent.route_path,
          COALESCE(parent.time_spent_seconds, 0) + 
          COALESCE(
              CASE 
                  WHEN ?4 = 1 THEN 
                      SUM(
                          CASE 
                              WHEN child.route_path LIKE parent.route_path || '/%'
                              THEN child.time_spent_seconds 
                              ELSE 0 
                          END
                      )
                  ELSE 0
              END, 0) as total_time
      FROM route_time_tracking_table parent
      LEFT JOIN route_time_tracking_table child 
          ON child.profile_id = parent.profile_id 
          AND child.date = parent.date
          AND child.route_path LIKE parent.route_path || '/%'
      WHERE 
          parent.date = ?1  
          AND parent.profile_id = ?2
          AND parent.route_path IN (?3)
      GROUP BY 
          parent.id, parent.date, parent.profile_id, parent.route_path
      ORDER BY parent.date;
    ''',
      variables: [
        Variable.withDateTime(todayDate),
        Variable.withString(profileId),
        Variable(routePaths),
        Variable.withBool(shouldCountTimeSpentChildren),
      ],
      readsFrom: {db.routeTimeTrackingTable},
    );
    
    ...
    
    
    yield* dailyTimeEntries;
 }

stacktrace,

E/flutter (24778): [ERROR:flutter/runtime/dart_vm_initializer.cc(40)] Unhandled Exception: Invalid argument (params[3]): Allowed parameters must either be null or bool, int, num, String or List<int>.: _Set len:1
E/flutter (24778): #0      StatementImplementation._bindParam (package:sqlite3/src/implementation/statement.dart:239:14)
E/flutter (24778): #1      StatementImplementation._bindIndexedParams (package:sqlite3/src/implementation/statement.dart:188:7)
E/flutter (24778): #2      StatementImplementation._bindParams (package:sqlite3/src/implementation/statement.dart:250:9)
E/flutter (24778): #3      StatementImplementation.selectWith (package:sqlite3/src/implementation/statement.dart:278:5)
E/flutter (24778): #4      CommonPreparedStatement.select (package:sqlite3/src/statement.dart:95:12)
E/flutter (24778): #5      Sqlite3Delegate.runSelect (package:drift/src/sqlite3/database.dart:161:27)
E/flutter (24778): #6      _BaseExecutor.runSelect.<anonymous closure> (package:drift/src/runtime/executor/helpers/engines.dart:77:19)
E/flutter (24778): #7      _BaseExecutor._synchronized (package:drift/src/runtime/executor/helpers/engines.dart:61:20)
E/flutter (24778): #8      _BaseExecutor.runSelect (package:drift/src/runtime/executor/helpers/engines.dart:74:26)
E/flutter (24778): #9      LazyDatabase.runSelect (package:drift/src/utils/lazy_database.dart:86:22)
E/flutter (24778): #10     CustomSelectStatement._executeRaw.<anonymous closure> (package:drift/src/runtime/query_builder/statements/select/custom_select.dart:51:45)
E/flutter (24778): #11     DatabaseConnectionUser.doWhenOpened.<anonymous closure> (package:drift/src/runtime/api/connection_user.dart:171:16)
E/flutter (24778): #12     _rootRunUnary (dart:async/zone.dart:1422:47)
E/flutter (24778): #13     _CustomZone.runUnary (dart:async/zone.dart:1324:19)
E/flutter (24778): <asynchronous suspension>
E/flutter (24778): #14     NonNullableCancellationExtension.resultOrNullIfCancelled (package:drift/src/runtime/cancellation_zone.dart:65:24)
E/flutter (24778): <asynchronous suspension>
E/flutter (24778): #15     QueryStream.fetchAndEmitData (package:drift/src/runtime/executor/stream_queries.dart:331:20)
E/flutter (24778): <asynchronous suspension>
E/flutter (24778): 

[simolus3]

join() returns a new statement with a slightly different API, so it can't be used with the .. call operator. Could you try if this works (I didn't check the rest of the statement):

Future<List<({RouteTimeTrackingTableData tracking, int totalTime})>>
    getRouteTimeTracking({
  required String profileId,
  required DateTime startDate,
  required DateTime endDate,
  required String parentRoute,
}) async {
  // Using Drift's DSL (Domain Specific Language)
  final parent = alias(routeTimeTrackingTable, 'parent');
  final child = alias(routeTimeTrackingTable, 'child');

  final join = select(parent).join(
    [
      leftOuterJoin(
        child,
        child.profileId.equalsExp(parent.profileId) &
            child.date.equalsExp(parent.date) &
            child.routePath.like('${parent.routePath}/%') &
            child.routePath.isNotExp(parent.routePath),
        useColumns: false,
      ),
    ],
  );

  final totalTime = coalesce([parent.timeSpentSeconds, const Constant(0)]) +
      coalesce(
        [
          child.timeSpentSeconds.sum(
            filter: child.routePath.equalsExp(parent.routePath) &
                child.profileId.equalsExp(parent.profileId) &
                child.date.equalsExp(parent.date),
          ),
          const Constant(0),
        ],
      );

  final result = await (join
        ..addColumns(
          [totalTime],
        )
        ..groupBy(
          [parent.date],
          having: (parent.routePath.equals(parentRoute) &
              parent.profileId.equals(profileId) &
              parent.date.isExp(child.date)),
        )
        ..where(
          parent.date.isBetween(Variable(startDate), Variable(endDate)) &
              parent.profileId.equals(profileId) &
              parent.routePath.equals(parentRoute),
        )
        ..orderBy([
          OrderingTerm(expression: parent.date, mode: OrderingMode.desc),
        ]))
      .map((row) {
    return (tracking: row.readTable(parent), totalTime: row.read(totalTime)!);
  }).get();

  return result;
}

[Vedsaga]

It seems to pointing some error by analyzer dart code is not compiling. Btw no worries, for time being it's ok to use the SQl statement,

could you please help why getting error, I know that there is no Variable to pass the Iterable, so what could we done to fix it?

#3344 (comment)

[simolus3]

It seems to pointing some error by analyzer dart code is not compiling. Btw no worries, for time being it's ok to use the SQl statement,

I have updated the snippet. By the way, it looks like the return type is off (or how would you include the custom expression total_time) otherwise?

could you please help why getting error, I know that there is no Variable to pass the Iterable, so what could we done to fix it?

This is not fixable with raw customQueries because SQLite doesn't support array variables. However, you can use compiled queries (these work especially well in drift files if you're already using SQL to define your tables). Drift recognizes when a variable is used in a place where an array is expected and then applies a transformation to allow multiple variables to be used as an emulation for array variables. This requires analysis at build time though, so it's not available with customSelect alone.

And of course, if you're using the Dart query builder you can always use isIn as a filter.

[Vedsaga]

I have updated the snippet.

Thank you. Btw, it seems to still result in no output, anyway for time being using drift file, so going to move the query in that file.

By the way, it looks like the return type is off (or how would you include the custom expression total_time) otherwise?

Was using custom model to map it 😅 .

Thank you again for this great package :)

[Vedsaga]

getOnDateTimeSpentForRoutes(
    REQUIRED :date AS DATE,
    REQUIRED :profile_id AS TEXT,
    REQUIRED :route_paths AS List<TEXT>,
    REQUIRED :include_subroutes AS BOOLEAN
):
SELECT
    parent.id,
    parent.date,
    parent.profile_id,
    parent.route_path,
    COALESCE(parent.time_spent_seconds, 0) +
    COALESCE(
        CASE
            WHEN :include_subroutes THEN
                SUM(
                    CASE
                        WHEN child.route_path LIKE parent.route_path || '/%'
                        THEN child.time_spent_seconds
                        ELSE 0
                    END
                )
            ELSE 0
        END, 0) as total_time
FROM route_time_tracking_table parent
LEFT JOIN route_time_tracking_table child
    ON child.profile_id = parent.profile_id
    AND child.date = parent.date
    AND child.route_path LIKE parent.route_path || '/%'
WHERE
    parent.date = :date
    AND parent.profile_id = :profile_id
    AND parent.route_path IN :route_paths
GROUP BY
    parent.id, parent.date, parent.profile_id, parent.route_path
ORDER BY parent.date;

Is :route_paths not being correctly passed?

Could you please point what could be correct approach, didn't found any approach method in array section or may be I didn't understood
https://drift.simonbinder.eu/sql_api/drift_files/#arrays

cc:
@simolus3

[simolus3]

Is :route_paths not being correctly passed?

We don't support explicitly defining types as arrays yet, and AS TEXT doesn't work either. Looks like the only option at the moment is to not specify the type for :route_paths (drift will infer it correctly though).

Btw, it seems to still result in no output, anyway for time being using drift file, so going to move the query in that file.

If you have a small working data set to try (as insert statements for example), it would be easier for me to reproduce and I could test the snippets :D

[Vedsaga]

Thank you, don't want to bother you any further 😅 . Job is done with custom query. Not specific the type did the trick and able to compile.

…

On Tue, 19 Nov, 2024, 12:14 am Simon Binder, ***@***.***> wrote: Is :route_paths not being correctly passed? We don't support explicitly defining types as arrays yet, and AS TEXT doesn't work either. Looks like the only option at the moment is to not specify the type for :route_paths (drift will infer it correctly though). Btw, it seems to still result in no output, anyway for time being using drift file, so going to move the query in that file. If you have a small working data set to try (as insert statements for example), it would be easier for me to reproduce and I could test the snippets :D — Reply to this email directly, view it on GitHub <#3344 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AOCYWCFQFQQTRQPFNSVE7X32BIYRZAVCNFSM6AAAAABR7B6LMWVHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTCMRZGU3DEMI> . You are receiving this because you authored the thread.Message ID: ***@***.***>