My experience is with SQL but I am working on learning parse server data management and in the example below I demonstrate how I would use SQL to represent the data I currently have stored in my parse server classes. I am trying to present all the users, the count of how many images they have uploaded, and a count of how many images they have liked for an app where users can upload images and they can also scroll through and like other people's images. I store the id of the user who uploads the image on the image table and I store an array column in the image table of all the ids that have liked it.
Using SQL I would have normalized this into 3 tables (user, image, user_x_image), joined the tables, and then aggregated that result. But I am trying to learn the right way to do this using parse server where my understanding is that the best practice is to structure the data the way I have below. What I want to do is produce a "leader board" that presents which users have uploaded the most images or liked the most images to inspire engagement. Even justy links to examples of how to join/aggregate parse data sets would be very helpful. If I wasn't clear in what I am trying to achieve please let me know if the comments and I will add updates.
-- SQL approximation of data structured in parse
create volatile table users
( user_id char(10)
, user_name char(50)
) on commit preserve rows;
insert into users values('1a','Tom');
insert into users values('2b','Dick');
insert into users values('3c','Harry');
insert into users values('4d','Simon');
insert into users values('5e','Garfunkel');
insert into users values('6f','Jerry');
create volatile table images
( image_id char(10)
, user_id_owner char(10) -- The object Id for the parse user that uploaded
, UsersWhoLiked varchar(100) -- in Parse class this is array of user ids that clicked like
) on commit preserve rows;
insert into images values('img01','1a','["4d","5e"]');
insert into images values('img02','6f','["1a","2b","3c"]');
insert into images values('img03','6f','["1a","6f",]');
-----------------------------
-- DESIRED RESULTS
-- Tom has 1 uploads and 2 likes
-- Dick has 0 uploads and 1 likes
-- Harry has 0 uploads and 1 likes
-- Simon has 0 uploads and 1 likes
-- Garfunkel has 0 uploads and 1 likes
-- Jerry has 2 uploads and 1 likes
-- How to do with normalized data structure
create volatile table user_x_image
( user_id char(10)
, image_id char(10)
, relationship char(10)
) on commit preserve rows;
insert into user_x_image values('4d','img01','liker');
insert into user_x_image values('5e','img01','liker');
insert into user_x_image values('1a','img02','liker');
insert into user_x_image values('2b','img02','liker');
insert into user_x_image values('3c','img02','liker');
insert into user_x_image values('1a','img03','liker');
insert into user_x_image values('6f','img03','liker');
-- Return the image likers/owners
sel
a.user_name
, a.user_id
, coalesce(c.cnt_owned,0) cnt_owned
, sum(case when b.relationship='liker' then 1 else 0 end) cnt_liked
from
users A
left join
user_x_image B
on a.user_id = b.user_id
left join (
sel user_id_owner, count(*) as cnt_owned
from images
group by 1) C
on a.user_id = c.user_id_owner
group by 1,2,3 order by 2
-- Returns desired results
First, I am assuming you are running Parse Server with a MongoDB database (Parse Server also supports Postgres and it can make things little bit easier for relational queries). Because of this, it is important to note that, besides Parse Server implements relational capabilities in its API, in fact we are talking about a NoSQL database behind the scenes. So, let's go with the options.
Option 1 - Denormalized Data
Since it is a NoSQL database, I'd prefer to have a third collection called LeaderBoard. You could add a afterSave trigger to the UserImage class and make LeaderBoard always updated. When you need the data, you can do a very simple and fast query. I know that it sounds kinda strange for a experienced SQL developer to have a denormalized data, but it is the best option in terms of performance if you have more reads than writes in this collection.
Option 2 - Aggregate
MongoDB supports aggregates (https://docs.mongodb.com/manual/aggregation/) and it has a pipeline stage called $lookup (https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/) that you can use in order to perform your query using a single api call/database operation. Parse Server supports aggregates as well in its API and JS SDK (https://docs.parseplatform.org/js/guide/#aggregate) but unfortunately not directly from client code in Swift because this operation requires a master key in Parse server. Therefore, you will need to write a cloud code function that performs the aggregate query for you and then call this cloud cloud function from your Swift client code.