AE
Ash Elixir•3y ago
sriky27

cross_join on a Ash.Query

Hi I am able to do a cross_join on a Ecto.Query style. I am not able to convert to the Ecto.Query -> Ash.Query o r I am not able to perfrom Ecto.Query.from style cross_join on the Ash.Query in the prepare fn. Please advise.
6 Replies
ZachDaniel
ZachDaniel•3y ago
can you show me an example of what you're trying to do?
sriky27
sriky27OP•3y ago
Hi Zach, Here is the example. I could not figure out how to convert this to ash query or use query in the first place. 
prepare(fn query, _context ->   
  ecto_query = from r in AshResource,
    cross_join: t in fragment("SELECT generate_series(?::timestamp, ?::timestamp, '30 min'::interval)", ^start_at, ^end_at),
    on: overlaps(tsrange(r.start_date, r.end_at), t) and r.canceled == false and r.resource_id == ^resource_id
end)
prepare(fn query, _context ->   
  ecto_query = from r in AshResource,
    cross_join: t in fragment("SELECT generate_series(?::timestamp, ?::timestamp, '30 min'::interval)", ^start_at, ^end_at),
    on: overlaps(tsrange(r.start_date, r.end_at), t) and r.canceled == false and r.resource_id == ^resource_id
end)
ZachDaniel
ZachDaniel•3y ago
So, in SQL a cross join can produce duplicates in your query Generally speaking this is not something that makes sense in the resource-oriented world However, you may be able to use the modify_query option 
read :foobar do
  modify_query fn ash_query, ecto_query ->
      ecto_query = from r in AshResource,
        cross_join: t in fragment("SELECT generate_series(?::timestamp, ?::timestamp, '30 min'::interval)", ^start_at, ^end_at),
        on: overlaps(tsrange(r.start_date, r.end_at), t) and r.canceled == false and r.resource_id == ^resource_id

    {:ok, ecto_query} 
  end
end
read :foobar do
  modify_query fn ash_query, ecto_query ->
      ecto_query = from r in AshResource,
        cross_join: t in fragment("SELECT generate_series(?::timestamp, ?::timestamp, '30 min'::interval)", ^start_at, ^end_at),
        on: overlaps(tsrange(r.start_date, r.end_at), t) and r.canceled == false and r.resource_id == ^resource_id

    {:ok, ecto_query} 
  end
end
If you could lay out the specific use case (like what kind of result you want to produce from your action, I might be able to advise more.
sriky27
sriky27OP•3y ago
Basic idea was to generate the time slots which are not reserved There are reservations which are made, now trying to find the timeslots which are not allocated
ZachDaniel
ZachDaniel•3y ago
So what I think you would do here is make a calculation Although I assume you need to filter on this in some way? Another option would be to create a view. Actually a view doesn't quite work in your instance either I think I might do it this way: 
calculate :used_time_slots, {:array, :utc_datetime}, expr(
  fragment("""
    (SELECT 
      dt 
    FROM generate_series(?::timestamp, ?::timestamp, '30 min'::interval) 
    WHERE overlaps(tsrange(?, ?), dt) 
    AND ? == false)
  """, ^arg(:start_at), ^arg(:end_at), start_date, end_at, canceled) do

  argument :start_at, :utc_datetime, allow_nil?: false
  argument :end_at, :utc_datetime, allow_nil?: false
end)
calculate :used_time_slots, {:array, :utc_datetime}, expr(
  fragment("""
    (SELECT 
      dt 
    FROM generate_series(?::timestamp, ?::timestamp, '30 min'::interval) 
    WHERE overlaps(tsrange(?, ?), dt) 
    AND ? == false)
  """, ^arg(:start_at), ^arg(:end_at), start_date, end_at, canceled) do

  argument :start_at, :utc_datetime, allow_nil?: false
  argument :end_at, :utc_datetime, allow_nil?: false
end)
This is a bit different, in that its a subquery per row and not a cross join. That may mean that it won't perform as well, you'll have to try it out
sriky27
sriky27OP•3y ago
Thanks Zach I will try it out.

Did you find this page helpful?