How to write complex query with fragments

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;

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!()

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!()

9 Replies

ZachDaniel•2y ago

Well, you won’t be able to sort by 1 (not sure why you’d want to) And you can’t select fragments You’d need to add a calculation to the resource and select the calculation Or load a one off calculation Ash.Query.calculate I won’t be at a computer for a while but there are examples of expression calcs floating around and in the docs

michaelstOP•2y ago

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

    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•2y ago

make it a sql view?

ZachDaniel•2y ago

🤔 does order_by: 1 actually do that? TIL about order_by: 1 just looked it up, apparently its a thing What exactly are you looking to return? Like thinking about it from a resource perspective, you'll be accessing this by making some specific query against a resource I imagine 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. 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

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

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) Depending on exactly what you're looking to do, doing it with ecto might be the best way 🙂

michaelstOP•2y ago

ya this wouldn't return any ash resource, it returns an avg coverage percentage by day it is to build a graph

ZachDaniel•2y ago

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

michaelstOP•2y ago

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

ZachDaniel•2y ago

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•2y ago

yeah you can use numbers for sort and order and group etc I'm not a fan of it personally, prefer to be explicit and use names

Gaming

Programming

How to write complex query with fragments

Did you find this page helpful?