findRowCount doesn't work when bean has property with @Formula annotation

Question

I have following class:

@Entity
@Table(name = "clients")
public class Client extends Model {
    @Id
    public int id;

    @Formula(select = "inv.some_data", 
            join = "left join (select 1 as some_data) as inv")
    public int someData;

    public static Finder<String, Client> find = 
        new Finder<String, Client>(String.class, Client.class);

    public static int countClientsWithData() {
        return Client.find.where().gt("someData", 0).findRowCount();
    }
}

It has someData field (play framework will generate getters and setters automatically). And also countClientsWithData uses this field in where clause. Now if I do

int count = Client.countClientsWithData();

It will throw NullPointerException while trying to execute query

select count(*) from clients t0 where inv.some_data > ?

Looks like findRowCount doesn't recognize join in @Formula annotation. Any thoughts on how to work around this problem?

Updated question: narrowed down problem to findRowCount call.

Bloomy

This is a bug. Use what zaffargachal says for now. – Lilliamlillian

Not an answer to your question and just my opinion, but if you really want to use SQL then you should go with iBatis/MyBatis. If you want to use JPA, then use JPA and avoid SQL, avoid SQL, avoid SQL. – Tooley

Answer 1

So you want the count without using findRowCount() method and without fetching all the data ..

Solution: Copy the same query, and make it on the form select count(*) from .. and use it to find the count

Example:

If your query is on the form ..

Query: SELECT * FROM clients

Then this line of code Client.find.where().gt("some_data", 0).findRowCount(); will be equivlant to ..

Count Query: SELECT COUNT(*) FROM clients WHERE some_data > 0

Caritacaritas

Answer 2

One possible way could be use findlist() method and use its size() method to get number of rows instead of findRowCount() method

return Client.find.where().gt("totalOrdersAmount", 0).findList().size();

Barnard

Does that mean that it will fetch the whole table from database? – Bloomy

yep, it will load everything in the Java heap :-( – Oliveira

No it will get the group by rows after applying aggregation functions – Barnard

Answer 3

You should debug this and see what you're missing. It's either a bug as described, or you somehow are not at all referencing what you think you are. We've all been there though, but if you can breakpoint at that line and see your object, just make sure everything is what you expected to look like. As soon as you add trying to deal with database fields and referencing them, misspellings for instance cause many headaches.

Insole

Yes, everything else works as expected because zaffargachal's answer works (except that it fetches the whole table) – Bloomy