Cannot sort descending, when ordering by caluculated float in a method

Hi, I have found an issue in ordering by a method. I have two entities : Invoice and InvoiceItem.

public class InvoiceItem{
    private float price;
    private float amount;
    ..
}

public class Invoice{
    private List<InvoiceItem> invoiceItemList;
    ..
    public Float getPrice(){
        Float price = 0f;
        if (invoiceItemList!=null) {
            for(InvoiceItem ii : invoiceItemList){
	        price += ii.getPrice()*ii.getAmount();
            }
        }
        return price;
    }
    ..
}

When I try this query: (I know, it's not very efficient...)

select i from Invoice i order by i.getPrice() desc/asc

it gives the same order for desc and asc (every time asc)

When the float is not calculated (only returned), the ordering works correctly.

Michael

EDIT:

I have found out, that ordering doesn't work in this case at all - it was only a luck, that asc ordering was working...

Following your report I wrote the following test - but it works well.

Could you please change it to demonstrate the issue?

package com.objectdb.forum;

import java.util.*;
import javax.persistence.*;

public final class T353
{
    public static void main(String[] args)
    {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory("$objectdb/db/test.odb");
        EntityManager em = emf.createEntityManager();
       
        em.getTransaction().begin();
       
        Invoice i1 = new Invoice();
        i1.getItemList().add(new InvoiceItem(1, 1));
        i1.getItemList().add(new InvoiceItem(2, 2));
        em.persist(i1);

        Invoice i2 = new Invoice();
        i2.getItemList().add(new InvoiceItem(3, 3));
        em.persist(i2);

        em.getTransaction().commit();

        Query query = em.createQuery(
            "SELECT i FROM Invoice i ORDER BY i.getPrice() DESC");
        List resultList = query.getResultList();
        System.out.println(resultList);

        em.close();
        emf.close();
    }
   
    @Entity
    public static class InvoiceItem{
        private float price;
        private float amount;
        InvoiceItem(float price, float amount) {
            this.price = price;
            this.amount = amount;
        }
        public float getPrice() {
            return price;
        }
        public float getAmount() {
            return amount;
        }
        @Override
        public String toString() {
            return amount + "*" + price;
        }
    }

    @Entity
    public static class Invoice {
        @OneToMany(cascade=CascadeType.PERSIST)
        private List<InvoiceItem> itemList = new ArrayList<InvoiceItem>();
        public Float getPrice() {
            float price = 0f;
            for(InvoiceItem ii : itemList){
                price += ii.getPrice() * ii.getAmount();
            }
            return Float.valueOf(price);
        }
        public List<InvoiceItem> getItemList() {
            return itemList;
        }
        @Override
        public String toString() {
            return itemList.toString();
        }
    }
}

I have changed your code, to demonstrate the issue:

@Entity
public class Invoice {
   
    @OneToMany(mappedBy = "invoice", fetch= FetchType.EAGER, orphanRemoval=true, cascade= CascadeType.ALL)
    private List<InvoiceItem> itemList = new ArrayList<InvoiceItem>();
    public Float getPrice() {
        float price = 0f;
        for(InvoiceItem ii : itemList){
            price += ii.getPrice() * ii.getAmount();
        }
        return Float.valueOf(price);
    }
    public List<InvoiceItem> getItemList() {
        return itemList;
    }
    @Override
    public String toString() {
        return itemList.toString();
    }
}


@Entity
public class InvoiceItem{
    private float price;
    private float amount;
   
    @ManyToOne
    private Invoice invoice;
   
    InvoiceItem(float price, float amount, Invoice i) {
        this.price = price;
        this.amount = amount;
        this.invoice = i;
    }
    public float getPrice() {
        return price;
    }
    public float getAmount() {
        return amount;
    }

    public Invoice getInvoice() {
        return invoice;
    }

    public void setInvoice(Invoice invoice) {
        this.invoice = invoice;
    }
    @Override
    public String toString() {
        return amount + "*" + price;
    }
}


public static void main(String[] args) {
        com.objectdb.Enhancer.enhance("javaapplication.*");
       
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory("$objectdb/db/test.odb");
        EntityManager em = emf.createEntityManager();

        em.getTransaction().begin();

        Invoice i1 = new Invoice();
        i1.getItemList().add(new InvoiceItem(1, 2, i1));
        em.persist(i1);
       
        Invoice i2 = new Invoice();
        i2.getItemList().add(new InvoiceItem(2, 2, i2));
        em.persist(i2);
       
        Invoice i3 = new Invoice();
        i3.getItemList().add(new InvoiceItem(5, 2, i3));
        em.persist(i3);
       
        Invoice i4 = new Invoice();
        i4.getItemList().add(new InvoiceItem(4, 2, i4));
        em.persist(i4);
       
       
        Invoice i5 = new Invoice();
        i5.getItemList().add(new InvoiceItem(3, 2, i5));
        em.persist(i5);

        em.getTransaction().commit();

        Query query = em.createQuery(
            "SELECT i FROM Invoice i ORDER BY i.getPrice() desc");
        List resultList = query.getResultList();
        System.out.println(resultList);

        em.close();
        emf.close();
    }

The result list is everytime [[2.0*1.0], [2.0*2.0], [2.0*5.0], [2.0*4.0], [2.0*3.0]], so ther is no order...

Michael

OK. It seems that mapped by (inverse) fields are unavailable to methods in queries, regardless if the method is used in ORDER BY or in any other query clause.

The following query will fail as well (will return only 0s).

SELECT i.getPrice() FROM Invoice i

Since user methods in queries is an ObjectDB extension and not part of JPA / JDO fixing this is in a lower priority - but if you need this feature you may fill a feature request.