Joins on 3 tables

General
1

Hi, I’m having some problems implementing joins between 3 tables.
I have 3 tables: PictureModel, Voted, and Report. Both Voted and Report have 1-1 relationships to PictureModel.
In the console I can run both of these queries successfully:
Report[Picture].objectId is not null
Voted[Picture].ownerId is not null
However if I try to combine these into one query aka
Report[Picture].objectId is not null and Voted[Picture].ownerId is not null
I get the message that it is an invalid where clause in the console, also when I tried to run the query using the CodeRunner I got this error message:
BackendlessException{ code: ‘Server.Processing’, message: ‘java.lang.RuntimeException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Not unique table/alias: ‘relatedpicture’’ }
Is there any way I can get this type of query working with backendless?

2

Hi Anna,

Could you please provide us your table schema or if it is possible data export.

Cheers, Artur.

3

PictureModel:

Column name - totalVotes
Data type - INT
Default value - 0

Column name - user
Data type - RELATION
Default value - null

Column name - country
Data type - RELATION
Default value - null

Column name - noVotes
Data type - INT
Default value - null

Column name - ownerId
Data type - STRING
Default value - null

Column name - expiration
Data type - DATETIME
Default value - null

Column name - objectId
Data type - STRING_ID
Default value - null

Column name - brand
Data type - RELATION
Default value - null

Column name - pictureID
Data type - STRING
Default value - null

Column name - created
Data type - DATETIME
Default value - null

Column name - active
Data type - BOOLEAN
Default value - true

Column name - updated
Data type - DATETIME
Default value - null

Column name - store
Data type - RELATION
Default value - null

Column name - minutesTillTimeout
Data type - INT
Default value - null

Column name - yesVotes
Data type - INT
Default value - null

Column name - location
Data type - RELATION
Default value - null

Voted

Column name - ownerId
Data type - STRING
Default value - null

Column name - updated
Data type - DATETIME
Default value - null

Column name - picture
Data type - RELATION
Default value - null

Column name - user
Data type - RELATION
Default value - null

Column name - created
Data type - DATETIME
Default value - null

Column name - objectId
Data type - STRING_ID
Default value - null

Report

Column name - objectId
Data type - STRING_ID
Default value - null

Column name - reportType
Data type - RELATION
Default value - null

Column name - user
Data type - RELATION
Default value - null

Column name - updated
Data type - DATETIME
Default value - null

Column name - created
Data type - DATETIME
Default value - null

Column name - ownerId
Data type - STRING
Default value - null

Column name - picture
Data type - RELATION
Default value - null

4

Hello Anna,

Could you please send us on support@backendless.com your data export so we can reproduce the problem?

Best, Artur.

5

Hi, Anna!

You receive this error because you have in both Voted and Report tables relation with the same name “picture”. If you change it for example to voted_picture and report_picture in Voted and Report tables respectively, you can run queries with where clause like this without errors:

Report[report_picture].objectId is not null and Voted[voted_picture].objectId is not null