Nested preload limit - set to 5 but it's returning 3

Phoenix Forum Questions / Help
, ,
1

Hello there im working on pagination on my query

first lets see my query

query
    |> join(:left, [o], op in OrderProduct, as: :order_product, on: o.id == op.order_id)
    |> join(:left, [o, op], p in Product, as: :product, on: op.product_id == p.id)
    |> preload(
         [order_product: op, product: p],
         [
           :order_type,
           :merchant,
           :customer,
           :payment_method,
           order_product: {
             op,
             product: p
           },
         ]
       )

is valid query and its working, after giving it limit the result mess up

what i mean by mess up is i set the limit 5 but it return 3
i thought it was my query but after hour debugging i realize if i remove nested preload to be this

  query
    |> join(:left, [o], op in OrderProduct, as: :order_product, on: o.id == op.order_id)
    |> join(:left, [o, op], p in Product, as: :product, on: op.product_id == p.id)
    |> preload(
         [order_product: op, product: p],
         [
           :order_type,
           :merchant,
           :customer,
           :payment_method,
#           order_product: {
#             op,
#             product: p
#           },
         ]

it working and find any idea what is going here ??

2

Take a look at the query that this is generating - Ecto.Adapters.SQL.to_sql/3 will give you the actual SQL that gets sent.

The usual culprit in cases like this is that LIMIT applies to the total number of rows returned, but when preloading in a single query there will be one row in the result per OrderProduct; if there are orders with more than one product, fewer than 5 orders will be returned.

1 Like
3

Is there any options for me to make pagination but still doing preload ?

4

Preload in separate queries instead of joining them inline.

1 Like
5

sorry i dont realy get want you mean

so basicly i need get the data first then i iterate trrough all data to get all associate data ?

is that best practice for this case ?? or i miss intepret you sugesstion ??

6

I’m using this to preload n items per row: Ecto preload n items per entity in a list · GitHub

Would love to know if folks find issues with that approach, but so far it worked well for me

7

Essentially yes, but you don’t need to iterate the data manually. If you don’t join associations in your quieries then preloads in ecto will happen in a separate query already. Even though there can be merit in doing less queries to the db there’s also the downside of data duplication within the resultset. Joining assocs just for preloading is imo nothing to blindly default to.

2 Likes