Sorting by a join field in a query with Flop not working

Arsenalist

I’m sorting by a join field and it’s throwing an error. Here’s the query being executed:

    from(p in Product,
      left_join: variants in assoc(p, :variants), as: :variants,
      left_join: venue_arrangement in assoc(p, :venue_arrangement)
      preload: [
        variants: variants
      ]
    )

Here are the schemas:

defmodule Amplify.Models.Product do
  use Amplify.MedusaSchema

  @derive {
    Flop.Schema,
    filterable: [:showtimes],
    sortable: [:showtimes],
    adapter_opts: [
      join_fields: [
        showtimes: [
          binding: :variants,
          field: :starts_at_utc,
          path: [:variants, :utc_datetime]
        ]
      ]
    ]
  }

  schema "product" do
    field :title, :string
    has_many :variants, Amplify.Models.ProductVariant
  end
end

defmodule Amplify.Models.ProductVariant do
  use Amplify.MedusaSchema

  schema "product_variant" do
    belongs_to :product, Amplify.Models.Product
    field :thumbnail, :string
    field :starts_at_utc, :utc_datetime
  end
end

When I pass in ["showtimes"] as order_directions to flop params and call like this:

Flop.validate_and_run(query, flop_params)

I get:

** (Postgrex.Error) ERROR 42703 (undefined_column) column p0.showtimes does not exist

7 comments

#ecto #flop

1 444 7

2024-10-17 18:53:38 UTC

Showing Posts 1 to 7

Show Best Posts
Show All (oldest first)
Show All (newest first)

woylie

You’ll need to pass the schema module. Flop doesn’t infer the schema automatically.

Flop.validate_and_run(query, flop_params, for: Amplify.Models.Product)

Post #1

woylie

Unrelated, the path option is used to find the cursor values from the result set in case you use cursor pagination. This should thus be [:variants, :starts_at_utc].

Post #2

Arsenalist

Amazing, thank you. That worked perfectly. This library is eliminating the need to write a ton of code for me. Thank you again!

Post #3

Arsenalist

Hi, somewhat related, but do you know how I can print the underlying query that Flop creates when appending the LIMIT etc clauses? I know how to print a plain old Ecto query but how do I access the one that Flop creates when running validate_and_run? In dev this automatically prints but I’m trying to debug something i production.

Post #4

Arsenalist

I find that the LIMIT is not being appended to the query (the total count still returns the right count though).

    {:ok, {results, meta}} = Flop.validate_and_run(query, %{
      "first" => 20,
      "order_by" => ["showtimes"],
      "order_directions" => ["asc"]
    }, for: Amplify.Models.Product)
    meta = Map.put(meta, :total_count, Flop.count(query, meta.flop))
    %{metadata: meta, data: results}

I have a fairly complex query with about 12 joins, but that should’t matter?

This also appears to work perfectly fine in development, but in production, the limit is not being applied

Post #5

Arsenalist

Did some digging around and was able to print the query. The query is printed correctly with the right LIMITs applied and everything. When I run the query directly against the DB it works great.

However, Flop doesn’t return all the results from validate_and_run/3. This is only a problem when there’s a query being passed in. It works fine when you’re just querying a model directly (without joins etc.)

Post #6

Arsenalist

So the issue here was that I wasn’t adding distinct: p.id in my query. Once I added that it all behaved properly. I’m not sure why that matters since Flop should just return whatever it finds. As an example, here’s what I was seeing:

    {:ok, {results, meta}} = Flop.validate_and_run(query, %{
      first: 20,
      order_by: ["showtimes"],
      order_directions: ["asc"]
    }, for: Amplify.Models.Product)
    IO.inspect "total length is #{length(results)}". # this prints 1
    meta = Map.put(meta, :total_count, Flop.count(query, meta.flop))
    IO.inspect "total count is #{meta.total_count}" # this prints 154
    %{metadata: meta, data: results}

Post #7

— All posts loaded —