Relation column in having clause

Data Service
1

I have a table ‘Icon’ and it has a 1:N relation column ‘tags’, and I was trying to get icons with more than 2 tags:

let queryBuilder = Backendless.DataQueryBuilder.create()
queryBuilder.setProperties('Count(tags.objectId), objectId')
queryBuilder.setGroupBy('objectId')
queryBuilder.setHavingClause('Count(tags.objectId)>2')
Backendless.Data.of('Icon').find(queryBuilder)

But it shows this error: “Column ‘objectId’ in having clause is ambiguous”

Is there anything wrong with the query?

2

Hello @Barry

Unfortunately I haven’t found a way to do this. You can get all the records and then filter them.

const queryBuilder = Backendless.DataQueryBuilder.create()
queryBuilder.setProperties(['objectId', 'Count(tags)'])
queryBuilder.setGroupBy('objectId')
queryBuilder.setPageSize(100)

// Here you need to get all the records
const records = await Backendless.Data.of('Icon').find(queryBuilder)
const result = records.filter(({ count }) => count > 2)

In this example you will not get more than 100. To get all the records when there are more than 100 you need to create an additional function

Regards

3

Hi Viktor @viktor.liablin,

I found that if I use alias like below then it works. Any idea why?

queryBuilder.setProperties('Count(tags.objectId) as tagCount, objectId')
queryBuilder.setGroupBy('objectId')
queryBuilder.setHavingClause('tagCount>2')
4

Hello @Barry

Yes, you’re absolutely right — using an alias like tagCount solves the problem, and here’s why:

In your original query, you’re using Count(tags.objectId) in the setProperties (SELECT) and again in the setHavingClause (HAVING clause). That itself is fine, but you’re also selecting objectId, and Backendless uses objectId in multiple places — from both the main Icon table and the joined Tag table.

As a result, when the SQL query reaches the HAVING clause and sees:

HAVING count(objectId) > 2

…it doesn’t know which objectId you mean — from Icon or from Tag. That’s why it throws:

Column 'objectId' in having clause is ambiguous

Why aliasing fixes it
When you do this:

queryBuilder.setProperties('Count(tags.objectId) as tagCount, objectId')
queryBuilder.setHavingClause('tagCount > 2')

You’re telling Backendless:

“Assign a unique name (tagCount) to this aggregation result.”

Then, in the HAVING clause, Backendless generates:

HAVING tagCount > 2

Now there’s no ambiguity — you’re referring specifically to the alias defined in the SELECT part.

Regards,
Volodymyr

5

Thanks Volodymyr.
setHavingClause('Count(tags.objectId)>2')
In the having clause I did specify tags.objectId so I don’t know why it is still considered ambiguous.

I guess if there is no alias then the data returned before having clause would be
{ count: 2, objectId: XXX }
So the having clause Count(tags.objectId)>2 couldn’t find tags.objectId

If use an alias then it would return:
{ tagCount: 2, objectId: XXX }
Then Count(tagCount)>2 would have no problem in having clause.

6

You’re very close, and your intuition about the alias is correct — but the issue isn’t that tags.objectId can’t be found in the HAVING clause because it’s missing in the result, but rather that the generated SQL doesn’t carry tags.objectId through the aggregation process directly.

Regards,
Volodymyr