I have a 1:n relation from Users to ‘Stocks’, as well as a 1:n relation from a table Labeled ‘Survivors’ to the same table ‘Stocks’.
Ideally, I would like to have one query where I list all the survivor names in a list, regardless if the user owns any associated stock to a survivor. But, if they do own stock, provide the stockOwned count by the related survivor and if the don’t own any stock, just display 0 under Owned, Like so.
The way I am doing it currently is causing major load time because I am doing it with two separate queries and using two foreach loops to compare and ensure that there are no duplicates which can be caused when multiple users own stocks belonging to multiple users.
First Query: WHERE stocks.Users[stocks].objectId = ’ currentUser objectId ’
Second Query: WHERE stocks.Users[stocks].objectId != ’ currentUser objectId ’
Once I have both loaded, I do my compare using the following nest for loop to remove the duplicate survivor names that occur when multiple users have stock relating to the same survivor.
Normally I would resolve this using joins, but I cannot for the life of me figure out how to do it here. While the solution above works, the load time this puts on the page is terrible. Any ideas to make the query more efficient so I don’t need to use front end logic to clean it up?
Hello @Dan_Worwood
Try this:
where clause = stocks.Users[stocks].objectId=‘A5C63A43-8A19-415B-B4DE-EBD3D3C7F2AC’ or stocks.Users[stocks].objectId is null
This request for your data will return:
[
{
"name": "Alec Marino",
"sum": 6
},
{
"name": "Christian Hubicki",
"sum": 6
},
{
"name": "Davie Rickenbacker",
"sum": null
},
{
"name": "Nick Wilson",
"sum": null
},
{
"name": "Pat Cusack",
"sum": null
},
{
"name": "Rob Cesternino",
"sum": null
},
{
"name": "Tony Vlaccos",
"sum": null
}
]
@Volodymyr_Ialovyi this almost works.
Thank you so much for your response! The problem now is if another user owns stock in a survivor where the current user does not. For example, if we user the objectId : ‘858F2CEB-0DF1-4119-AEBE-B9A0D5509AC1’ only 6 survivor names are returned instead of all 7 because this user does not have any stock associated with “Christian Hubicki” but user with objectId: ‘A5C63A43-8A19-415B-B4DE-EBD3D3C7F2AC’ does have associated stock.
If I add the additional “or stocks.Users[stocks].objectId!= ‘858F2CEB-0DF1-4119-AEBE-B9A0D5509AC1’” to the where clause, I get all 7 survivors, but now I don’t know if it is survivor stock owned by the current user.
Hi, @Dan_Worwood
I would suggest to add one more query in that case:
where clause = stocks.Users[stocks].objectId is null or stocks.Users[stocks].objectId = ‘858F2CEB-0DF1-4119-AEBE-B9A0D5509AC1’ or stocks.Users[stocks].objectId != ‘858F2CEB-0DF1-4119-AEBE-B9A0D5509AC1’
This request will return the next data, which will be the same as for the first user:
[
{
"name": "Alec Marino",
"___class": "Survivors",
"sum": 7
},
{
"name": "Christian Hubicki",
"___class": "Survivors",
"sum": 6
},
{
"name": "Davie Rickenbacker",
"___class": "Survivors",
"sum": null
},
{
"name": "Nick Wilson",
"___class": "Survivors",
"sum": null
},
{
"name": "Pat Cusack",
"___class": "Survivors",
"sum": null
},
{
"name": "Rob Cesternino",
"___class": "Survivors",
"sum": null
},
{
"name": "Tony Vlaccos",
"___class": "Survivors",
"sum": null
}
]
Thanks @Marina.Kan
The problem with that query is that that user does not own any stock in the survivor “Christian Hubicki”, this query is showing the stock related to the other user, " A5C63A43-8A19-415B-B4DE-EBD3D3C7F2AC". Does that make sense what I’m trying to do?
Hi Dan,
Please help me understand the relations in question conceptually. Are “survivors” also users? I am struggling with the following:
user does not own any stock in the survivor “XXX”
What does it mean in real-life? From the schema diagram you shared, it appears that a relationship between users and survivors is only through some common stock they share. Again, understanding the real-life model (outside of the schema) would help me understand how to structure the query (or perhaps restructure the data model better).
Regards,
Mark
Thanks @mark-piller,
I guess the easiest way to explain it, think of ‘Survivor’ as ‘Company’ instead. In my application, users need to be able to own stock in a ‘Company’ and so the ‘Users’ table has a relationship to the table, ‘Stocks’, and ‘Stocks’ has a relationship with ‘Survivors’ so that a user knows what their stock is associated with.
So for the company ‘Christian Hubicki’, User A5C63A43-8A19-415B-B4DE-EBD3D3C7F2AC has 6 stock, but User 858F2CEB-0DF1-4119-AEBE-B9A0D5509AC1 has none. But for ‘Alec Marino’, they both have stock.
I am trying to build a query so that it lists all the stock a user has for each company, and for the companies that they don’t have any, show 0. I also want to include companies that no users have associated stock with.
Thanks, @Dan_Worwood , it helps. In this case, would it make sense that the Stocks table has a 1:1 relationship with Survivors? Right now, the relationship is in the Survivors table and it is one-to-many, which doesn’t quite make sense to me.
@mark-piller
Thanks Mark, your comment made me pause and think through my tables again. I guess when I was thinking through the tables, I though 1:n due to the fact that a survivor can be associated to different stock. I’m glad I spoke through the use case with you and went ahead and made the changes. Thank you again!
You are very welcome. It should simplify your query (I think). Please let us know if you need any help.
Regards,
Mark
