Ash Framework•4mo ago

Counting Related Records with a Virtual Field in a Multi-Tenant app

In a multi-tenant application, when retrieving records from the database using a read action, is there a way to define a virtual field that counts the number of records in the database? For example, something like:

calculate 
  :counter, 
  :integer, 
  expr(
    fragment(
      "SELECT COUNT(*) __TENANT_PLUS_CURRENT_TABLE__"
    )
  )

calculate 
  :counter, 
  :integer, 
  expr(
    fragment(
      "SELECT COUNT(*) __TENANT_PLUS_CURRENT_TABLE__"
    )
  )

34 Replies

kernel•4mo ago

yeah, use aggregates, they should respect the multitenancy stuff

catrapatoOP•4mo ago

From what I have read, aggregates operate on associations not over the resource itself

kernel•4mo ago

right, there was some Ash.count stuff I think

kernel•4mo ago

https://elixirforum.com/t/how-do-i-select-count-w-o-relationship/58827

Elixir Programming Language Forum

How do I SELECT COUNT(*) w/o relationship?

I have a table with no relationship. I need to SELECT COUNT(*) FROM table WHERE condition; How do I do that inside an action/calculation/aggregate? Thanks.

kernel•4mo ago

at the bottom Ash.count(), pretty sure it takes tenancy into consideration

catrapatoOP•4mo ago

Thank you for sharing that post. I have saw it before and I just wondering if there is an alternative to don’t make another query or wrapped it in a transaction. Really appreciate your help @kernel

kernel•4mo ago

where do you want count to be? the way above is the only way to get it directly on the Resource iirc, otherwise you can have it on the 'Tenant' if you have a global tables tenant - there you should be able to do a has_many :something, Something, then an aggregates count :something_count, :something, but yeah 🤷🏿‍♂️

catrapatoOP•4mo ago

I’m using a schema-based approach for what it matters.

ZachDaniel•4mo ago

I think what you need to clarify is how you want to actually query this data for example, if you had :counter as a calculation would you really want results like this:

[%Thing{counter: 100}, %Thing{counter: 100}, %Thing{counter: 100}]

[%Thing{counter: 100}, %Thing{counter: 100}, %Thing{counter: 100}]

they'd all have the same value

catrapatoOP•4mo ago

It looks silly but it’s a pattern that we currently use in my work. Maybe it’s not the pretties but there are some advantages using it in that way; like condense everything in one single query and also bringing the most up-to-date count. The middle ground would be wrapped the two queries in a transaction.

kernel•4mo ago

I mean you could possibly have a custom calculation that calls Ash.count on that resource with the tenant or something if you wanted that so it would be in the same transaction you won't get it into a single query though using the ash constructs imo you still need to explain how you want to query the data, as theres at least 2 ways I can think of are you wanting to query Thing from a single tenant, or are you wanting to return a list which shows all counts for all tenants or something it doesn't make sense to have a global count on a single instance of a resource

catrapatoOP•4mo ago

For a single tenant... Imagine that you have an User resource and you want to bring the records + its number of records. Something like Zach mentioned ☝️ This is a handmade approach

  calculations do
    calculate :count,
              :integer,
              expr(fragment("SELECT COUNT(*) FROM tenant_dev.educational_levels"))
  end

  calculations do
    calculate :count,
              :integer,
              expr(fragment("SELECT COUNT(*) FROM tenant_dev.educational_levels"))
  end

SELECT e0."id", e0."name", (SELECT COUNT(*) FROM tenant_dev.educational_levels)::bigint FROM "tenant_dev"."educational_levels" AS e0 []

SELECT e0."id", e0."name", (SELECT COUNT(*) FROM tenant_dev.educational_levels)::bigint FROM "tenant_dev"."educational_levels" AS e0 []

kernel•4mo ago

so wouldn't you have a user has_many educational_levels, and then do an aggregate count :educational_levels_count? so you'd do Ash.all(User, load: [:educational_levels_count]) ? or do you want each user to have a count(of all the other users in same tenant) attribute?

catrapatoOP•4mo ago

I'm using only one table. No associations are involved. The name of the table is educational_levels I want to bring back the records for that table and the number of records on it.

