How to get a default value based on another column (and maybe the same column)

[OliverRhyme]

Hi, for example I have a table value named position (IntColumn), and I want its default value to be the max (or 0 if null) of the position column itself. I tried withDefault(position.max()) but it causes StackOverflowError.

Maybe my implementation is wrong or maybe it is not currently possible. I want to know how to do it or possible workaround. Thank you

[simolus3]

Unfortunately, sqlite only supports constant values for defaults, so you can't use column values in defaults. I'm not aware of any moor api which could be used to model this either, so I think this impossible.

Can you implement this check somewhere in your app before constructing the companion to insert?

[OliverRhyme]

You mean execute max() query first before the actual insert operation??

[simolus3]

Essentially you want the column to be non-negative, right? Can you just do that check in Dart and set it to 0 if its not? The only way for that column to be negative is if you explicitly set it to a negative value.

[OliverRhyme]

No, I want the default value to be the "max" value of the same column. And only putting zero if it is the first item (null max)

[simolus3]

Ah, I see. That's still not possible to do in a table declaration because a default value must be constant.

You could use something like this:

final defaultPosition = coalesce<int>([
  subqueryExpression(selectOnly(yourTable)..addColumns([yourTable.position.max()])),
  const Constant(0),
]);

MyTableCompanion.custom(
  position: defaultPosition,
  // ... rest of your fields
),

To make that easier to use, you could also add an extension on the regular companion:

extension on MyTableCompanion {
  Insertable<MyTable> withDefaultPosition() {
    final defaultPosition = coalesce(...);
    final columns = toColumns(false);
    return RawValuesInsertable({
      'position': defaultPosition,
      ...columns,
    });
  }
}

With that, you can just use the regular companions and call withDefaultPosition before an insert.

[OliverRhyme]

Okay copy, Thank youu