Best Practices: Virtual Field / select_merge

My app has leases of units. A lease has a move in and a move out date. Depending upon those dates a lease is implicitly “past”, “active”, or “future”. I have composable queries like filter_past_leases and filter_future_leases and such. I would like to have an additional filter query that returns leases AND includes their status.

This could be done with a specialized query that returns leases in a list of maps with something like [%{lease: lease, status: "past"}, ...]. This is mostly fine to work with, but is not composable. I’d rather return a list of Lease structs and have the status included somehow and still be able to further filter the query as needed.

One way to do this is to use a virtual field. Basically, add a virtual status field on a Lease struct. Then a query uses select_merge to merge the calculated status into the Lease struct. This works really well and the query can then be further composed like any other ecto query.

|> preload_lease_unit()
# composable query in a pipeline that fills in "status" field
|> calculate_lease_statuses()
|> filter_leases_by_property(property)

I guess my only hesitancy is that it seems like virtual fields have a bit of a bad reputation. Perhaps that reputation has more to do with overusing virtual fields in cases where a changeset or different struct could be used to encapsulate the logic (e.g. setting a password).

Does using a virtual field and select_merge seem reasonable or should I consider alternate methods?

I should note that I’d like to avoid hard-coding a status because 1) it becomes something that needs to be updated in multiple scenarios and 2) I need to be able to calculate “past”, “active”, and “future” for any given day rather than just the current day (e.g. occupancy 30 days out).

1 Like

Are you sure this is a property that needs to be calculated with SQL? Seems like a plain Elixir function that takes a Lease and a Date and returns "past" | "active" | "future" would do just as well.

Yeah, totally fair question. I have both that plain Elixir function, as well as, SQL queries for those in the specific categories. They have been enough up to this point, but I need to be able to filter and sort the entire collection of leases across multiple properties and these statuses are an important criteria. Being able to do so is also important for various reporting being built out.

Are you thinking there are some potential gotchas/smells to the virtual field / select_merge route I mentioned?