ZachDaniel•4mo ago

Right, but just to be clear, you'd be returning that for all rows

catrapatoOP•4mo ago

I'm going to use the following:

__RESOURCE__ 
|> Ash.Query.for_read(:read) 
|> Ash.count(tenant: "__TENANT__")

__RESOURCE__ 
|> Ash.Query.for_read(:read) 
|> Ash.count(tenant: "__TENANT__")

Yeap. I don't care if I have a 100 rows with the same info.

ZachDaniel•4mo ago

Gotcha, so I'm not sure if you actually have a good way to do that with Ash right now TBH the primary reason being that there isn't a tool to reference the tenant's prefix in the expression actually, yeah, there is a way

catrapatoOP•4mo ago

Good point!

ZachDaniel•4mo ago

its just ugly

catrapatoOP•4mo ago

lol show me! Maybe I can abstract it. = )

ZachDaniel•4mo ago

😆 probably not

relationships do
  has_many :all_educational_levels, __MODULE__ do
    no_attributes? true
  end
end

relationships do
  has_many :all_educational_levels, __MODULE__ do
    no_attributes? true
  end
end

You can make a relationship to "all of the educational levels"

catrapatoOP•4mo ago

To make a circular dependecy?

ZachDaniel•4mo ago

Circular dependencies aren't a problem with relationships

catrapatoOP•4mo ago

That's cool!

ZachDaniel•4mo ago

and then do

calculate :total_count, :integer, expr(count(all_educational_levels))

calculate :total_count, :integer, expr(count(all_educational_levels))

catrapatoOP•4mo ago

Let me try just for fun!

ZachDaniel•4mo ago

That wouldn't handle things like filters though i.e EducationalLevel |> Ash.Query.filter(active == true) |> Ash.Query.load(:count) |> Ash.read!() would return active educational levels, with the total count of all educational levels, not just the active ones

catrapatoOP•4mo ago

It didn't work. I used this

  relationships do
    has_many :all_educational_levels, __MODULE__ do
      no_attributes? true
    end
  end

  calculations do
    calculate :total_count, :integer, expr(count(all_educational_levels))
  end

  relationships do
    has_many :all_educational_levels, __MODULE__ do
      no_attributes? true
    end
  end

  calculations do
    calculate :total_count, :integer, expr(count(all_educational_levels))
  end

And I got the following error:

** (Ash.Error.Unknown) 
Bread Crumbs:
> Error returned from: Calmecalli.Curriculum.EducationalLevel.read

Unknown Error

* ** (Ecto.QueryError) deps/ash_postgres/lib/data_layer.ex:3297: field `aggregate_0` in `select` does not exist in schema Calmecalli.Curriculum.EducationalLevel in query:

from e0 in Calmecalli.Curriculum.EducationalLevel,
  as: 0,
  left_lateral_join: e1 in Calmecalli.Curriculum.EducationalLevel,
  on: true,
  select: merge(struct(e0, [:id, :name, :inserted_at, :updated_at]), %{
  total_count:
    type(
      type(
        coalesce(as(1).aggregate_0, type(^0, {:parameterized, {Ash.Type.Integer.EctoType, []}})),
        {:parameterized, {Ash.Type.Integer.EctoType, []}}
      ),
      {:parameterized, {Ash.Type.Integer.EctoType, []}}
    )
})

** (Ash.Error.Unknown) 
Bread Crumbs:
> Error returned from: Calmecalli.Curriculum.EducationalLevel.read

Unknown Error

* ** (Ecto.QueryError) deps/ash_postgres/lib/data_layer.ex:3297: field `aggregate_0` in `select` does not exist in schema Calmecalli.Curriculum.EducationalLevel in query:

from e0 in Calmecalli.Curriculum.EducationalLevel,
  as: 0,
  left_lateral_join: e1 in Calmecalli.Curriculum.EducationalLevel,
  on: true,
  select: merge(struct(e0, [:id, :name, :inserted_at, :updated_at]), %{
  total_count:
    type(
      type(
        coalesce(as(1).aggregate_0, type(^0, {:parameterized, {Ash.Type.Integer.EctoType, []}})),
        {:parameterized, {Ash.Type.Integer.EctoType, []}}
      ),
      {:parameterized, {Ash.Type.Integer.EctoType, []}}
    )
})

