Query on embedded schema field

[tanguilp]

Hello,

I have the usual profile embedded schema in my %User{} schema.

Is there a way to make user.profile.country_code a filterable or sortable field?

I tried with join_fields and failed miserably.

Cheers!

[woylie]

You can add a lateral join to your query that selects the country_code using a fragment.

@derive {
  Flop.Schema,
  filterable: [:country_code],
  sortable: [:country_code],
  join_fields: [
    country_code: [
      ecto_type: :string,
      binding: :profile,
      field: :country_code
    ]
  ]
}

def list_users(%{} = args) do
  User
  |> from(as: :user)
  |> join(
    :inner_lateral,
    [u],
    subquery(
      User
      |> where([u2], u2.id == parent_as(:user).id)
      |> select([u2], %{
        country_code: fragment("? -> 'country_code'", u2.profile)
      })
    ),
    on: true,
    as: :profile
  )
  |> Flop.validate_and_run(args, for: User)
end

Here's the query plan I get for filtering by country_code: "de" and sorting by country code with a small amount of test data:

Limit  (cost=0.14..19.44 rows=1 width=1168)
  ->  Nested Loop  (cost=0.14..19.44 rows=1 width=1168)
        ->  Seq Scan on users su0  (cost=0.00..11.05 rows=1 width=48)
              Filter: ((profile -> 'country_code'::text) = '\"de\"'::jsonb)
        ->  Index Scan using users_pkey on users u0  (cost=0.14..8.16 rows=1 width=1136)
              Index Cond: (id = su0.id)

[tanguilp]

Thanks, tested and it works.

Now I'm wondering if it could be simplified 🤔 It feels overly complicated.

[woylie]

Flop relies on the Ecto query API and does not use any fragments, since the syntax depends on the used database. That's why there is currently no support for jsonb operators. Maybe this will change in the future, not sure.