Relational queries in Cloud Code (parse.com)

Question

Table structure:

Collection
name (String)

Image
collection (Pointer<Collection>)
url (String)
position (Number)

Image class has the column collection which is a pointer to the Collection class.

position is used to sort Images within a Collection.

---

What would be the most efficient way in Cloud Code to accomplish the following?

Lets assume I have ~3000 images and 3 collections.

What would be the best way to construct a query that returns an array containing all collections that have at least one associated Image and show only the first 5 images of each collection, sorted by position

Associated images for each collection would need to be included in the response and could look vaguely like this:

results: [{
  collection: {
    name: 'foo'
  },
  images: [{
    position: 0,
    url: 'test.jpg'
  },
  {
    position: 1,
    url: 'test.gif'
  }]
}, {

  ...

}]    

All I can think of at the moment is doing two queries, one to get all Collections and another to get all Images upfront and then filter them, which seems quite backwards, coupled with the fact that Parse is limited to result sets of 1000 at maximum. Do I need to rethink my table structure?

Answer 1

You can do this in a single call, given your constraints.

If you have a small number of Collection objects and you're using a relative position (each Collection has an Image where position = 1, 2, etc.), there's no need to overthink the problem. These constraints mean there are relatively few items where 'position' < 6 (or 5, if 0 is your first index position).

Simply make a call on the Image class and restrict it to objects where position is less than or equal to 5. Use the .include() to return the Collection object attached to each Image object as part of the return. Then sort the Image objects locally in whatever format you see fit.

Remember that Parse limits the number of objects than can be returned to 100, and you can increase that to up to 1000. If you .include() for the Collection on each Image, it means each returned Image counts for 2 objects, so you can return up to 500 Image objects. Sounds like this is well above your expected needs.

Voilá - one call, all your objects.

var Image = Parse.Object.extend("Image");
var query = new Parse.Query(Image);
query.lessThan("number", 6); //Assumes a lowest index of 1; switch to 5 if your lowest index number is 0
query.include("collection");
query.find({
  success: function(results) {
    //Sort the results according to Collection, send to controllers, etc.
    //Each result will have a pulled, valid Collection object ready on its .collection
  },
  error: function(error) {
    alert("Error: " + error.code + " " + error.message);
  }
});

UPDATE It is also worth noting that this approach outperforms a 'nested query' approach, both for a small number of collections or at scale (1000s or 10,000s of collections)

Answer 2

With that data structure, you will have multiple (nested) queries, but done in CloudCode it should still be plenty fast when you use proper limits and promises.

Try it in curl here:

curl -X POST   -H "X-Parse-Application-Id: 1ClVjWioWtW4uz8Nt7zYiXHkoNYQao6Z1rdXd8Gt"   -H "X-Parse-REST-API-Key: VlYbFT8uhPpsYeRW5ezIn1A8bWa5N9OW9riqs4ji"   -H "Content-Type: application/json"   -d '{}'   https://api.parse.com/1/functions/collectionSummary

Cloud Code:

var _ = require('underscore');

Parse.Cloud.define("collectionSummary", function(request, response) {
    var collections = [];
    var collectionQuery = new Parse.Query("Collection");
    collectionQuery.find({
        success: function(results) {
            var promises = [];
            _.each(results, function(collection) {
                var name = collection.get("name");
                var imageQuery = new Parse.Query("Image");
                imageQuery.equalTo("collection", collection);
                imageQuery.ascending("position");
                imageQuery.limit(5);
                promises.push(imageQuery.find({
                    success: function(images) {
                        if (images.length > 0) {
                            collections.push({ "collection":collection, "images":images });
                        }
                    },
                    error: function() {
                        response.error("Could not read images.");
                    }
                }));
            });
            Parse.Promise.when(promises).then(function() {
                response.success(collections);
            });
        },
        error: function() {
            response.error("Could not read collections.");
        }
    });
});

Output:

