How would you compute individual virtual field values when looping through an association?

Thanks for the insight. I guess I was thinking about reducing semi-duplicate data in the database, and converting the price in cents to a dollar amount felt like an ok’ish thing to do at runtime.

I’ll go with storing both in the database for this scenario.

However, in your second solution where you define those functions, wouldn’t you end up leaking out the context modules responsible for those functions inside of your template? I don’t know enough about Elixir yet to really determine what would happen with that 2nd pattern. I see it returns a map, but is that something you could technically pass into a template from a controller and iterate over just as if it were a list of ecto schema results?

I don’t think there’s a problem with building it at runtime, but if it’s always to be used, then probably it makes sense to have it natively in the record (unless you need to juggle currency conversions or something like that, where the value to display depends on something that you need to fetch at runtime? Which defeats the point of storing it).

Well the idea was to use that in the context where you fetch the course and packages association and not on the template - it does iterate twice on the list in that case though.

Other than that, I can only see the way you’re already doing it. If you could do the operation at the DB level then probably a custom query to the preload along with a select and fragment to populate the returned field would also work? But in this case not sure how to make that work with

You can do the work in sql and just let ecto populate the virtual field from that? I do that excessively. ^.^;

What do you mean by that exactly?

When I preload associations via a join then I often calculate a lot of new values as well, I just add those to the ecto query is all, can be as simple as adding something like virtfield: a.somefield + a.anotherfield or so. Basically just think “How would this be done in SQL”, whatever that is is generally the better answer as SQL is the data translation language. :slight_smile:

Do you have a concrete code example of that?

Poke me on tuesday and I’ll be back at work then to grab actual code. :slight_smile:

But in general, if you have many rows to few records (otherwise use a join if it’s few rows preloading to many records), it’s something like this from memory (and of course it works with join preloads too via subquery’s):

Repo.preload(list_of_records, [
  thing_to_preload: from(a in thingy, select: merge(c, %{the_virtual_field: do_whatever_here})),
  # Repeat above for however many you want to preload

It’s documented in the docs though very sporadically (which makes it a bit hard to piece together if you don’t know what you are looking for, PR’s welcome though!). :slight_smile:

1 Like

To expand on what @OvermindDL1 said, something like this should work:

preload_query = from(p in Packages, select: %{p | price: fragment("trunc((?::numeric / 100), 2)::text", p.price_in_cents)})

|> Ecto.Query.preload([packages: ^preload_query])
|> Repo.get!(id) 

But this is just a string populating the virtual field.
I never used the Money lib, but I just took a look and it seems it’s just a struct with a value in cents and a currency atom, both of which have ecto types counterparts , so probably you could do the preload query as:

preload_query = from(p in Packages, select: %{p | price: type(p.price_in_cents, Money.Ecto.Type)}})


preload_query = from(p in Packages, select: %{p | price: %Money{amount: p.price_in_cents, currency: type(^:USD, Money.Currency.Ecto.Type)}})

I also imagine a course would have a very few number of packages, so iterating twice on the list just for the sake of readibility and separating the context from the template logic won’t incur any measurable performance hit and the code will be slightly more straightforward

1 Like

Should the price_in_cents field really just be:

field :price, MyApp.Money, source: :price_in_cents

where MyApp.Money is a custom ecto type that just does this transformation all the time.

1 Like

Perhaps? I never used source, I was just looking at it from the point of view of populating a virtual field at query - if that works then it’s definitively better…

Well, what I’m getting at is that it doesn’t seem like the actual raw price_in_cents value is all that useful, since what you want is a money struct. If all you want is a money struct, structure the ecto schema to give that to you all the time via a custom type.


I agree - I just hadn’t realised you could have a virtual field with a source pointing to a row field and it would populate it

1 Like

Well, what I’m getting at is that it doesn’t seem like the actual raw price_in_cents value is all that useful, since what you want is a money struct

I haven’t implemented everything yet, but I was imagining this:

  • Prices are stored in the database as cents, mainly to avoid all sorts of float rounding issues since I would be multiplying by other numbers (such as to apply percent based discounts).

  • All types of money based calculations are done on the cents values.

  • Course instructor inputs package prices as dollar amounts. It gets converted to cents before being saved to the database.

  • The course description page always shows amounts in dollars and likely everywhere else on the website.

  • The price in cents would also likely end up being submit as a hidden form field value in the checkout form and then I would get the “real” prices from the DB, apply any discounts and ensure it matches what was submit by the user.

For now everything is in USD, but I kind of like the idea of being able to change that without it being hard coded as dollars in the database.

With that said, price in cents seems pretty important but so is the price. Although it seems ensuring the price is always populated as a virtual attribute is becoming a massive headache. With Ruby for example I would just hang a .price method off the package and calculate it from cents to dollars once in the model and display the .price whenever I needed it. Then it works the same if I’m accessing 1 package or looping over a list of packages (ie. it’s always computed), even if I’m accessing it in a model, controller or view.

1 Like

To be clear, it isn’t strictly a virtual field in this case. Source just let’s you name a field differently than the underlying column. It isn’t virtual though cause it is backed by a real column, whereas a virtual field is not.


Just to make sure I understand - the field (the one where we’re defining a source), isn’t persisted correct? It uses the source to figure out the underlying column we want and then when loading the record allows ecto to automatically cast the source column value into whatever type it defines for that field?

1 Like

If you haven’t actually implemented this yet I’d just use and not try to implement this all yourself. Money is complicated.

1 Like

I am using that package. I implemented everything talked about so far, but it’s not entirely “done done”. Still hung up on populating the virtual field in a way that’s not gross.

1 Like

Where does the virtual field come in? The package docs all use real fields.

1 Like

The persisted price_in_cents is just a regular integer field (code is in the original post). I was using the virtual field of price to take that persisted cents and then convert it to Money (which stores both the currency prefix and the amount in dollars since I am using USD as the prefix). I never used Money’s Ecto specific types.

Is that not the way to approach it?


There’s nothing wrong with this approach, but I think it’s worth highlighting the difference between the type of a column as specified in a migration, and the type of a field as specified in a schema.

In the migration, you’re working with the underlying database types. For currency, the most sensible option can definitely be an integer column that represents a number of cents.

In the schema, you’re specifying the desired Elixir type that you want to use when working with that value. This can sometimes be basically the same as the underlying database type (ie text vs String) but other times it can be helpful to use a richer Elixir type if the database type isn’t particularly ergonomic on its own.

What this boils down to is that if I were doing this I’d probably do:

# migration
add :price, :integer

# Schema
field :price, Money

where Money is either whatever type ships with ExMoney, or your own type that builds an ExMoney value explicitly from cents.