Possibilty of use parameters with in sql clause

[busslina]

I'm trying multiple ways, none worked:

Future<Set<JobSchedule>> Function() loadJobSchedulesSqlCapsule(
  CapsuleHandle use,
) {
  final executeDbQuery = use(executeDbQueryCapsule);
  final glovoShiftSlots = use(glovoShiftSlotsCapsule);

  return () => executeDbQuery(
        Sql.named(
          'SELECT * FROM $_schema.$_table WHERE glovo_shift_slot_id IN (@glovoShiftSlots)',
        ),
        parameters: {
          'glovoShiftSlots': glovoShiftSlots
              .getAll()
              .map((glovoShiftSlot) => glovoShiftSlot.id)
              .map((id) => Uuid(id)),
          // .reduceSecure((value, element) => '$value, $element', ''),
          // .toList(),
        },
      ).then((result) => result.map(JobSchedule.fromResultRow).toSet());
}

[busslina]

My current workaround is this but it would be cool to be able to do it via parameters:

Future<Set<JobSchedule>> Function() loadJobSchedulesSqlCapsule(
  CapsuleHandle use,
) {
  final executeDbQuery = use(executeDbQueryCapsule);
  final glovoShiftSlots = use(glovoShiftSlotsCapsule);

  final log = use(logCapsule)('Load job schedules (SQL)');

  return () {
    final ids = glovoShiftSlots
        .getAll()
        .map((glovoShiftSlot) => glovoShiftSlot.id)
        .map((id) => '\'$id\'')
        .reduceSecure((value, element) => '$value, $element', '');

    log.info('Ids: $ids');

    return executeDbQuery(
      'SELECT * FROM $_schema.$_table WHERE glovo_shift_slot_id IN ($ids)',
    ).then((result) => result.map(JobSchedule.fromResultRow).toSet());
  };
}

[isoos]

@busslina: I think the @param::_uuid type specification + List<String> parameter would create a uuid TypedValue and that should probably work in your query. Could you please check it?

However, even if this works, we have a very limited story for array types and some other base types, and should probably introduce more special objects that are handled automatically.

[busslina]

@isoos I'm getting this error:

Future<Set<JobSchedule>> Function() loadJobSchedulesSqlCapsule(
  CapsuleHandle use,
) {
  final executeDbQuery = use(executeDbQueryCapsule);
  final glovoShiftSlots = use(glovoShiftSlotsCapsule);

  return () {
    // Current & future glovo shift slot ids
    final ids = glovoShiftSlots.collection
        .getAll()
        .map((glovoShiftSlot) => glovoShiftSlot.id)
        .toList();

    return executeDbQuery(
      Sql.named(
        'SELECT * FROM $_schema.$_table WHERE glovo_shift_slot_id IN (@ids::_uuid)',
        // 'SELECT * FROM $_schema.$_table WHERE glovo_shift_slot_id IN @ids::_uuid',
      ),
      parameters: {
        'ids': ids,
      },
    ).then((result) => result.map(JobSchedule.fromResultRow).toSet());
  };
}

(Severity.error 42883: operator does not exist: uuid = uuid[] hint: No operator matches the given name and argument types. You might need to add explicit type casts.

ids is List<String>.

[isoos]

This is an LLM reply, but I think it has merits:

PostgreSQL's IN operator expects a value list, not an array, so passing an array parameter to IN (...) will always fail with a type mismatch. The idiomatic fix is = ANY(...), which does accept an array:

SELECT * FROM t WHERE id = ANY(@ids::_uuid)
parameters: {'ids': ['uuid-1', 'uuid-2']}

The _uuid annotation tells the driver to encode the List as uuid[], and = ANY(uuid[]) is semantically equivalent to IN (...) for membership tests.

[isoos]

Closing as I don't see any actionable item here.