michaelst57:

I’m trying to write this query with Ash

select DATE(inserted_at), round(avg(percentage), 2)
from coverage
where "owner" = 'coverbot-io' and "repo" = 'coverbot' and is_for_default_branch
group by 1
order by 1;

I’ve gotten this far but I’m getting an error about fragment/1 being undefined and also not sure how to implement the group by.

Coverage
  |> Ash.Query.filter(owner == ^owner and repo == ^repo and is_for_default_branch)
  |> Ash.Query.select([fragment("DATE(inserted_at)"), fragment("ROUND(AVG(percentage), 2)")])
  |> Ash.Query.sort([1])
  |> Coverbot.Api.read!()

zachdaniel:

Well, you won’t be able to sort by 1 (not sure why you’d want to)

zachdaniel:

And you can’t select fragments

zachdaniel:

You’d need to add a calculation to the resource and select the calculation

zachdaniel:

Or load a one off calculation Ash.Query.calculate

zachdaniel:

I won’t be at a computer for a while but there are examples of expression calcs floating around and in the docs

michaelst57:

The sort by 1 is to sort by the calculated result of the date, here is how I acommplished the query with ecto, maybe this is the better route for this case query = from c in Coverage, join: a in ApiKey, on: c.api_key_id == a.id, select: %{ date: type(fragment("DATE(?)", c.inserted_at), :utc_datetime), percentage: type(fragment("ROUND(AVG(?), 2)", c.percentage), :float) }, where: c.owner == ^owner and c.repo == ^repo and c.is_for_default_branch and a.user_id == ^user.id, order_by: 1, group_by: 1

kernel_io:

make it a sql view?

zachdaniel:

🤔 does order_by: 1 actually do that?

zachdaniel:

TIL about order_by: 1

zachdaniel:

just looked it up, apparently its a thing

zachdaniel:

What exactly are you looking to return?

zachdaniel:

Like thinking about it from a resource perspective, you’ll be accessing this by making some specific query against a resource I imagine

zachdaniel:

The group_by: 1 will also be a problem. Remember the purpose of Ash is to sort of…regulate the way that you interact with these things, not necessarily to contain within it a correlary for every postgres operator.

zachdaniel:

One thing you can potentially do is:

read :whatever_this_is do
  modify_query fn ash_query, ecto_query -> 
    {:ok, new_ecto_query}
  end
end

zachdaniel:

In that query you might be able to accomplish what you want, although it might interrupt other features (like loading aggregates on the returned result)

zachdaniel:

Depending on exactly what you’re looking to do, doing it with ecto might be the best way 🙂

michaelst57:

ya this wouldn’t return any ash resource, it returns an avg coverage percentage by day

michaelst57:

it is to build a graph

zachdaniel:

I’d probably put this as a calculation on a resource somewhere if possible, and then calculate it using ecto.

michaelst57:

Would it make sense to create a virtual resource that isn’t persisted to any datastore?

zachdaniel:

Yes, that would also be an option 🙂 it can add a bit of complexity depending on what features you want to support/how you model it

kernel_io:

yeah you can use numbers for sort and order and group etc

kernel_io:

I’m not a fan of it personally, prefer to be explicit and use names