CODEWITHSUNDEEP
androidcouchbase-lite
Vitalii
Vitalii

Reputation: 11091

Multiple and() conditions in where clause for couchbase lite Android client

In my Android app we use couchbase lite database version 2.8.6

I run three database queries.

QueryBuilder.select(
    SelectResult.property("id"),
    SelectResult.property("timestamp"),
    SelectResult.property("rating"))
.from(DataSource.database(database))
.where(Expression.property("type").equalTo(Expression.string(DOC_TYPE)))

In the result I see three items from database printed to a console. As expected. Format here ans below is id|timestamp|rating

4e39f79c-9e11-4aba-9fb6-95d910f46cd9|0|-2147483648
e95646ee-ba3a-4978-b2a8-5383f31be2f1|0|-2147483648
e02d0eb3-6c9b-4942-b43c-a752eefc77a8|1630525956184|2147483647

QueryBuilder.select(
    SelectResult.property("id"),
    SelectResult.property("timestamp"),
    SelectResult.property("rating"))
.from(DataSource.database(database))
.where(Expression.property("type").equalTo(Expression.string(DOC_TYPE))
   .and(Expression.property("rating").lessThan(Expression.intValue(1))
)

Result is as expected as we search everything with rating < 1, third item is filtered out.

4e39f79c-9e11-4aba-9fb6-95d910f46cd9|0|-2147483648
e95646ee-ba3a-4978-b2a8-5383f31be2f1|0|-2147483648

QueryBuilder.select(
    SelectResult.property("id"),
    SelectResult.property("timestamp"),
    SelectResult.property("rating"))
.from(DataSource.database(database))
.where(Expression.property("type").equalTo(Expression.string(DOC_TYPE))
   .and(Expression.property("rating").lessThan(Expression.intValue(1))
       .and(Expression.property("timestamp")).lessThanOrEqualTo (Expression.longValue(1))
                    )
            )

And now the result is really strange as I receive three items form the database. And the third one has timestamp much greater than 1 put into the query.

4e39f79c-9e11-4aba-9fb6-95d910f46cd9|0|-2147483648
e95646ee-ba3a-4978-b2a8-5383f31be2f1|0|-2147483648
e02d0eb3-6c9b-4942-b43c-a752eefc77a8|1630525956184|2147483647

Any ideas how to make it work with multiple and()? If I try to remove the second and() and keep the third one everything works as expected.

Upvotes: 0

Views: 206

Answers (2)

Pasin
Pasin

Reputation: 86

I would expect that the query would be as you did originally:

.where(condition1.and(condition2) .and(condition3) .and(...))

I think there is an additional parenthesis here that seems to be wrong:

.and(Expression.property("timestamp"))

Upvotes: 0

Vitalii
Vitalii

Reputation: 11091

After deep investigations I found several problems in my code:

  • .and() should be called at the end of the "child" condition and not at the "parent" level.

For the conditions like this

val condition1 = Expression.property("type").equalTo(Expression.string(DOC_TYPE))
val condition2 = Expression.property("rating").lessThan(Expression.intValue(1))
val condition3 = Expression.property("timestamp")).lessThanOrEqualTo (Expression.longValue(1))

The correct way is

.where(condition1.and(
            condition2.and(
                condition3.and(
                    ...
                )
            )
        )

but NOT like I've tried

.where(condition1.and(condition2)
                 .and(condition3)
                 .and(...)
      )
  • For the creation of the object I used a code that converts an object to Map<String, Any> before saving to couchbase. Everything seemed to be OK till I realized that Long was converted to Double.
  • The last point, I have much complicated queries and inside one of them I accidentally used .add() instead of .and(). No comments.

Hope this will help to somebody to save some time.

Upvotes: 0

Related Questions