Rewriting sql query with subjoin to ecto / phoenix query

Questions / Help
,
1

Hi,

I have been breaking my head over the following query for the past day.

I have a Games table, and GameEvents table. In the games table game information is stored and in the game_events table, events regarding the games are stored (per user). I want to get a list of 9 recent games per user.

I thought ‘oh easy’, select all game_events where device_uuid (user) matches passed in device_uuid, distinct on game_id and sort by inserted_at. Alas this isn’t the case when using distinct with a sort_by on another column. I did some more searching and found a couple of nice examples and managed to create the sql query that gets the work done:

schema structure:

  schema "games" do
    has_many :game_events, GameEvent, on_delete: :delete_all

    ... omitted fields...
    timestamps()
  end

  schema "game_events" do
    belongs_to :game, Game
    field :external_user_token, :string
    field :device_uuid, :string

    field :type, :string
    field :payload, :map

    timestamps()
  end

Query that gives the desired result:

SELECT ges.game_id, ges.inserted_at, ges.device_uuid FROM game_events ges
JOIN (
    SELECT game_id, max(inserted_at) max_inserted_at
    FROM game_events
	WHERE device_uuid = '1234'
    GROUP BY game_id
) recent_ges
ON ges.game_id = recent_ges.game_id AND ges.inserted_at = recent_ges.max_inserted_at
ORDER BY ges.inserted_at DESC
LIMIT 9

My querstion now is, how do I get this query into ecto. I’ve managed to get the sub_query in the join working:

# The dynamic is used to select on the device_uuid (or external_user_token).
def game_events_preload(%{attribute: attribute, value: value}) do
from ge in __MODULE__,
  where: ^dynamic([ge], field(ge, ^attribute)  == ^value)
end

    subquery =
      external_user_identifier
      |> game_events_preload()
      |> select([ge], %{game_id: ge.game_id, max_inserted_at: max(ge.inserted_at)})   # I dont know if this map can work like an alias (recent_ges)
      |> group_by([ge], ge.game_id)

    # How do I get this join with the irder rught?
    game_ids =
      from ge in __MODULE__,
        join: unique_ges in ^subquery

But I can’t seem to get the subquery JOIN with the ON and ORDER BY working. Is this possible in ecto? Or is there another way to achieve the desired result?

Thanks!

1 Like
2

:wave:

You may try using a lateral join.

From https://hexdocs.pm/ecto/Ecto.Query.html#join/5:

Game
|> join(:inner_lateral, [g], gs in fragment("SELECT * FROM games_sold AS gs WHERE gs.game_id = ? ORDER BY gs.sold_on LIMIT 2", g.id))
|> select([g, gs], {g.name, gs.sold_on})

And your query maybe gets turned into:

defmacrop last_game_events(game_id, device_uuid, limit \\ 9) do
  quote do
    fragment(
      """
      SELECT *
      FROM game_events
      WHERE device_uuid = ? AND game_id = ?
      ORDER BY inserted_at DESC
      LIMIT ?
      """,
      unquote(device_uuid),
      unquote(game_id),
      unquote(limit)
    )
  end
end

@spec list_games_with_latest_game_events(String.t()) :: [%Game{}]
def list_games_with_latest_game_events(device_uuid) do
  import Ecto.Query

  Game
  |> join(:inner_lateral, [g], ge in last_game_events(g.id, ^device_uuid))
  |> select([g, ge], %{g | last_game_events: ge}) # assuming you have a `:last_game_events` (maybe virtual) field on games
  |> Repo.all()
end

If you want to know more about lateral joins in postgres, see https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL.

1 Like
3

Thanks for your suggestion, however I can’t seem to get the query to work to see if I get the desired results. Ecto is giving me this error:

I dont have last_game_events, however I do have game_events but this gives an error.

** (ArgumentError) cannot load `{2, "abcd", "1234", 2, "completed", %{"this" => "stores", "json" => %{"so" => "cool"}, "payload" => "test"}, ~N[2018-11-28 12:01:46.000000], ~N[2018-11-28 12:01:46.000000]}` as type :world_map:

I will try with the lateral join from your example!

4

What if you change the query to return a list of maps?

  Game
  |> join(:inner_lateral, [g], ge in last_game_events(g.id, ^device_uuid))
- |> select([g, ge], %{g | last_game_events: ge}) # assuming you have a `:last_game_events` (maybe virtual) field on games
+ |> select([g, ge], %{game: g, last_game_events: ge})
  |> Repo.all()

?

5

Initially I had it working with your suggestions and the lateral_join:

  defmacrop last_game_events(external_user_identifier) do
    quote do
      fragment(
        """
        SELECT game_id, max(inserted_at) max_inserted_at
        FROM game_events AS recent_ges
        WHERE recent_ges.device_uuid = ?
        GROUP BY recent_ges.game_id
        """,
        unquote(external_user_identifier)
      )
    end
  end

  def recently_played_games(external_user_identifier) do
    game_ids =
      __MODULE__
      |> join(
        :inner_lateral,
        [ge],
        recent_ges in last_game_events(external_user_identifier),
        on: ge.game_id == recent_ges.game_id and
            ge.inserted_at == recent_ges.max_inserted_at
        )
      |> order_by([ge, recent_ges], desc: ge.inserted_at)
      |> select([ge, _], ge.game_id)
      |> limit(9)

This returns the desired results, so now it’s time to see if we can simplify things. A colleague helped me to simplify the query:

select ges.game_id, max(ges.inserted_at), ges.device_uuid
from game_events ges
where device_uuid = '1234'
group by ges.game_id, ges.device_uuid
order by max(ges.inserted_at) desc
limit 9

The group by acts as a distinct on game_id and by ordering on max(inserted_at) it returns only the newest records!

This is the resulting query in ecto:

    game_ids =
      external_user_identifier
      |> game_events_preload()
      |> select([:game_id])
      |> group_by([g], g.game_id)
      |> order_by([g], desc: max(g.inserted_at))
      |> limit(9)

    external_user_identifier
      |> game_events_preload()
      |> Game.games_query()
      |> join(:inner, [g], g_ids in subquery(game_ids), on: g.id == g_ids.game_id)

Thanks for your help and pointing me in the right direction.

2 Likes