CODEWITHSUNDEEP
couchbase-lite
Nie Selam
Nie Selam

Reputation: 1451

Function.count returns 0 on a query that includes Join

I have documents such as

Category(type="category", catId = 1, name = "cat")

and Products with

Product(catId=1, type="product"), Product(catId=1, type="product")

Now I want to get list of all Categories (all their field) and the total number of products per category. The query runs and returns all categories correctly but the total number of products per category is 0. I am sure each category has a product(s) though.

val catAlias = "catAlias"
        val productAlias = "productAlias"

        val q =
            QueryBuilder
                .select(
                    SelectResult.expression(Function.count(Expression.property("$productAlias.catId").from(productAlias))).`as`("totalProducts"),
                    SelectResult.all().from(catAlias),
                    SelectResult.expression(Meta.id.from(catAlias)).`as`("metaId"),

                    )
                .from(
                    DataSource.database(readsDB).`as`(catAlias),
                )
                .join(
                    Join.leftJoin(DataSource.database(readsDB).`as`(productAlias))
                        .on(
                            Expression.property("$productAlias.type").equalTo(
                                Expression.string(
                                    "product",
                                ),
                            )
                                .and(
                                    Expression.property("$productAlias.catId")
                                        .equalTo(Expression.property("$catAlias.catId")),
                                ),
                        ),
                )
                .where(
                    Expression.property("$catAlias.type").from(catAlias)
                        .equalTo(Expression.string("category")),

                    ).groupBy(
                Expression.property("$catAlias.catId")
            )

What could be the error?

Upvotes: 0

Views: 26

Answers (2)

G. Blake Meike
G. Blake Meike

Reputation: 6715

We are puzzled by this bit in the first SELECT line of your original query: .from(productAlias))). What happens if you just delete that?

Upvotes: 0

Nie Selam
Nie Selam

Reputation: 1451

not sure if it is the right way but this fixed it:

            SelectResult.expression(Function.count(Expression.string("*"))).`as`("productsCount")

Upvotes: 0

Related Questions