预订 _id, user, invoice_number, payment_type, txid 用户 _id, firstName, SecondName, usrEmail 搜索查询 $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(); 应用程序\预订 public function user() { return $this->hasOne('App\Userlist', 'user', '_id'); } 两个图片集用户预订 用户集合 { "_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" } 预订集合 { "_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) }解决方案您可以使用aggregate()进行raw查询,该查询可以使用 $lookup 运算符可在此处实现加入": $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 ) )); }); $lookup 将返回数组"对于包含与提供的'localField'值无"或更多匹配项的目标字段,其中该值是单数或值数组.通常,我们在这里使用ObjectId,尤其是当以_id链接到'foreignField'时.这比客户端可以执行的任何操作要好,因为任何其他操作都需要对每个集合源对数据库进行多个查询. $lookup 在单个请求和响应中完成.唯一真正需要注意的是,因为它与ORM/ODM是分开的",所以您需要指定实际的集合名称",而不是类或模型的名称.因此,我只是在这里假设"users",但是您可能需要将其调整为实际调用的Users集合.无论如何,拥有加入"数据之后,您就可以 "usrEmail"属性上的c20> ,并包含在您的查询中.对于实际查询,由于您基本上是对两个集合中的数据执行$or条件,因此我们不能真正地 $match ,直到执行连接"之后.然后当然有 $skip 和 $limit 进行分页. /p>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, txidusers_id, firstName, SecondName, usrEmailSearch 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\Bookingpublic function user() { return $this->hasOne('App\Userlist', 'user', '_id'); }Images of two collectionUserBookingsUser 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. 这篇关于如何使用Laravel和MongoDB匹配加入的集合?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
08-28 01:01
查看更多