How to efficiently lookup joining table and sort by that data

Working with Data
,
1

Background: We have a users table and a office location table. A user record has an office_location_id. We need to show a list of users and their office locations (10 results per page), and also be able to sort the data by office location name ASC or DESC.

We currently have an aggregate query, that pulls all user records and then does a lookup in the office locations table. Then we sort by that column, then skip/limit the results to get the first 10 results. It works perfectly fine, but extremely slowly (5s)

We have 30,000 users and each has an office location. I don’t know the internals of Mongo, but I assume it has to loop over each of those 30,000 users to do the office lookup for each one, just to then show only 10 results (i.e. it’s having to lookup 29,990 unneeded office locations).

If we move the lookup/sort after the skip/limit, then it becomes much quicker, by obviously only sorts the 10 results returned, leading to incorrect sorting per page (ABC,ABC instead of AAB,BCC). So we need to do the lookup and sort before skip/limit.

Is there a better way to do this sort of thing?

2

To efficiently look up data in a joining table and sort the results by that data in MongoDB, you can use the aggregate() method and specify a $lookup stage to perform the join, followed by a $sort stage to sort the results. Here is an example of how you might do this:

db.table1.aggregate([
    {
        $lookup: {
            from: "table2",
            localField: "id",
            foreignField: "table1_id",
            as: "table2_data"
        }
    },
    {
        $sort: {
            "table2_data.column1": 1
        }
    }
])

n this example, we are using the $lookup stage to perform the join between table1 and table2, using the id field in table1 and the table1_id field in table2. The results of the join are stored in the new table2_data field. We then use the $sort stage to sort the results by the column1 field in the table2_data field.

As with the SQL example, the specific details of your query will depend on the structure of your data and the specific data you are trying to retrieve. You can read more about the $lookup and $sort stages in the MongoDB documentation.

3

I’d also recommend potentially creating an index that could be used when joining the tables. That is if this is a key part of what you are doing and/or you regularly are searching for the field that you would index in your app.

6

I have the same case and I used sort after lookup, but it takes 15s+ to execute.
here is the query:

db.contacts.aggregate([
    {
        "$match": {
            "channels.platform_third_party_id": "252681238855909",
            "account_id": 3
        }
    },
    {
        "$lookup": {
            "from": "messages",
            "localField": "channels.contact_third_party_id",
            "foreignField": "contact_channel_id",
            "as": "unreadMessages",
            "pipeline": [
                {
                    "$match": {
                        "platform_third_party_id": "252681238855909",
                        "platform_type": "Modules\\Conversations\\Inbox\\Models\\FacebookPage",
                    }
                },
                // {
                //     "$group": {
                //         "_id": "$contact_channel_id",
                //         // "_unread_messages_count": {
                //         //     "$sum": {
                //         //         "$cond": [
                //         //             { "$eq": ["$read_at", null] },
                //         //             1,
                //         //             0
                //         //         ]
                //         //     }
                //         // },
                //         "_last_message_at": { "$max": "$created_at" }
                //     }
                // },
            ]
        }
    },
    // {
    //     "$addFields": {
    //         // "_unread_messages_count": { "$arrayElemAt": ["$unreadMessages._unread_messages_count", 0] },
    //         "_last_message_at": { "$arrayElemAt": ["$unreadMessages._last_message_at", 0] }
    //     }
    // },
    {
        "$sort": {
            "unreadMessages.created_at": -1
        }
    },
    {
        "$limit": 11
    },
    {
        "$project": {
            "unreadMessages": 0
        }
    }
]);

The commented parts are actually part of the query that I’m trying to make, but I commented everything to keep lookup and sort alone, and it still takes 15s+ to execute :sweat_smile:
any help?