How to convert a boolean to an int in the query?

How to convert a boolean to an int in the query?
I have five parameters of type boolean in the entity object - approveEmail, approvePhoneNumber, ...

I can not sort them

ORDER BY approveEmail, approvePhoneNumber, ....

because the parameters equivalent.

I want to sort them in the query by their sum

ORDER BY (approveEmail + approvePhoneNumber + ... )

But I get an exception

com.objectdb.o._PersistenceException: Invalid operand type boolean for operator +

Please tell me how to do this?

Thanks for your help

Consider adding an int field to your entity class, that will sum them. You can calculate it in a JPA lifecycle event.

Currently you cannot convert boolean to int in queries. It is possible to add this to ObjectDB in future versions, but using a pre-calculated field would be much more efficient (you can also use an index on that field to improve performance), and this solution is available now.

I can not use pre-calculated field because some of the parameters I have calculated directly in the query, depending on the query

for example

SELECT DISTINCT m FROM Person m
LEFT OUTER JOIN m.answers answers
LEFT OUTER JOIN answers.persons likedPersons
ORDER BY (m.approveEmail + m.approvePhoneNumber + ... + (count(likedPersons) > 0))

Following your request we added support for casting boolean to int in build 2.5.2_03.

You should be able to use:

    ORDER BY (int)approveEmail + (int)approvePhoneNumber + ...

true and Boolean.TRUE are converted to 1.

false and Boolean.FALSE are converted to 0.

Thank you very much

Can it be used with CriteriaBuilder?

I downloaded version 2.5.2_04. but in the Explorer queries

SELECT approveEmail FROM Person
ORDER BY (int)approveEmail DESC

and

SELECT approveEmail FROM Person
ORDER BY approveEmail DESC

return different results.

Query

SELECT (int)approveEmail FROM Person

return column of null.

You are right. Build 5.5.2_03 was missing some required changes.

Please try build 5.5.2_05, which should also enable using such casting with criteria queries.

The following queries against the world.odb sample database work well:

    SELECT (int)c.capital FROM City c ORDER BY (int)c.capital

and with criteria queries:

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Integer> q = cb.createQuery(Integer.class);
        Root<City> c = q.from(City.class);
        Path<Boolean> boolCaptial = c.get("capital");
        Expression<Integer> intCaptial = boolCaptial.as(Integer.class);
        q.select(intCaptial);
        q.orderBy(cb.asc(intCaptial));
        query = em.createQuery(q);
        resultList = query.getResultList();

Query 

SELECT approveEmail FROM Person ORDER BY (int)approveEmail DESC

works fine.

Query

SELECT approveEmail FROM Person ORDER BY (int)approveEmail + (int)approvePhoneNumber DESC

works fine.

Criteria query

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Integer> q = cb.createQuery(Integer.class);
        Root<Person> c = q.from(Person.class);
        Path<Boolean> boolApproveEmail = c.get("approveEmail");
        Expression<Integer> intApproveEmail = boolApproveEmail.as(Integer.class);
        q.select(intApproveEmail);
        q.orderBy(cb.asc(intApproveEmail));
        query = em.createQuery(q);
        resultList = query.getResultList();

works fine.

But criteria query

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Integer> q = cb.createQuery(Integer.class);
        Root<Person> c = q.from(Person.class);

        Path<Boolean> boolApproveEmail = c.get("approveEmail");

        Path<Boolean> boolApprovePhoneNumber= c.get("approvePhoneNumber");

        Expression<Integer> intApproveEmail = boolApproveEmail.as(Integer.class);

        Expression<Integer> intApprovePhoneNumber = boolApprovePhoneNumber.as(Integer.class);

        q.select(intApproveEmail);
        q.orderBy(cb.desc(cb.sum(intApproveEmail, intApprovePhoneNumber)));
        query = em.createQuery(q);
        resultList = query.getResultList();

does not work

Just tried a similar query on the world.odb database that works:

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Integer> q = cb.createQuery(Integer.class);
        Root<City> c = q.from(City.class);
        Path<Boolean> boolCaptial = c.get("capital");
        Expression<Integer> intCaptial = boolCaptial.as(Integer.class);
        q.orderBy(cb.desc(cb.sum(intCaptial, intCaptial)));
        q.select(intCaptial); 
        query = em.createQuery(q);
        resultList = query.getResultList();

Please explain what doesn't work, and provide a simple test that we will be able to run.

I am sorry, it works fine. This I was wrong. Thanks for your help