Sorting of Entities using "Order by" in Entity Designer

Support
1

Hi,
I have i simple Application with Entities “Order” an “OrderItem”. Order have a property “date” and one-to-many list of OrderItems.

To keep it simple, OrderItems have a backreference to Order.

Now i am in Order-Designer in the IDE. I check “orderitems” attribute on the left side and go to “Order by” on the right. I want to order by “order”.

I start the application, go to “Orders” an open some (i’ve created one Order over Entity inspector therefore) and want to open this order. It works.

Then i want to change sorting. Not more just order by “order” but by date of it. I set “order.date” in “Order by” field in Order-Designer.
Now a do the same steps and i get this exception:

Exception Description: Invalid query key [date] in expression.
Query: ReadAllQuery(name=“orderitems” referenceClass=OrderItem )

I’ll attach a demo project that i used for my other post, but the problem is also reproducable there.

Could you take a look if it possible to do what i want or am i making something wrong? Maybe it is a bug?
demo2.zip (2.3 MB)

Best Regards

3

Greetings,

I’ve opened your project and I see what you’re trying to achieve. However, since all OrderItem instances in the collection belong to the same Order, each item’s order.date is identical. Sorting by a value that never varies won’t change the order of the list—and in plain JPA (and Jmix) you cannot even express a nested path like order.date in @OrderBy, which is why you get:

Invalid query key [date] in expression.

Why @OrderBy(“order.date”) doesn’t work ?

  • JPA rule: @OrderBy only accepts direct attributes of the target entity (OrderItem), not nested paths.
  • Jmix’s generated ReadAllQuery parses your @OrderBy value and fails when it sees the unknown key date on the OrderItem itself.

What you can do instead

  1. Custom JPQL loader in the screen
    Define your collection loader with an explicit ORDER BY on the parent date:

       <collection id="orderItemsDl" class="com.company.demo.entity.OrderItem">
     <loader id="orderItemsDl">
       <query>
         <![CDATA[
           select oi
           from demo_OrderItem oi
           where oi.order.id = :entityId
           order by oi.order.date
         ]]>
       </query>
     </loader>
   </collection>

    This generates SQL that orders by the foreign-key column (order_date) even though it’s a parent property.

  2. Spring Data repository method
    If you prefer repository-style loading, add:

    List<OrderItem> findByOrderIdOrderByOrderDateAsc(UUID orderId);

    Then in your screen controller:

       @Inject
   private OrderItemRepository orderItemRepo;

   @Subscribe
   public void onBeforeShow(BeforeShowEvent event) {
       List<OrderItem> items = orderItemRepo
           .findByOrderIdOrderByOrderDateAsc(getEditedEntity().getId());
       orderItemsDl.setItems(items);
   }

  3. Programmatic sorting after load
    Let Jmix load the items normally, then sort in Java:

       @Subscribe
   public void onBeforeShow(BeforeShowEvent event) {
       List<OrderItem> items = new ArrayList<>(getEditedEntity().getOrderitems());
       items.sort(Comparator.comparing(i -> i.getOrder().getDate()));
       orderItemsDl.setItems(items);
   }

I hope I solved ur problem, if you have any questions, reply this thread.

Best regards,
Dmitry

4

Hi Dmitry,
Thank you for your answer!

I’m sorry, you’re actually right, there’s no point in sorting by the field Order.date because it is always the same. But i wanted to keep the example simlpe :slight_smile:

In my real application the Order Items are related to another Entity (e.g. ProductInformation, which could contain price, description of the product etc). In this case, I wanted to order by nested path like orderitem.productinformation.price.

And I wanted to keep the OrderItems - DataContainer as Nested Collection Container and not sort programmatically. As a nested DataContainer, I can not define a custom loader on it.

As I see in your examples, there is no way to do it? It is not a problem, then we will look for another solution of our case.

Thank you!

1 Like
5

Hello again!

JPA’s @OrderBy can sort only by attributes that belong directly to OrderItem; it cannot follow associations like productInformation.price. That’s why the nested CollectionContainer cannot accept orderitem.productinformation.price in the Order by field.

Practical options:

  • Add a simple field (or @Formula) in OrderItem that stores the price and order by that field (im not sure in this case due to eclipselink not hibernate)
  • Use a dedicated CollectionLoader/repository query with ORDER BY oi.productInformation.price.
  • Load the items as usual and sort them in memory.

2ond solution is simplest for ur case, i guess.

Results

With the standard Order by designer setting this nested‑path sort isn’t possible.

Best effort way to solve your problem is to manually sort items via JPQL in screens (view) via ORDER BY oi.productInformation.price