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?