Overview

A formula property is a read only entity property whose value is computed by a SQL expression at query time rather than stored in its own column. This is typically a SQL literal value, SQL case statement, SQL function or similar. Ebean provides two annotations for this:

• @Formula - we write the physical SQL for the select (and any join), using a ${ta} placeholder for the base table alias. This gives maximum control but is more verbose.
• @Formula2 - we write a logical expression using dot-notation property paths (e.g. parent.familyName). Ebean translates the paths to the correct table aliases and adds the required joins automatically.

Both annotations produce read only properties and are included in queries by default (see Default inclusion and @Transient below to make them opt-in instead).

@Formula

With @Formula we supply the SQL select fragment, and an optional join. We use ${ta} wherever we need the base table alias of the entity.

@Entity
public class ParentPerson {

  // aggregation via a derived join, ${ta} is the base table alias
  @Formula(select = "coalesce(f2.child_count, 0)",
           join = "left join (select parent_id, count(*) as child_count"
                + " from child group by parent_id) f2 on f2.parent_id = ${ta}.id")
  Integer childCount;

  // coalesce across a joined table using an explicit join alias (j1)
  @Formula(select = "coalesce(${ta}.family_name, j1.family_name)",
           join = "join parent_person j1 on j1.id = ${ta}.parent_id")
  String effectiveFamilyName;
}

Notes on @Formula:

• The join string must start with join or left join.
• We manage the join aliases (j1, f2, etc) ourselves and reference them in select.
• @Formula is @Repeatable and supports a platforms() restriction.

@Formula2

@Formula2 is the easier, path based replacement for @Formula. We write the expression using property paths and Ebean resolves each path to the correct table alias, adding the joins it needs automatically.

@Entity
public class ParentPerson {

  @ManyToOne
  GrandParentPerson parent;

  String familyName;

  // Ebean automatically left joins 'parent' and resolves the aliases
  @Formula2("coalesce(familyName, parent.familyName)")
  String derivedFamilyName;
}

A query selecting derivedFamilyName produces (roughly):

select t0.id, coalesce(t0.family_name, t1.family_name)
from parent_person t0
left join grand_parent_person t1 on t1.id = t0.parent_id

Multi-level paths join through each step:

// joins parent and parent.parent automatically
@Formula2("coalesce(familyName, parent.familyName, parent.parent.familyName)")
String deepFamilyName;

@Formula2 works wherever a normal property does - the required joins are added automatically in each case:

// selected explicitly
DB.find(ParentPerson.class).select("derivedFamilyName").findList();

// used in where (auto-joins even when not selected)
DB.find(ParentPerson.class).where().eq("derivedFamilyName", "Smith").findList();

// used in order by
DB.find(ParentPerson.class).orderBy("derivedFamilyName").findList();

// referenced via a path from another bean
DB.find(ChildPerson.class).where().eq("parent.derivedFamilyName", "Smith").findList();

It also resolves correctly inside nested fetch() joins, so a @Formula2 on a fetched association is computed with its own joins relative to that association.

Notes on @Formula2:

• The expression supports any SQL function whose arguments are logical property paths.
• @Formula2 supports a platforms() restriction.
• No ${ta} and no hand written join - that is the point of @Formula2.

Default inclusion and @Transient

Both annotations are included in queries by default (just like a normal mapped property). When no explicit select()/fetch() is given, the formula - and for @Formula2 the joins it requires - are added to the query.

Add @Transient to make the formula opt-in: it is then not selected by default and must be requested explicitly via select() or fetch(). Do this when the formula (or the joins it needs) is relatively expensive.

// not selected by default, must be requested explicitly
@Transient
@Formula2("coalesce(familyName, parent.familyName)")
String lazyDerivedFamilyName;

DB.find(ParentPerson.class)
  .select("lazyDerivedFamilyName")   // explicitly included, join auto-added
  .findList();

Which should we use?

Prefer @Formula2 for expressions over property paths (coalesce/case/functions across associations). It is shorter, refactor-friendly, and the joins stay correct as the model changes.

Use @Formula when we need raw SQL that does not map cleanly to property paths - for example a derived aggregate sub-select / dynamic view, or vendor-specific SQL.