Table joins

General
1

Is there any equivalent of a join query?
I have a Videos table, and a Follows table
If
I want to display a feed of videos from users that I follow how would I
select from the following table and filter results of the videos table
based on that?

2

Hi Charlie,

It might be possible, but I’d like to ask about specific relations between the tables. I assume there is a one-to-many between Follows and Videos. How is the relation expressed to define users who follow you? Is there a relation between Users and Follows?

Regards,
Mark

3

So this involves 3 tables. Users, Follows, and Videos. The Follows table
is a join between users(instead of using direct many-to-many, the join
table gives me a timestamp of when a user followed another for an
activity feed). So when using Parse I was able to select all the users
that I am following from the following table then load a video feed
based on those users. Ex.


- (PFQuery *)queryForTable {
    // Query for the friends the current user is following
    PFQuery *followingActivitiesQuery = [PFQuery queryWithClassName:kPAPActivityClassKey];
    [followingActivitiesQuery whereKey:kPAPActivityTypeKey equalTo:kPAPActivityTypeFollow];
    [followingActivitiesQuery whereKey:kPAPActivityFromUserKey equalTo:[PFUser currentUser]];
 
    // Using the activities from the query above, we find all of the photos taken by
    // the friends the current user is following
    PFQuery *photosFromFollowedUsersQuery = [PFQuery queryWithClassName:self.className];
    [photosFromFollowedUsersQuery whereKey:kPAPPhotoUserKey matchesKey:kPAPActivityToUserKey inQuery:followingActivitiesQuery];
    [photosFromFollowedUsersQuery whereKeyExists:kPAPPhotoPictureKey];
 
    // We create a second query for the current user's photos
    PFQuery *photosFromCurrentUserQuery = [PFQuery queryWithClassName:self.className];
    [photosFromCurrentUserQuery whereKey:kPAPPhotoUserKey equalTo:[PFUser currentUser]];
    [photosFromCurrentUserQuery whereKeyExists:kPAPPhotoPictureKey];
 
    // We create a final compound query that will find all of the photos that were
    // taken by the user's friends or by the user
    PFQuery *query = [PFQuery orQueryWithSubqueries:[NSArray arrayWithObjects:photosFromFollowedUsersQuery, photosFromCurrentUserQuery, nil]];
    [query includeKey:kPAPPhotoUserKey];
    [query orderByDescending:@"createdAt"];
    . . .
    return query;
}
4

Hi Charlie,

I am not familiar with Parse’s query format and the code you posted is hard to follow, especially getting into composite elements.

Suppose the Users table has a column called ‘videos’ which is a 1:N relation with the table Video. So any user has a collection of videos. At the same time there is a column in the Users table called ‘following’ which is a 1:N collection to the Users table (meaning every user has a collection of other users he follows).

The question is how to get all the videos for the people I follow.

I could not figure out a way to build a single query approach. The ultimate whereClause query which would be sent to the Video table (since you’re loading videos) can be in the following format:

Users[videos].email in ('user1email', 'user2email', 'user3email'...)

or if you prefer to use objectId to identify users, it would be this:

Users[videos].objectid in ('user1objectId', 'user2objectId', 'user3objectId'...)

This essentially means that in order to run the query above, you need to identify a list of email addresses (or object ids) of the people you follow. It can be done just by loading the value for the “following” column/property of the current user.

Hope this helps.

Mark

5

So my User doesn’t have a Videos column or a following column. Videos have a single userId and I need the Followings table to be separate because I need to build an activity feed based on when a user Followed another. Also isn’t the limit ~250 object for queries? So if the user is following more than 250 people this will take many queries.

Mt tables are as such

-USER- |userId|

-Video- |userId|

-Following- |toUserId| |fromUserId|

This is the only thing that I can’t seem to work out with Backendless but is absolutely necessary for my app.

6

Hi Charlie,

With the schema you have, to get a list of videos of the people you follow, it would take 2 queries:

  1. load all objects from Following where:
 fromUserId = 'your user id'
  1. get all videos from Video where:
userId in ('userid1', 'userid2', 'userid3'....)

the userid1, userid2, userid3 are the objects received from the first query.

For performance reasons I would recommend creating a custom service which you can deploy into Backendless API Engine. If both queries run on the server the client side would be making a single call and getting all the data.

Hope this helps.

