Backendless Support
 
Answered

Is circular reference between 2 tables allowed

Hello

I have another question about circular references. I will use simplified example to illustrate my question.

Lets say I have the User table and the Comments table.

I have 2 use cases:

1- I want to get all the comments posted by user xyz

2- I want to get all the comments posted along with their Users (ie all the comments posted today and their user name)

I understand that I can achieve one of them by having a reference from one table to another.

Is it bad (or frawn upon) to have 2 ways relationship? I mean User table would have a column referencing the Comments table. And the Comments table would have a column referencing the user Table.

Theoritically, this way it should allow me to load any row with its relation.

Please let me know if you see any issues with this

Thank you

Leave a Comment

Comments (6)

photo
1

Hi SnakeEyes

If I've correctly understood you, you can use only one relationship Comments.user 1:1 Users

and you can retrieve all Comments of user and also you can retrieve Comments with their user

f4065c5fc73dbb88c0489add5645205e

93119fdd0a1ae9370e5e0829e086f07f

9c7d738114a5f5330769d146c2f3330d

this is what you need?

Regards, Vlad

photo
1

Hello

Thanks for your reply

This is not what I meant.

What I meant is if you table A and table B.

Then A column 1 will have a relation to table B.

And B column1 will have a relation to table A

Is this bad idea?

So if we look at the comments example above

I want to retrieve user xyz and all his comments.

I also want to retrieve all comments with their userId

Makes sense?

photo
1

It's possible to have circular reference between two tables but is redundant in your example with Users and Comments.

Having just user column in Comments table gives you an ability to retrieve both kind on information.

To retrieve user xyz comments, use the following where clause for Comments table: user.objectId = 'XYZ_USER_OBJECT_ID'

To retrieve all comments with their userId, you don't need where clause at all. But in this request you have to specify an option to retrieve user relation (https://backendless.com/docs/rest/doc.html#relations-retrieve)

photo
1

Hello

I don't think it is redundant. What you explained makes sense if you have one comment and you want to know the user for it.

The problem happens when you want to load all the comments for the day (could be 100) along with their users. You don't want to load the user for each comment one by one as that would be so. Many calls and so slow.

This is why I suggested the circular reference. I just wanto to make sure that I don't end up with infinite loop when querying :

User is linked to comments and the comments are linked to user who is linked to comments... Etc

photo
1

if you want to load lets say 100 Comments and know about publisher (user), you just need one of this

- use relationName in query

- use AutoLoad checkbox in console

- use relationDepth

and you will have all the comments along with users in a single API Call (see my the last screenshot above)

circular relations works well so you can try do it, and also you can try with one way relations =)

photo
1

Correct. That would work if I want to load 100 comments along with their publisher. In the same APP in a different use case, I want to load all the comments belonging to single user (ie ,please show me all my comments that I posted).

This is why I suggested the cuircular relation as I can go either direction

query user and all his comments

OR

query all comments along with their user info

Cool, as long as this is "not bad" way of doing things then it solves my problem