Parse-server: 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 https://github.com/parse-community/Parse-SDK-JS/issues/921#issuecomment-531391139

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.