{
  "result": [
    {
      "collection": {
        "__type": "Object",
        "className": "Collection",
        "createdAt": "2015-05-11T21:52:28.406Z",
        "name": "Apple",
        "objectId": "cpTsJBNc9q",
        "updatedAt": "2015-05-11T21:52:28.406Z"
      },
      "images": [
        {
          "__type": "Object",
          "className": "Image",
          "collection": {
            "__type": "Pointer",
            "className": "Collection",
            "objectId": "cpTsJBNc9q"
          },
          "createdAt": "2015-05-11T22:12:35.152Z",
          "objectId": "0AmtlLrlHT",
          "position": 0,
          "updatedAt": "2015-05-11T22:12:49.463Z",
          "url": "test0"
        },
        {
          "__type": "Object",
          "className": "Image",
          "collection": {
            "__type": "Pointer",
            "className": "Collection",
            "objectId": "cpTsJBNc9q"
          },
          "createdAt": "2015-05-11T22:12:58.185Z",
          "objectId": "WbomPr3hUK",
          "position": 1,
          "updatedAt": "2015-05-11T22:13:05.523Z",
          "url": "test1"
        },
        {
          "__type": "Object",
          "className": "Image",
          "collection": {
            "__type": "Pointer",
            "className": "Collection",
            "objectId": "cpTsJBNc9q"
          },
          "createdAt": "2015-05-11T22:19:02.836Z",
          "objectId": "ASLInM7Hu5",
          "position": 15,
          "updatedAt": "2015-05-11T22:19:08.990Z",
          "url": "test8"
        },
        {
          "__type": "Object",
          "className": "Image",
          "collection": {
            "__type": "Pointer",
            "className": "Collection",
            "objectId": "cpTsJBNc9q"
          },
          "createdAt": "2015-05-11T22:19:14.719Z",
          "objectId": "VkCF98Ts0N",
          "position": 20,
          "updatedAt": "2015-05-11T22:19:20.699Z",
          "url": "test9"
        },
        {
          "__type": "Object",
          "className": "Image",
          "collection": {
            "__type": "Pointer",
            "className": "Collection",
            "objectId": "cpTsJBNc9q"
          },
          "createdAt": "2015-05-11T22:19:27.032Z",
          "objectId": "I2mEG20bfp",
          "position": 40,
          "updatedAt": "2015-05-11T22:19:37.554Z",
          "url": "test10"
        }
      ]
    },
    {
      "collection": {
        "__type": "Object",
        "className": "Collection",
        "createdAt": "2015-05-11T21:52:35.297Z",
        "name": "Banana",
        "objectId": "zxPfnWlm1T",
        "updatedAt": "2015-05-11T21:52:35.297Z"
      },
      "images": [
        {
          "__type": "Object",
          "className": "Image",
          "collection": {
            "__type": "Pointer",
            "className": "Collection",
            "objectId": "zxPfnWlm1T"
          },
          "createdAt": "2015-05-11T22:16:05.924Z",
          "objectId": "vK9O7b5vR6",
          "position": 0,
          "updatedAt": "2015-05-11T22:16:12.607Z",
          "url": "test2"
        },
        {
          "__type": "Object",
          "className": "Image",
          "collection": {
            "__type": "Pointer",
            "className": "Collection",
            "objectId": "zxPfnWlm1T"
          },
          "createdAt": "2015-05-11T22:16:26.676Z",
          "objectId": "oMEMgwauEi",
          "position": 1,
          "updatedAt": "2015-05-11T22:16:30.750Z",
          "url": "test3"
        },
        {
          "__type": "Object",
          "className": "Image",
          "collection": {
            "__type": "Pointer",
            "className": "Collection",
            "objectId": "zxPfnWlm1T"
          },
          "createdAt": "2015-05-11T22:16:35.378Z",
          "objectId": "0sAbNK1LbN",
          "position": 2,
          "updatedAt": "2015-05-11T22:16:39.475Z",
          "url": "test4"
        },
        {
          "__type": "Object",
          "className": "Image",
          "collection": {
            "__type": "Pointer",
            "className": "Collection",
            "objectId": "zxPfnWlm1T"
          },
          "createdAt": "2015-05-11T22:16:42.984Z",
          "objectId": "QVZJmHQJ6E",
          "position": 3,
          "updatedAt": "2015-05-11T22:16:52.405Z",
          "url": "test5"
        },
        {
          "__type": "Object",
          "className": "Image",
          "collection": {
            "__type": "Pointer",
            "className": "Collection",
            "objectId": "zxPfnWlm1T"
          },
          "createdAt": "2015-05-11T22:16:56.827Z",
          "objectId": "NGS39LCAJL",
          "position": 4,
          "updatedAt": "2015-05-11T22:17:01.401Z",
          "url": "test6"
        }
      ]
    }
  ]
}