Regards,
Mark

7

Hi Charlie,

were you able to create a solution for your case? Would you mind sharing it with others?

I think this is a very common problem people will have to go through when building a social app. I’d be great if there was some example code.

Thank you!

8

Mark,

How does paging work in this example? For instance, let’s say there are 10,000 videos returned from the query of Video. Let’s also assume the Video table has another column, “title”, and that Charlie wants to return the results in alphabetical order by each video’s title. How would he do that using a custom service? Would he have to handle paging manually?

Thanks!
Joseph

9

If videos are retrieved as a collection of “related” objects, there is no way to set a sorting order for them with the default implementation. The sorting options works only for the primary table where you send your query.

Also, if you’re building a mobile app (or even a web app), I would not recommend returning 10,000 objects back to the client (as tempting as it may sound )))

Cheers,
Mark

10

Hi Mark,

I think I did a bad job at explaining my question. I would never return 10,000 objects to an iOS app. lol. Let me clarify.

In this example, you suggest creating a custom service to deploy into the Backendless API Engine. Within the code of this custom service, you first suggest to get “all” objects from Following where fromUserId = “your user id”. Then, secondly (also within the code of the custom service), you suggest to get all videos from Video where userId is in an array of toUserId from the results of the previous query. Is that correct?

If so, lets assume two things:

    There exist a total of 35,000 Following objects that match your first query (fromUserId = "your userId") There exist a total of 10,000 Video objects that match your second query
In this case, we would, of course, never just go ahead and return 10,000 results to an iOS app. They would have to be returned in pages. I have a few questions about this:
    When I make the second query (of the Video table) within my custom service code in the API engine, do I have access to all existing results (the full 10,000) in memory from within the scope of that service code, or do I only have access to a subset of the full 10,000 videos at a time? In other words, if I wanted to, could I iterate over all 10,000 and make changes before returning any of the results to the iOS app? Does the custom service in the Backendless API engine provide an automatic way for me to return these 10,000 results in pages, or do I have to implement my own code in my custom service to deliver those results in pages?
This should get to the bottom of what I am trying to do. I'll ask about sorting when we get this clarified. Thanks!!
11

Hi Joseph,

Thank you for clarifying. My answers are below:

When I make the second query (of the Video table) within my custom 
service code in the API engine, do I have access to all existing results
 (the full 10,000) in memory from within the scope of that service code,
 or do I only have access to a subset of the full 10,000 videos at a 
time? In other words, if I wanted to, could I iterate over all 10,000 
and make changes before returning any of the results to the iOS app?

The second query would return data paged. Which means you get only a subset of the full collection at a time. In Backendless Cloud the most records you can get in a single request is 100. In Backendless Pro and Managed Backendless that number is configurable and can be higher.

Does the custom service in the Backendless API engine provide an 
automatic way for me to return these 10,000 results in pages, or do I 
have to implement my own code in my custom service to deliver those 
results in pages?

API Engine is a combination of two main components: (1) a container of custom business logic and (2) a processing mechanism which turns any arbitrary Java and Node.js code into an API service. With that said, any specific functionality for working with app’s data would be within your code.

Hope this helps.

Regards,
Mark

12

I am interpreting your response to mean the following:

    When I make my first query of the Following table in my Backendless API service code, I will only receive 100 Following objects at a time. In other words, there is no way my custom service code can access "all" Following objects using a single query. So, assuming I queried the Following table only once, my second query to the Video table would be based only on the first 100 returned Following objects, not all Following objects that exist that match my first query.
Lets assume again that:
    There exist a total of 35,000 Following objects that match the first query (fromUserId = "your userId") There exist a total of 10,000 Video objects that match the second query
Given the above, how do I return, in pages of course, *all* videos of *all* the people I follow. My first guess at a solution would be the following:
    Query the Following table once, receiving 100 Following objects. Query the Video table using those 100 Following objects, and receive 100 Video objects. Continue to return pages from the Video table query until no objects remain. Repeat this process with the next page of Following objects until all following objects have been processed.
However, there are flaws to this solution. For example, its possible that I would find no Video objects that match any of the objects from the Following table until I have requested the 30,000th Following object. In such a case, I would have received hundreds of pages of results from Following, and then executed hundreds of queries on Video without returning any Video results. This doesn't seem very performant. Is there some other way of doing this?