Join equivalent for find, Fetched Properties

General
1

I have this data model:
Album <—>> Photo <—>> Like <<—> User
I want to get the photos from specific Album and I want to attach Likes to them as well. I would like to have some equivalent of a Fetched Property, because I don’t want to get an array of likes to each photo, I just want to have the one like.

my original SO question:

2

Hi Miroslav,

I have a few questions which would help me with an answer for yours:

Is “Like” a separate table? What do you store in it?
What do you mean by “one like” ?

Regards,
Mark

3

Album, Photo, Like, User are separate tables, one like is one row in the Like table. Multiple Likes can have relationship to one photo, but I only want to receive one particular Like row, the one that also has relationship to the current user. I was trying to illustrate this in the schema:
Album <—>> Photo <—>> Like <<—> User

where —> is 1:1 relationship and —>> is 1:N relationship

4

With backendless, it’s 2! queries. For performance reasons I’d do this inside cloud code.

Otherwise user something like elastic search (If you want more performance).

5

Miroslav,

Please see my response here: (that question is almost identical to yours):
http://support.backendless.com/t/tabe-joins#comment-9182

Regards,
Mark

6

@Mark: This is something different, Charlie is querying a list of videos, I don’t want a list, I want to narrow down sublists.

Imagine this:
Album <—>> Photo <—>> Like <<—> User
User

  • name
  • email

Like

  • userId
  • photoId
  • value

Photo

  • albumId

Album

  • title
  • userId

Please see that every photo can have many Likes. What I want to get is a collection of photos for a specific album with one specific Like entity (table row). Like this:
{
Photo

  • ID = 1
  • albumId = 5
  • like:
    Like
  • ID = 38922
  • photoId = 1 // note this value, this is the photo’s id
  • userId = 14 // note this value, this is my user’s id
    ,
    Photo
  • ID = 2
  • albumId = 5
  • like:
    Like
  • ID = 38923
  • photoId = 2 // note this value, this is the photo’s id
  • userId = 14 // note this value, this is my user’s id

}

Basically, I need to know, if I’ve liked this photo or not.
How can I do it? Should I use “Backendless Logic”? If so, how? I believe this is very common case and there’s already some example I could use.

Thank you very much!

7

To know if a user liked a photo or not, you can do this:

whereClause = "photoId = photo_id_value and userId = user_id_value"
BackendlessDataQuery query = new BackendlessDataQuery( whereClause );
BackendlessCollection&lt;Like&gt; likes = Backendless.Data.of( Like.class ).find( query );


if( likes.getCurrentPage().size() > 0 )
{
 // yes, user with user_id_value liked photo with photo_id_value
}
8

That’s exactly what I was trying to avoid because now I would have to make a new query for every photo.

Actually, I’ll have to make multiple queries for each photo if I want to know this information:

  • if I like it
  • how many people like it
  • names of the first 5 people who like it
  • total number of comments
  • first 5 comments and people who commented

I want my app to be fast, so when the user scrolls the collectionView, they don’t have to wait for the data.

That’s why I was asking here because I believe there is a way how to get that information more efficiently.

9

All your required data could be added to the photo object.

Add object ID’s of user liking the photo as comma seperated values or json into a string value (likeUserIds).
You can count them -> Num of likes
You can check if you liked it -> contains your userID
5 names -> Same as userID, csv/json as String value (likeUserNames)

Get’s you all the information in one document/query.

10

@Jens Thank you for the message. I’m trying to build an app, where there will be not tens but tens of thousands likes on each photo. Imagine you’d have to query 10 000 or 100 000 id’s on each photo just to know if you’ve liked it or not. So this solution is not viable in my case.

Even downloading the data on a cellular network - 25 photos, 100 000 likes each, that’s staggering 25MB of data if every id was represented by 10 byte string. And that’s not even counting the image data:)

11

As this is very important, I’ve started a bounty on stack overflow:

12

Miroslav,

I believe what you want to accomplish is entirely possible, however it would require some changes in the schema to optimize the performance and minimize the number of queries. For example, the following data items could actually be pre-calculated for every photo thus removing the need to run additional queries to get the values.

  • how many people like it
  • names of the first 5 people who like it
  • total number of comments
  • first 5 comments and people who commented
    As the number of likes changes, you can add API Event Handlers which would be responsible for maintaining the state and consistency on the per-photo basis.
    Regards,
    Mark
13

@Mark: of course I’ll do it like that. Thanks for the note. But that’s not the topic of my question. My question is how can I implement the fetching of “Like” relationship efficiently so I know if I like the photo or not.

14

Miroslav,

Would it be possible for you run the export of your app schema and email to support@backendless.com (or post here, it is up to you)? This way we would know exactly what the schema looks like and be able to assist.

Regards,
Mark

15

I’ve used different names in my example in the question. Here the important tables are:
User
Challenge (contains photos, = album in my example)
Vote (has a value, so it can be negative - downvote, = like in my example)
Photograph

export_2016_03_05_20_29_51.zip (1.49kB)

16

Thanks, Miroslav. I am trying to make sense of the schema to understand the semantics.

    I do not see any relationships in Challenge, it has only string values. Are any of the Challenge properties related to (or have same values from) other tables? Does the "user" relation in the Vote table represent a user that voted up/down a specific photograph? Do I understand it correctly that to know if a user "A" liked a photo "B", then there would be a record in the Vote table where "user" property points to object A in the Users table and the "photograph" property points to object B in the Photograph table?
Regards, Mark
17
    Photograph has a challenge relationship collumn, it may have been missing in the export yes yes
18

Thanks. In this case if the question is how to get all the photos a user liked, you would query the Vote table with the following whereClause:

user.objectId = ‘objectId-of-the-user-in-question’ and value > 0

Seems too easy and makes me feel that I missed something here.

19

obviously, there can be thousands of pictures that have been liked by this user. My question is, how to make it fast without having to fetch thousands of pictures and match it on the frontend.

Look at this:
https://www.instagram.com/developer/endpoints/media/
GET/media/media-id
Get information about a media object. Use the type field to differentiate between image and video media in the response. You will also receive the user_has_liked field which tells you whether the owner of theaccess_token has liked this media.
The public_content permission scope is required to get a media that does not belong to the owner of the access_token.

I’d like to apply best practices. I know how to use brute force, but that’s not what I want to do.

20

Is the goal to load all of them for the user or only a specific subset?

Backendless returns all the collections in a paged format, with most of 100 objects at a time, that would preclude the client-side from being overloaded.