dimanche 19 avril 2015

ORMite 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()

Aucun commentaire:

Enregistrer un commentaire