$ expr arrayElementAt不适用于嵌入式文档的聚合 [英] $expr arrayElementAt not working in aggregation for embedded document
问题描述
我正在像
$cursor = $this->collection->aggregate(
array(
array(
'$project' => array(
'FullName' => array('$concat' => array('$first_name', ' ', '$middle_name', ' ', '$last_name')),
'FirstMiddle' => array('$concat' => array('$first_name', ' ', '$middle_name')),
'FirstLast' => array('$concat' => array('$first_name', ' ', '$last_name')),
'FirstName' => array('$concat' => array('$first_name')),
'MiddleName' => array('$concat' => array('$middle_name')),
'LastName' => array('$concat' => array('$last_name')),
'Student' => '$$ROOT'
)
),
array(
'$match' =>
array(
'$or' => array(
array("FullName" => new MongoDB\BSON\Regex($arg, 'i')),
array("FirstLast" => new MongoDB\BSON\Regex($arg, 'i')),
array("FirstMiddle" => new MongoDB\BSON\Regex($arg, 'i')),
array("FirstName" => new MongoDB\BSON\Regex($arg, 'i')),
array("MiddleName" => new MongoDB\BSON\Regex($arg, 'i')),
array("LastName" => new MongoDB\BSON\Regex($arg, 'i')),
array("Student.registration_temp_perm_no" => $arg),
'$expr' => array(
'$eq'=> array(
array('$arrayElemAt' => array('$allotment_details.room_id', -1)), $this->RoomId)),
),
// "Student.assigned_keys" => ['$exists' => false],
"Student.schoolId" => new MongoDB\BSON\ObjectID($this->SchoolId)
)
)
)
);
我有一个集合，其中包含
I have collection which contains data like
"first_name": "John",
"middle_name": "",
"last_name": "Mayor",
"allotment_details": [
{
"allotment_id": "ff666d55-2fcc-79b2-e4da-e165939555bb",
"room_id": "5be2d9aad2ccda0fdc006a65",
"bay_id": ObjectId("5be2d9aad2ccda0fdc006a61"),
...
}
以上代码可用于三种名称类型的精细级联，并搜索在$arg中传递的所有数据.请注意，我添加了array('$arrayElemAt' => array('$allotment_details.room_id', -1)), $this->RoomId))以便根据名称的串联来获取学生，并且应该基于$this->RoomId来获取那些学生.
The above code is working for fine concatenation of three names types and searching all the data passed in $arg. Note that I have added array('$arrayElemAt' => array('$allotment_details.room_id', -1)), $this->RoomId)) in order to fetch students based on concatenation of names and those students should be fetched on the basis of $this->RoomId.
上面的代码不是获取分配到房间的学生，而是根据名称的串联获取所有学生. 请帮忙!!!
The above code is not fetching students assigned to a room but is fetching all the students based on concatenation of names. Please help !!!
推荐答案
快速修复
您的管道"在这里主要不起作用，因为您的初始基本上是将该字段包括在计划"文档中，因为这是聚合管道阶段的工作方式:
Quick fix
Your "pipeline" does not work here primarily because your initial $project lacks the field you want to use an a later stage. The "quick fix" is therefore basically to include that field in the "projected" document, since that's how aggregation pipeline stages work:
array(
array(
'$project' => array(
'FullName' => array('$concat' => array('$first_name', ' ', '$middle_name', ' ', '$last_name')),
'FirstMiddle' => array('$concat' => array('$first_name', ' ', '$middle_name')),
'FirstLast' => array('$concat' => array('$first_name', ' ', '$last_name')),
'FirstName' => array('$concat' => array('$first_name')),
'MiddleName' => array('$concat' => array('$middle_name')),
'LastName' => array('$concat' => array('$last_name')),
'Student' => '$$ROOT',
'allotment_details' => 1 # that's the change
)
),
或者即使您仍然将$$ROOT用作Student，也只需在该路径下限定字段:
Or even since you used $$ROOT for Student anyway, simply qualify the field under that path:
'$expr' => array(
'$eq'=> array(
array('$arrayElemAt' => array('$Student.allotment_details.room_id', -1)),
$this->RoomId
)
),
但是 我恳求您不要这样做.
连接字符串"的整个概念，以便稍后进行在内容上使用$match 是一个非常糟糕的主意，因为这意味着整个集合在实际完成任何过滤"之前就已在管道中被重写.
The whole concept of "concatenating strings" in order to do a later $match on the content is a really bad idea since it means the whole collection gets rewritten in the pipeline before any "filtering" actually gets done.
同样，寻找匹配最后一个"数组元素也是一个问题.更好的方法是实际上将新项目"添加到数组的开始"，而不是结束".这实际上是 $position 甚至是$sort对 $push 的修饰符分别在何处添加项目或项目的排序顺序.
Likewise looking to match on the "last" array element is also an issue. A far better approach is to instead actually add "new items" to the "beginning" of the array, instead of the "end". This is actually what the $position or possibly even the $sort modifiers to $push do for you, by changing where items get added or the sorted order of items respectively.
通过更改存储方式的方式，这需要花费一些时间，但是这样做的好处是，无需进行评估的$expr参数即可极大地提高所需查询的速度.
This takes a little work by changing the way you store things, but the benefits are greatly improved speed of such queries like you want without needing an evaluated $expr argument.
核心概念是使用如下语法预挂"新数组项:
The core concepts are to "pre-pend" new array items with syntax like:
$this->collection->updateOne(
$query,
[ '$push' => [ 'allotment_details' => [ '$each' => $allotments, '$position' => 0 ] ] ]
)
$alloments 必须为 $position 用于0，以便添加新的数组项"first".
Where $alloments must be an array as required by $each and $position is used to 0 in order to add the new array item "first".
或者，如果实际上在数组中的每个对象中都有类似created_date的属性，那么您可以使用"类似
Alternately if you actually have something like created_date as a property within each of the objects in the array, then you "could" use something like $sort as a modifier instead.
$this->collection->updateOne(
$query,
[ '$push' => [
'allotment_details' => [ '$each' => $allotments, '$sort' => [ 'created_date' => -1 ] ]
]]
)
这实际上取决于您的查询"和其他访问要求是依靠上次添加"还是最新日期"，然后通常还取决于是否打算在其中更改这样的created_date或其他"sort"属性.一种在排序"时会影响数组元素顺序的方法.
It really depends on whether your "query" and other access requirements rely on "last added" or "latest date", and then also typically if you intend to possibly alter such a created_date or other "sort" property in a way which would effect the order of the array elements when "sorted".
执行此操作的原因是匹配数组中的最新"(现在是第一")项，变成:
The reason you do this is then matching the "latest" ( which is now the "first" ) item in the array simply becomes:
$this->collection->find([
'allotment_details.0.room_id': $this->RoomId
])
MongoDB允许使用注释" ，使用0索引.您不能要做的是指定一个负"索引，即:
MongoDB allows the "first" array index to be specified with "Dot Notation", using the 0 index. What you cannot do is specify a "negative" index i.e:
$this->collection->find([
'allotment_details.-1.room_id': $this->RoomId # not allowed :(
])
这就是为什么要在更新"上执行上述操作，以便将数组重新排序"为可行形式的原因.
That's the reason why you do the things shown above on "update" in order to "re-order" your array to the workable form.
另一个主要问题是字符串的串联.如前所述，这只是为了进行所需的匹配而造成不必要的开销.这也是不必要的"，因为您可以使用$or来避免出现这种情况，因为每个字段的条件都已存在，因为它们已经存在于实际文档中:
The other main issue is the concatenation of strings. As already mentioned this creates unnecessary overhead just in order to do the matching you want. It's also "unnecessary" since you can complete avoid this using $or with the conditions on each of the fields as they exist already within the actual document:
$this->collection->find([
'$or' => [
[ 'first_name' => new MongoDB\BSON\Regex($arg, 'i') ],
[ 'last_name' => new MongoDB\BSON\Regex($arg, 'i') ],
[ 'middle_name' => new MongoDB\BSON\Regex($arg, 'i') ],
[ 'registration_temp_perm_no' => $arg ]
],
'schoolId' => new MongoDB\BSON\ObjectID($this->SchoolId),
'allotment_details.0.room_id': $this->RoomId
])
当然，无论实际需要什么完全"查询条件，但您都应该了解基本概念.
And of course whatever the "full" query conditions actually need to be, but you should be getting the basic idea.
如果您实际上并没有在寻找偏词"，那么可以使用文本搜索"在带有名称"的字段中定义.创建索引后，将是:
Also if you are not actually looking for "partial words", then a "text search" defined over the fields with the "names". After creating the index that would be:
$this->collection->find([
'$text' => [ '$search' => $arg ],
'schoolId' => new MongoDB\BSON\ObjectID($this->SchoolId),
'allotment_details.0.room_id': $this->RoomId
])
总体而言，我真的建议您仔细查看所有其他选项，而不是对您现有的代码进行一些小的更改.通过对存储方式以及索引"事物的存储方式进行一些仔细的重组，您将获得巨大的性能优势，而广泛的$concat蛮力"方法根本无法提供这种优势.
Overall I would really recommend looking closely at all the other options rather than making one small change to your existing code. With a little careful re-structuring of how you store things and indeed "index" things, you get huge performance benefits that your extensive $concat "brute force" approach simply cannot deliver.
NB 现代PHP发行版通常支持
[]作为array()的更简短表示.它更干净而且更容易阅读.所以请使用它.
N.B Modern PHP Releases generally support
[]as a much more brief representation ofarray(). It's a lot cleaner and far easier to read. So please use it.
这篇关于$ expr arrayElementAt不适用于嵌入式文档的聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
