CODEWITHSUNDEEP
javaandroidsqliteormlite
stefankmitph
stefankmitph

Reputation: 3306

ORMLite groupByRaw and groupBy issue on android SQLite db

I have a SQLite table content with following columns:

-----------------------------------------------
|id|book_name|chapter_nr|verse_nr|word_nr|word|
-----------------------------------------------

the sql query 

select count(*) from content where book_name = 'John'
group by book_name, chapter_nr

in DB Browser returns 21 rows (which is the count of chapters)

the equivalent with ORMLite android:

long count = getHelper().getWordDao().queryBuilder()
                        .groupByRaw("book_name, chapter_nr")
                        .where()
                        .eq("book_name", book_name)
                        .countOf();

returns 828 rows (which is the count of verse numbers)

as far as I know the above code is translated to:

select count(*) from content
where book_name = 'John'
group by book_name, chapter_nr

result of this in DB Browser:

   | count(*)
------------
 1 | 828
 2 | 430
 3 | 653
...
 21| 542
---------
21 Rows returned from: select count(*)...

so it seems to me that ORMLite returns the first row of the query as the result of countOf().

I've searched stackoverflow and google a lot. I found this question (and more interestingly the answer)

You can also count the number of rows in a custom query by calling the > countOf() method on the Where or QueryBuilder object.

// count the number of lines in this custom query int numRows = dao.queryBuilder().where().eq("name", "Joe Smith").countOf();

this is (correct me if I'm wrong) exactly what I'm doing, but somehow I just get the wrong number of rows.

So... either I'm doing something wrong here or countOf() is not working the way it is supposed to.

Note: It's the same with groupBy instead of groupByRaw (according to ORMLite documentation joining groupBy's should work)

...
.groupBy("book_name")
.groupBy("chapter_nr")
.where(...)
.countOf()

EDIT: getWordDao returns from class Word:

@DatabaseTable(tableName = "content")
public class Word { ... }

Upvotes: 3

Views: 1632

Answers (1)

Gray
Gray

Reputation: 116888

returns 828 rows (which is the count of verse numbers)

This seems to be a limitation of the QueryBuilder.countOf() mechanism. It is expecting a single value and does not understand the addition of GROUP BY to the count query. You can tell that it doesn't because that method returns a single long.

If you want to extract the counts for each of the groups it looks like you will need to do a raw query check out the docs.

Upvotes: 1

Related Questions