How to Match on Joined Collections Using Laravel a

I have two collections "bookings" and "users". In "bookings" collection I have a field "user". It is related to "users._id".

I have a booking listing page. In this listing page data from "bookings" and "users" are showing.

In this listing page I have a text box to search invoice_number, payment_type, txid and usrEmail. I have written query to search invoice_number, payment_type, txid and it is working but I am stuck on usrEmail section. How can I combine with users and write search query to search usrEmail?

I am using laravel and mongodb. I am following "https://github.com/jenssegers/laravel-mongodb"

bookings

_id, user, invoice_number, payment_type, txid

users

_id, firstName, SecondName, usrEmail

Search Query

$bookings = Booking::select('_id', 'invoice_number', 'temp_user_id', 'user', 'checkin_from', 'reserve_to', 'beds', 'dormitory', 'sleeps', 'status', 'payment_status', 'payment_type', 'total_prepayment_amount', 'cabinname', 'reference_no', 'clubmember', 'bookingdate', 'txid')
                ->where('is_delete', 0)
                ->where(function($query) use ($search) { /* That's the closure */
                    $query->where('invoice_number', 'like', "%{$search}%")
                        ->orWhere('payment_type', 'like', "%{$search}%")
                        ->orWhere('txid', 'like', "%{$search}%");
                })
                ->skip($start)
                ->take($limit)
                ->orderBy($order, $dir)
                ->get();

App\Booking

public function user()
    {
        return $this->hasOne('App\Userlist', 'user', '_id');
    }

Images of two collection User Bookings

User collection

{ "_id" : ObjectId("57877d23049ac1b819000029"), "usrName" : "adminuser", "usrPassword" : "96a65063135247fef732b5901fe05d1f", "usrFirstname" : "Sarath", "usrLastname" : "TS", "usrTelephone" : null, "usrEmail" : "sara@gmail.com", "usrMobile" : "956209959", "usrAddress" : null, "usrFax" : "4564654", "usrZip" : null, "usrBirthday" : null, "usrDAV" : "316148-DAV-Deutscher Alpenverein", "usrActive" : "1", "usrlId" : "1", "lngId" : "1", "usrPasswordSalt" : ";:?hJM\"9=z/)ea?{%-[**:]68UOT>{gj^{P0+RCF#,Id8c:n+h", "usrRegistrationDate" : ISODate("2016-07-14T11:53:07Z"), "usrRegistrationToken" : "2c2e296bda1661c7fc0645f927d0b17f", "is_delete" : "0", "usrUpdateDate" : ISODate("2017-05-16T09:12:07Z"), "usrPasswordDate" : ISODate("2016-10-25T07:35:57Z"), "usrCity" : null, "usrNewsletter" : null, "money_balance" : "0", "usrCountry" : "Deutschland" }

Booking Collection

