Statements with a generated api: Filtering on JSON array

[llfbandit]

Hi,

Given this request:
SELECT DISTINCT * FROM motiveCategoryRows, json_each(motiveCategoryRows.locationIds) WHERE json_each.value = :locationId
where locationIds is JSON array and locationId is an int.

I tried to generate the corresponding method which was apparently a success but with locationId as a String parameter.
When executing this, no record was pulled from DB.

Using customSelect directly worked by modifying locationId to an int variable like so:

return customSelect(
      'SELECT DISTINCT * FROM motiveCategoryRows, json_each(motiveCategoryRows.locationIds) WHERE json_each.value = :locationId',
      variables: [Variable.withInt(locationId)],
      readsFrom: {motiveCategoryRows},
    )
        .map((row) => MotiveCategory(id: row.read<int>('id'), ...))
        .get();

Is there a way to tweak the generated code?

[simolus3]

I think the easiest way would be to do WHERE CAST(json_each.value AS INT) = :locationId. In that case, the generator should be smart enough to know that you want an int. For json_each, we don't really know the type otherwise and we fall back to a string.

[llfbandit]

Thanks! This does the trick!