Obtain sorted related data

General
1

Hello Backendless team.

My question is: Supppose i have two tables Conversations and Messages.
Conversations has 1:N property ‘messages’.
When i do request for Conversations its return all Conversations with array [Messages], wich not sorted.
Is there a way to get sorted related data without using additional requests?

Thanks.

2

Hello,

To get related data in sorted order, you need to use the two-step retrieval API.

Regards,
Mark

3

Hello @mark-piller ! At first thank you for your awesome product! I really have shocked about opportunities Backendless. Also thanks for professional team, which always try to help you with any issues!

So what about my question, technically i use DataQueryBuilder object, which allow me to obtain array [Conversations], with all needed data, exept sorted property ‘messages’.
Your answer assume using LoadRelationsQueryBuilder object, but this mean repeat new request for each element of array [Conversations]. It could be waste of API…
I also dont find any properties with type LoadRelationsQueryBuilder inside DataQueryBuilder class (ios sdk).

Regards, Dmitry

4

Hello Dmitry,

thank you for your kind words!

While using DataQueryBuilder with a single-step retrieval (Conversations list with messages in each conversation object) you’re limited to retrieving no more than 100 messages per each conversation object. Definitely not what you want. The two-step retrieval API allows you to fetch all related objects (of course using pagination if there are more than 100 messages in conversation). Also, it allows specifying sorting, properties, etc using LoadRelationsQueryBuilder.
Indeed it will require additional API requests, but that should not be a problem.

As for iOS SDK, our iOS engineer will assist you here.

Regards,
Stanislaw

5

Hello @Dmitry_Kalenkov,

I also dont find any properties with type LoadRelationsQueryBuilder inside DataQueryBuilder class (ios sdk).

The LoadRelationsQueryBuilder and DataQueryBuilder are 2 different classes.
The LoadRelationsQueryBuilder allows to get a related objects list for a specific relation property in a parent object is retrieved from the server.
This class also has a sortBy and properties fields, e.g:

let loadRelationsQueryBuilder = LoadRelationsQueryBuilder(relationName: "friends")
loadRelationsQueryBuilder.sortBy = ["name"]
loadRelationsQueryBuilder.properties = ["name"]
        
Backendless.shared.data.ofTable("Person").loadRelations(objectId: "88BF9CE7-AA84-4FF5-966D-A80D44E16AE0", queryBuilder: loadRelationsQueryBuilder, responseHandler: { response in
    if let friends = response as? [[String : Any]] {
        for friend in friends {
            print(friend["name"] ?? "")
        }
    }
}, errorHandler: { fault in
    print("Error: \(fault.message ?? "")")
})

The more information about two-step retrieval with examples can be found in our documentation.

6

Hello @stanislaw.grin, @olhadanylova.

Thanks for your answers!

I completely understand all what you are talking about. I have figured out how Backendless requests works. I also know that LoadRelationsQueryBuilder and DataQueryBuilder are 2 different classes. I just said that i dont have opportunitie using LoadRelationsQueryBuilder object as DataQueryBuilder property (together using).

I also dont need more than 100 messages per each conversation object. Screen with messages - anoter screen (with own pagination functions). I need last 100 messages (sorted created) for each conversation to continue sort it on client. (show last, chek count of unreaded, etc.)
My guess right now Backendles return related objects sorted by objectId… But this is mean returned related data unvalid to me.

7

Hi all…I am dealing with the same (at least very similar) situation. I have a Conversation with Messages and each Message is sent by Someone. If I use DataQueryBuilder I can go deep enough to retrieve all object relation levels, BUT I cannot sort by the Messages relation so I get a random set of messages. Obviously this doesn’t work for a messaging app so I tried LoadRelationsQueryBuilder. This works great for sorting the Messages, but it’s not possible to get “relations of relations” so each Message does not come back with the Someone who sent the message. In this scenario I need to run another query for EACH message just to get the Someone who sent it. It feels like I’m approaching this wrong or:

  1. DataQueryBuilder needs the ability to sortBy a relation

or

  1. LoadRelationsQueryBuilder needs the ability to load relations of relations like DataQueryBuilder can.

For now it seems the only solution is to run another query for every msg just to get the sender which feels very hacky.

8

Hi David,

Have you considered creating a View (which functions as a read-only table) that will have all the information from multiple tables? This way you can have both messages and their senders in the same view.

Regards,
Mark

9

Hi Mark…speedy reply thank you. No I haven’t tried that, but I will look into that option now.

10

Yes…views are probably the solution. Am I able to cast the response to an object or do I need to parse the arrays. I am using iOS…

11

You can use either the class-based or the dictionary -based approach when working with views.

Regards,
Mark

12

Hi @David_Thompson
I don’t find clear solution, but i handle this issue using Cloud Code… SO:

  1. We need “Conversation” table with 1:N property “persons” (BackendlessUser)
  2. We need “conversationId” property (String) for every message for “Message” table.

My Cloud Code:

class Conversations {

/**
 * @param {String} currentUserId
 * @param {String} pageSize
 * @param {String} offset
 * */
async getConversations(currentUserId, pageSize, offset) {
    const conversationsStore = Backendless.Data.of('Conversation')
    const messagesStore = Backendless.Data.of('Message')

    const ITEMS_PER_PAGE = pageSize
   
    const converstionsQuery = Backendless.Data.QueryBuilder.create()
      .setWhereClause(`persons.objectId = '${currentUserId}'`)
      .setPageSize(ITEMS_PER_PAGE)
      .setGroupBy('objectId')
      .setSortBy('updated desc')
      .setRelated( ["persons"])
      .setOffset( offset )
     
    const mainConverstions = await conversationsStore.find(converstionsQuery)
    if (!mainConverstions.length) { return [] }


    for (var i = 0; i < mainConverstions.length; i++) {
        var conversation = mainConverstions[i]

        const messagesQueryBuilder = Backendless.Data.QueryBuilder.create()
        .setWhereClause(`conversationId = '${conversation.objectId}'`)
        .setPageSize( 100 )
        .setSortBy('created desc')
        .setGroupBy('objectId')

     const messages = await messagesStore.find(messagesQueryBuilder)
    
        conversation.messages = messages
    }

    return mainConverstions
   }

}

Backendless.ServerCode.addService(Conversations);

Result:
I have get array of [Conversation] with related data AND sorted (desc) array of [Message] for each conversation .

13

@Dmitry_Kalenkov using cloud code was a neat approach, thanks for sharing

@mark-piller I ended up using the View approach. It is easier to maintain for me. Thanks for exposing the View features of the db…very powerful tool