How do I perform Joinquery with search in MongoDB and go?

Question

Hello Everyone,

I am working on a project where customers can send account deactivation request and as admin I can see the list and deactivate them on a page, on the same page I have a search filter to filter by name, email and phone number. I have a collection to hold customers and another collection to save deactivation request. I have done the listing part as its simple with select query but facing problem to get list by search filters, I have following 2 structs in Go lang to get the record:


 type DeactivationRequest struct {
        Id                          int         `json:"id" bson:"id"`
        Uid                         int         `json:"uid" bson:"uid"`
        RequestTime                 int64       `json:"request_time" bson:"request_time"`
        Status                      int         `json:"status" bson:"status"`
        Note                        string      `json:"note" bson:"note"`
    }

    type User struct {
        Id                         int         `json:"id" bson:"_id,omitempty"`
        FirstName                  string      `json:"first_name,omitempty" bson:"first_name,omitempty"`
        LastName                   string      `json:"last_name,omitempty" bson:"last_name,omitempty"`
        EmailId                    string      `json:"email_id,omitempty" bson:"email_id,omitempty"`
        Password                   string      `json:"password,omitempty" bson:"password,omitempty"`
        PhoneNumber                string      `json:"phone_number,omitempty" bson:"phone_number,omitempty"`
        AltPhoneNumber             string      `json:"alt_phone_number,omitempty" bson:"alt_phone_number,omitempty"`
        Status                     int         `json:"status" bson:"status"`
        Role                       string      `json:"role,omitempty" bson:"role,omitempty"`
    }

I am facing problem with join query to get records based on search keywords for which I have written following code.


    result := []bson.M{}
        skip := 0
        limit := 20
        c := mongoSession.DB("database").C("deactivation_requests")
        pipe := c.Pipe([]bson.M{bson.M{"$match": bson.M{ "status" : 0 }},
                                bson.M{"$lookup": bson.M{
                                                    "localField"    : "_id",
                                                    "from"          : "users",
                                                    "foreignField"  : "uid",
                                                    "as"            : "profile"}},
                                bson.M{"$unwind": "$profile"},
                                bson.M{"$skip": skip},
                                bson.M{"$limit": limit},})
        err = pipe.All(&result)
        if err != nil {
            return result, err
        }

Expected format for the result is as given below:

What is the best way to do this?

Thanks in advance.

<table>
        <tr>
            <th>Name</th>
            <th>Email</th>
            <th>Phone Number</th>
            <th>Note</th>
        </tr>
        <tr>
            <td>Swati Sharma</td>
            <td>[email protected]</td>
            <td>987-999-9999</td>
            <td>I don't want my acount</td>
        </tr>
        <tr>
            <td>James Black</td>
            <td>[email protected]</td>
            <td>999-999-3120</td>
            <td>I don't want my acount</td>
        </tr>
    </table>

If your criteria for searching is properties of the "User", then surely it makes more sense to search from the "User" collection and then only return the "deactivation_requests" which are joined to those users or user as the case may be. That's a lot more efficient than joining the collections first and then brute force searching the joined results for matching users. — Neil Lunn
@NeilLunn Thanks for your answer. I have updated my question please take a look. — Swati

Swati Swati · Accepted Answer · 2017-10-04T14:54:30

Hello

I found the answer for my query. I have tried the following code and it works for me.


    result := []bson.M{}
            skip := 0
            limit := 20
            c := mongoSession.DB("database").C("deactivation_requests")
            pipe := c.Pipe([]bson.M{
                                    bson.M{"$lookup": bson.M{
                                                        "localField"    : "_id",
                                                        "from"          : "users",
                                                        "foreignField"  : "uid",
                                                        "as"            : "profile"}},
                                    bson.M{"$unwind": "$profile"},
                                    bson.M{"$match": bson.M{"$or": []bson.M{ 
                                                        bson.M{"profile.first_name":bson.RegEx{"(?i).*"+name+".*", "i"} },
                                                        bson.M{"profile.last_name": bson.RegEx{ "(?i).*"+name+".*", "i"} }, 
                                                        bson.M{"profile.email_id": bson.RegEx{"(?i).*"+name+".*", "i"} }, 
                                                        bson.M{"profile.phone_number": bson.RegEx{".*"+keyword+".*", "i"} }, 
                                                        bson.M{"profile.alt_phone_number": bson.RegEx{".*"+keyword+".*", "i"} }, 
                                    }}},
                                    bson.M{"$skip": skip},
                                    bson.M{"$limit": limit},})
            err = pipe.All(&result)
            if err != nil {
                return result, err
            }

How do I perform Joinquery with search in MongoDB and go?

1 Answers