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