Need Help in Lookup Query Optimisation Mongodb

help
1

Hi Everyone,

We have a social app, in which users will get feed from its network, we are storing the network-related data in separate collections, but the API is taking more time to be executed,

Sharing the code,

 var data = Promise.await(
        Feed.rawCollection()
          .aggregate(
            feedQuery({user_id: user_id, limit: 0 })
           )
          .toArray()
      );
export function feedQuery({ user_id, limit }) {
    var obj = [];
    obj.push({
      $match: {
        $and: [
          {
            is_active: true,
          },
        ],
      },
    });
  
  
    obj.push({
      $lookup: {
        from: "user",
        localField: "created_by",
        foreignField: "user_id",
        as: "user_data",
      },
    });
  
  
    obj.push({
      $lookup: {
        from: "followers",
        let: {
          follower_of: "$created_by",
          user_id: user_id,
          // user_type: "USER",
        },
        pipeline: [
          {
            $match: {
              $expr: {
                $and: [
                  { $eq: ["$$follower_of", "$follower_of"] },
                  { $eq: ["$$user_id", "$user_id"] },
                  { $eq: [true, "$is_active"] },
                  { $eq: [1, "$status"] },
                ],
              },
            },
          },
          {
            $limit: 1,
          },
        ],
        as: "is_follower",
      },
    });

    obj.push({
      $lookup: {
        from: "followers",
        let: {
          follower_of: user_id,
          user_id: "$created_by",
        },
        pipeline: [
          {
            $match: {
              $expr: {
                $and: [
                  { $eq: ["$$follower_of", "$follower_of"] },
                  { $eq: ["$$user_id", "$user_id"] },
                  { $eq: [true, "$is_active"] },
                  { $eq: [1, "$status"] },
                ],
              },
            },
          },
  
          {
            $limit: 1,
          },
        ],
        as: "is_following",
      },
    });
  
  
  
    obj.push({
      $match: {
          $or: [
            **// { **
**            //   is_follower:{**
**            //     $gt: [{ $size: "$is_follower" }, 0],**
**            //   }**
**            // },**
**            // { **
**            //   is_following:{**
**            //     $gt: [{ $size: "$is_following" }, 0],**
**            //   }**
**            // },**
            { created_by: user_id },
            { created_by: "3c85cdebade1c51cf64ca9f3c09d182d" },
          ],
      },
    });
    obj.push({
      $sort: {
        created_at: -1,
      },
    });
  
    obj.push({
      $limit: 5,
    });
  
    obj.push({
      $project: {
        following_count: 1,
        followers_count: 1,
        user_data: {
          user_id: 1,
          name: 1,
          email: 1,
          user_type: 1,
          association_type: 1,
          profile_picture: 1,
        },
        // is_following: {
        //   _id: 1,
        // },
        // is_follower: {
        //   _id: 1,
        // },
        is_creator: {
          user_id: 1,
          name: 1,
          email: 1,
          user_type: 1,
          association_type: 1,
          profile_picture: 1,
        },
        user_liked_this_post: 1,
        special_post_type: 1,
        special_post: true,
        event_id: 1,
        blog_id: 1,
        group_id: 1,
        event_details: 1,
        group_details: 1,
        blog_details: 1,
        like_details_count: { $size: "$like_details_count" },
        feed_id: 1,
        content: 1,
        post_images: 1,
        is_active: 1,
        metadata_post: 1,
        created_at: 1,
        created_by: 1,
        total_likes: 1,
        total_comments: 1,
        metadata_details: 1,
        user_commented: 1,
        abusive_posts_: { $gt: [{ $size: "$abusive_posts" }, 0] },
      },
    });
    obj.push({ $match: { abusive_posts_: false } });
  
    return obj;
  }

Although, I have removed 4-5 more looks for getting the likes & comments on a particular post but overall the highlighted section is causing the issue,

if is_follower & is_following check in the match query is removed then time is reduced to 2s else its 6 seconds, if there is any other way of executing the same then please let me know.

Any help would be greatly appreciated.

Thanks

2

This looks sub optimal and a resource hog.

You want to avoid all of these pipelines and aggregates and just use the power of $in (https://www.mongodb.com/docs/manual/reference/operator/query/in/) and an array for each of your filters and get it all into one fast to execute, indexed query. The following example assumes use of a simplschema class named ‘Feed’ and meteor pubsub and achieves a similar effect in much fewer lines.

Meteor.publish('Feed', function (limit) {
    //default limit if none set
    var dl = limit || 5;
    return Feed.collection.find(
        {
        user_id: user_id, 
        is_active: true, 
        followers: {$in: {arrayOfFollowerIDs}}, 
        _id: {$nin: {arrayOfAbusivePostIDs}},
        },
        {
        sort: {created_at: -1}, 
        limit: dl
        });

The inverse of $in is not in ($nin) - https://www.mongodb.com/docs/manual/reference/operator/query/nin/

3

Hi @truedon,

What if we need to have a lookup as we need to show data on the frontend also

4

Just use a pubsub for your look up and subscribe to it based on user Id and send a limit for instance get 10 users posts based on userId

Instance.Collection.Find({user: userId}).limit(10)