Functions & Expressions

// Compare fields to each other
Authors.objects.filter(Authors.$.age.gtF(F('minAge')));

// Arithmetic with fields
await Posts.objects
  .filter(Posts.$.id.eq(1))
  .update({'viewCount': F('viewCount') + 1});

// Field arithmetic in annotations
Authors.objects.annotate({
  'age_in_months': F('age') * 12,
});

F('field') + value   // Addition
F('field') - value   // Subtraction
F('field') * value   // Multiplication
F('field') / value   // Division
F('field') % value   // Modulo

// Where updated_at is after created_at
Authors.objects.filter(
  Authors.$.updatedAt.gtField(Authors.$.createdAt)
);

final stats = await Authors.objects.aggregate({
  'total': Count.all(),
  'avg_age': Avg(Authors.$.age.col),
  'max_age': Max(Authors.$.age.col),
  'min_age': Min(Authors.$.age.col),
  'sum_age': Sum(Authors.$.age.col),
});

Count.all()                    // COUNT(*)
Count(Authors.$.id)            // COUNT(id)
Count(Authors.$.id, distinct: true)  // COUNT(DISTINCT id)

Sum(Authors.$.age.col)
Avg(Authors.$.age.col)
Min(Authors.$.createdAt.col)
Max(Authors.$.createdAt.col)

StdDev(Authors.$.age.col)     // Standard deviation
Variance(Authors.$.age.col)   // Variance

// Concatenate all names with comma separator
StringAgg(Authors.$.name.col, ', ')

ArrayAgg(Authors.$.name.col)
ArrayAgg(Authors.$.name.col, distinct: true)

JsonAgg(Authors.$.name.col)

Coalesce([Authors.$.nickname.col, Authors.$.name.col])

// With default value
Coalesce.withDefault(Authors.$.nickname.col, 'Anonymous')

NullIf(Authors.$.status.col, Value('inactive'))

Greatest([Authors.$.score1.col, Authors.$.score2.col, Authors.$.score3.col])
Least([Authors.$.price1.col, Authors.$.price2.col])

Case(
  [
    When(Authors.$.age.lt(18), then: Value('minor')),
    When(Authors.$.age.lt(65), then: Value('adult')),
  ],
  orElse: Value('senior'),
)

final results = await Authors.objects
  .annotate({
    'age_group': Case(
      [
        When(Authors.$.age.lt(18), then: Value('minor')),
        When(Authors.$.age.lt(65), then: Value('adult')),
      ],
      orElse: Value('senior'),
    ),
  })
  .toList();

Length(Authors.$.name.col)

Lower(Authors.$.name.col)
Upper(Authors.$.name.col)

Trim(Authors.$.name.col)

Substr(Authors.$.name.col, 1, 10)  // Start at 1, length 10

Concat([
  Authors.$.firstName.col,
  Value(' '),
  Authors.$.lastName.col,
])

Replace(Authors.$.bio.col, 'old', 'new')

CurrentDate()   // Current date only
Now()           // Current timestamp

Extract.year(Authors.$.createdAt.col)
Extract.month(Authors.$.createdAt.col)
Extract.day(Authors.$.createdAt.col)
Extract.hour(Authors.$.createdAt.col)
Extract.minute(Authors.$.createdAt.col)
Extract.second(Authors.$.createdAt.col)

// Generic extract
Extract('dow', Authors.$.createdAt.col)  // Day of week

DateTrunc('month', Authors.$.createdAt.col)
DateTrunc('year', Authors.$.createdAt.col)
DateTrunc('day', Authors.$.createdAt.col)

// Group by month
final monthlyStats = await Authors.objects
  .annotate({
    'month': DateTrunc('month', Authors.$.createdAt.col),
    'count': Count.all(),
  })
  .values(['month', 'count'])
  .toList();

Abs(Authors.$.balance.col)

Round(Authors.$.price.col)        // Round to integer
Round(Authors.$.price.col, 2)     // Round to 2 decimal places
Floor(Authors.$.price.col)        // Round down
Ceil(Authors.$.price.col)         // Round up

Power(Authors.$.base.col, 2)      // Square
Sqrt(Authors.$.value.col)         // Square root

Ln(Authors.$.value.col)           // Natural logarithm
Log(Authors.$.value.col)          // Base-10 logarithm

Authors.objects.filter(
  Length(Authors.$.name.col).gt(10)
);

Authors.objects.filter(
  Extract.year(Authors.$.createdAt.col).eq(2024)
);

final results = await Authors.objects
  .annotate({
    'name_length': Length(Authors.$.name.col),
    'created_year': Extract.year(Authors.$.createdAt.col),
    'full_name': Concat([Authors.$.firstName.col, Value(' '), Authors.$.lastName.col]),
  })
  .toList();

Authors.objects.orderBy(
  Length(Authors.$.name.col).desc()
);

await Authors.objects
  .filter(Authors.$.id.eq(1))
  .update({
    'name': Upper(Authors.$.name.col),
    'viewCount': F('viewCount') + 1,
  });