Check if a record exists; if not, fallback to a default one. How?

Questions / Help
,
1

I have a tenant architecture. In a table “payment_methods” are the payment methods that can be both shared among all the tenants and be overwritten by each individual tenant. There’s “payment_name_id” that identifies a payment system by name, let’s say “paypal”.

  • when “tenant_id” field is nil, it’s shared among all the tenants, by default.

  • when “tenant_id” field is nil and there’s another record with “tenant_id = 123” and the same “payment_name_id”, it’s shared among all the tenants and that individual tenant with id = 123 has to use the record with tenant_id = 123 instead of the one with tenant_id = nil.

  • when “tenant_id = 123” and there’re no other records with tenant_id = nil for a given “payment_name_id”,a tenant with id 123, and only that tenant, may use such a payment method

How do I create a query in Ecto for this? I’d check if there’s a payment_method with “tenant_id” = “my_tenant_id” and payment_name_id, and if there’s, I’d use it, if not I’d fallback to the one with tenant_id = nil for a given payment_name_id, if it exists at all.

Do there have to be 2 separate queries necessarily? I know how to create 2, but is there a way to do it simpler, in a single query?

2

Isnt this a case of just query for

SELECT * FROM payment_methods
WHERE  tenant_id = 123 OR tenant_id IS NULL

Or is there a join that I am missing? Even if it is you can join on something like the where condition above

3

You are missing the fact that there will be 2 records returned instead of 1.

@ojinari is the default configurable in the DB? If not, then maybe instead of fetching it from the DB just cache it locally. Alternatively you can do:

from payment_method in PaymentMethod,
  where: payment_method.tennant_id == ^tennant_id or is_nil(tennant_id),
  order_by: [asc_nulls_last: :tennant_id],
  limit: 1

Which will return at most one entry selecting the one with tennant_id present if exists.

1 Like
4

Exactly