ZachDaniel•4mo ago

:what: you're on the latest versions of everything? Does anything change if you do:

  calculations do
    calculate :total_count, :integer, expr(count(all_educational_levels, field: :id))
  end

  calculations do
    calculate :total_count, :integer, expr(count(all_educational_levels, field: :id))
  end

catrapatoOP•4mo ago

Nope. It didn't work as well. I just created the project a few days ago so I'm probably using the most up-to-date ash code. But I'm happy to check anything you think is necessary.

ZachDaniel•4mo ago

wild Can you try adding a filter to the relationship?

has_many :all_educational_levels, __MODULE__ do
  no_attributes? true
  filter expr(string_length(name) > 0)
end

has_many :all_educational_levels, __MODULE__ do
  no_attributes? true
  filter expr(string_length(name) > 0)
end

That...also shouldn't matter though TBH. what about as an aggregate

aggregates do
  count :total_count, [:all_educational_levels]
end

aggregates do
  count :total_count, [:all_educational_levels]
end

catrapatoOP•4mo ago

No luck. I'm using:

  relationships do
    has_many :all_educational_levels, __MODULE__ do
      no_attributes? true
    end
  end

  aggregates do
    count :total_count, [:all_educational_levels]
  end

  relationships do
    has_many :all_educational_levels, __MODULE__ do
      no_attributes? true
    end
  end

  aggregates do
    count :total_count, [:all_educational_levels]
  end

And I got:

** (Ash.Error.Unknown) 
Bread Crumbs:
  > Error returned from: Calmecalli.Curriculum.EducationalLevel.read

Unknown Error

* ** (Ecto.QueryError) deps/ash_postgres/lib/data_layer.ex:3297: field `total_count` in `select` is a virtual field in schema Calmecalli.Curriculum.EducationalLevel in query:

from e0 in Calmecalli.Curriculum.EducationalLevel,
  as: 0,
  left_lateral_join: e1 in Calmecalli.Curriculum.EducationalLevel,
  on: true,
  select: merge(struct(e0, [:id, :name, :inserted_at, :updated_at]), %{
  total_count:
    type(
      coalesce(
        type(as(1).total_count, {:parameterized, {Ash.Type.Integer.EctoType, []}}),
        type(^0, {:parameterized, {Ash.Type.Integer.EctoType, []}})
      ),
      {:parameterized, {Ash.Type.Integer.EctoType, []}}
    )
})

** (Ash.Error.Unknown) 
Bread Crumbs:
  > Error returned from: Calmecalli.Curriculum.EducationalLevel.read

Unknown Error

* ** (Ecto.QueryError) deps/ash_postgres/lib/data_layer.ex:3297: field `total_count` in `select` is a virtual field in schema Calmecalli.Curriculum.EducationalLevel in query:

from e0 in Calmecalli.Curriculum.EducationalLevel,
  as: 0,
  left_lateral_join: e1 in Calmecalli.Curriculum.EducationalLevel,
  on: true,
  select: merge(struct(e0, [:id, :name, :inserted_at, :updated_at]), %{
  total_count:
    type(
      coalesce(
        type(as(1).total_count, {:parameterized, {Ash.Type.Integer.EctoType, []}}),
        type(^0, {:parameterized, {Ash.Type.Integer.EctoType, []}})
      ),
      {:parameterized, {Ash.Type.Integer.EctoType, []}}
    )
})

On another note, your Ash Framework book has been a lifesaver. 🛟

ZachDaniel•4mo ago

Well then. Is this repo open source? If you could open an issue on ash_postgres. I'll look into it sometime this week. In the meantime, you can use the underlying tooling to workaround the issue i.e write a generic action that calls into ecto Maybe not that helpful if you aren't familiar with those tools though

catrapatoOP•4mo ago

I'll make a repo with a simple setup during the week and let you know. I'll wait for your feedback until then. Generic is on my list of things that I need to learn but I'll pass for now. Thanks Zach!

Gaming

Programming

Counting Related Records with a Virtual Field in a Multi-Tenant app

Did you find this page helpful?