{ "_id" : ObjectId("58046a49f8f888a80b00002a"), "cabinname" : "Matras-Haus", "checkin_from" : ISODate("2016-10-17T00:00:00Z"), "reserve_to" : ISODate("2016-10-20T00:00:00Z"), "user" : "57877d23049ac1b819000029", "beds" : "2", "dormitory" : "1", "sleeps" : "3", "clubmember" : "1", "status" : "4", "comments" : "", "bookingdate" : ISODate("2016-10-17T06:06:01Z"), "reservation_cancel" : "2", "is_delete" : NumberLong(1), "payment_status" : "1" }
{ "_id" : ObjectId("58183678d2ae67a404431d5c"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2016-10-31T23:00:00Z"), "reserve_to" : ISODate("2016-11-23T23:00:00Z"), "user" : "57877d23049ac1b819000029", "beds" : "2", "dormitory" : "0", "sleeps" : "2", "clubmember" : "0", "status" : "1", "total_price" : "1288", "payon_cabin" : "1288", "bed_prefer" : "0", "guests" : "2", "comments" : "", "prepayment_amount" : "0", "bookingdate" : ISODate("2016-11-01T06:30:16Z"), "reservation_cancel" : "2", "is_delete" : NumberLong(1), "payment_status" : "0" }
{ "_id" : ObjectId("581b31f3d2ae674d5f431d5b"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2016-11-07T23:00:00Z"), "reserve_to" : ISODate("2016-11-17T23:00:00Z"), "user" : "57877d23049ac1b819000029", "beds" : "3", "dormitory" : "0", "sleeps" : "3", "clubmember" : "0", "status" : "1", "total_price" : "840", "payon_cabin" : "840", "bed_prefer" : "0", "guests" : "3", "comments" : "", "prepayment_amount" : "0", "bookingdate" : ISODate("2016-11-03T12:47:47Z"), "reservation_cancel" : "2", "is_delete" : NumberLong(1) }
{ "_id" : ObjectId("5821af65d2ae67c82154efc5"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-09-05T22:00:00Z"), "reserve_to" : ISODate("2018-01-24T23:00:00Z"), "user" : "57877d23049ac1b819000029", "sleeps" : "2", "clubmember" : "0", "status" : "1", "total_price" : "5640", "payon_cabin" : "5630", "bed_prefer" : "0", "guests" : "2", "comments" : "", "prepayment_amount" : "21.25", "bookingdate" : ISODate("2016-11-08T10:56:37Z"), "reservation_cancel" : "2", "is_delete" : NumberLong(1) }
{ "_id" : ObjectId("582558d4d2ae679c4d8b4567"), "cabinname" : "2", "checkin_from" : ISODate("2017-07-31T22:00:00Z"), "reserve_to" : ISODate("2017-08-02T22:00:00Z"), "user" : "57877d23049ac1b819000029", "beds" : "", "dormitory" : "", "sleeps" : "2", "clubmember" : "0", "status" : "", "total_price" : "80", "payon_cabin" : "60", "halfboard" : "", "bed_prefer" : "0", "guests" : "2", "prepayment_amount" : "20", "bookingdate" : ISODate("2016-11-11T05:36:20Z"), "is_delete" : NumberLong(1) }
{ "_id" : ObjectId("58352c3cd2ae672341ec89e1"), "cabinname" : "Kemptner Hütte", "checkin_from" : ISODate("2017-05-31T22:00:00Z"), "reserve_to" : ISODate("2017-06-02T22:00:00Z"), "user" : "57877d23049ac1b819000029", "beds" : "", "dormitory" : "", "sleeps" : "2", "clubmember" : "0", "status" : "", "total_price" : "80", "payon_cabin" : "60", "halfboard" : "", "bed_prefer" : "0", "guests" : "2", "prepayment_amount" : "20", "bookingdate" : ISODate("2016-11-23T05:42:20Z"), "reservation_cancel" : "2", "is_delete" : NumberLong(1) }

You can do a raw query with aggregate() which can use the $lookup operator to effect the "join" here:

  $result = Booking::raw(function($collection) use($search, $start, $limit) {
     return $collection->aggregate(array(
       array( '$lookup' => array(
         'from' => 'users',
         'localField' => 'user',
         'foreignField' => '_id',
         'as' => 'user'
       )),
       array( '$unwind' => array( 
         'path' => '$user', 'preserveNullAndEmptyArrays' => True
       )),
       array( '$match' => array(
         '$or' => array(
           array( 'invoice_number' => array( '$regex' => $search ) ),
           array( 'payment_type' => array( '$regex' => $search ) ),
           array( 'txid' => array( '$regex' => $search ) ),
           array( 'user.usrEmail' => array( '$regex' => $search ) )
         )
       )),
       array( '$skip' => $start ),
       array( '$limit' => $limit )
     ));
  });

The $lookup will return an "array" for the target field containing "none" or more matching entries to the supplied 'localField' value(s), where that is either singular or an array of values. Typically we use ObjectId here, especially when linking to the 'foreignField' as _id.

This is better than anything that can be done client side, as any other operation would require making multiple queries to the database for each collection source. $lookup does this in a single request and response.

The only real note is that because this is "separate" from the ORM/ODM, you need to specify the actual "collection name" and not that of the class or model. So I am just presuming "users" here, but you will maybe need to adjust that to what your collection for Users is actually called.

Anyhow, after you have the "joined" data then you can $match on the "usrEmail" property from the joined data, and include in your query.

As for the actual query, since you are basically doing an $or condition across data from both collections we cannot really $match until "after" the join is performed.

Then of course there are the aggregation stages for $skip and $limit for your pagination as well.