How to use Parse.Query with pagination and order by pointer's field

[phatnguyenbk]

+--------------------------------------------------------+
|                      _User class                       |
+----------+---------------+-----------+-----------------+
| objectId | totalFavorite | totalView | totalConversion |
+----------+---------------+-----------+-----------------+

• User.totalFavorite is sum of Post.favoriteCount by each user.
• User.totalView is sum of Post.viewCount by each user.
• User.totalConversion is sum of Post.conversionCount by each user.

+-----------------------------------------------------------------------------------+
|                                   Post class                                      |
+----------+--------------------------+---------------+-----------+-----------------+
| objectId | createdBy (User Pointer) | favoriteCount | viewCount | conversionCount |
+----------+--------------------------+---------------+-----------+-----------------+

Relation between of _User and Post is one-many

I want to get all of posts with sort by popular-user ranking (priority: totalConversion < totalView < totalFavorite) and Pagination.

I do not have any idea for the best solution (easy and fast).

Please help me. Thanks all.

[natanrolnik]

@phatnguyenbk please read the docs on queries at Parse.com/docs for the specified language. You should probably use the skip parameter of a query. In JS, for example: https://parse.com/docs/js/guide#queries-query-constraints
Also, there is an implementation of pagination in the PFQueryTableViewController class in ParseUI for iOS.

This is not a parse-server related issue. Please close this issue.

[phatnguyenbk]

@natanrolnik
Thanks for your reply.

I knew about that. But maybe you don't understand what I mean.

Parse.Query do not support about order by pointer's field. So it's difficult for pagination.

I just want to discuss about for the best solution for this topic.

Thanks.

[natanrolnik]

Oh, now I'm see. I'm sorry.
Is there anything in your mind that you can propose in details?

[phatnguyenbk]

I'm thinking about 2 solutions.
Sol 1: Instance of storing totalFavorite, totalView, totalConversion in User, they will store in Post, maybe duplicate data for all of documents each user. I just order by on Post.

Sol 2: I create a new class Ranking (Pointer's Post, Pointer's User, rank), store list ordered Post by ranking, set crontab end of the day run to create data for ranking.

[sdf611097]

How about add new column(uid) in the collection?

Add beforeSave to get the pointer id, and then set uid. (for old client).
Write a script(query not exist uid) to trigger beforeSave to init the value.

[Silkjaer]

It would be AWESOME to be able to order based on pointer field values.

[dimovdaniel]

Any updated on this issue?

[dplewis]

@dimovdaniel You should be able to use aggregate query for order by pointers. A combination of $unwind + $lookup + $sort should do the trick. I haven't tested it tho.

[khalilsarabey]

@dplewis do you have an example for that please? I've been trying to do that but it's not working for me.

Thanks

[dplewis]

@khalilsarabey Here is an example in js and mongo shell

fit('order by pointer', (done) => {
    const pointer1 = new TestObject({ value: 1});
    const pointer2 = new TestObject({ value: 2});
    const pointer3 = new TestObject({ value: 3});

    const obj1 = new TestObject({ pointer: pointer1 });
    const obj2 = new TestObject({ pointer: pointer2 });
    const obj3 = new TestObject({ pointer: pointer3 });
    const pipeline = [
      {
        project: {
          tempPointer: { $substr: [ "$_p_pointer", 11, -1 ] }, // Remove TestObject$
        }
      },
      {
        lookup: {
          from: "test_TestObject",
          localField: "tempPointer",
          foreignField: "_id",
          as: "tempPointer"
        }
      },
      {
        unwind: {
          path: "$tempPointer",
        }
      },
      { sort : { "tempPointer.value" : -1 } }
    ];
    Parse.Object.saveAll([pointer1, pointer2, pointer3, obj1, obj2, obj3]).then(() => {
      const query = new Parse.Query(TestObject);
      return query.aggregate(pipeline);
    }).then((results) => {
      expect(results.length).toEqual(3);
      expect(results[0].tempPointer.value).toEqual(3);
      expect(results[1].tempPointer.value).toEqual(2);
      expect(results[2].tempPointer.value).toEqual(1);
      console.log(results);
      done();
    }).catch((error) => {
      console.log(error);
      done();
    });
  });

  fit('empty', (done) => done()); 
// fit allows only these tests to run instead of the entire test suite
// Using fit on one test results prevents objects in the database from deleting on the first test run.
// Using fit('empty', (done) => done());  ensures that objects get deleted every run

Mongo Shell

db.test_TestObject.aggregate([
  {
    $project: {
      tempPointer: { $substr: [ "$_p_pointer", 11, -1 ] }, // Remove TestObject$
    }
  },
  {
    $lookup: {
      from: "test_TestObject",
      localField: "tempPointer",
      foreignField: "_id",
      as: "tempPointer"
    }
  },
  {
    $unwind: {
      path: "$tempPointer",
    }
  },
  { $sort : { "tempPointer.value" : -1 } }
]);

[khalilsarabey]

@dplewis Thank you so much!

[dplewis]

Closing via parse-community/Parse-SDK-JS#921 (comment)

By the time we get the pointers it will be too late to do paging unless we implement leftJoin (supported after Mongo 3.4). LeftJoin has been tried but its a deep rabbit hole. If anybody is interested please feel free to open a PR.