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?
Hi Anna,
Could you please provide us your table schema or if it is possible data export.
Cheers, Artur.
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
Hello Anna,
Could you please send us on support@backendless.com your data export so we can reproduce the problem?
Best, Artur.
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