MongoDB count number of key contain the word by object

Question

I have a collection that contain data like

{
    "_id": {
        "$oid": "610b5c978e82a1738333dfc1"
    },
    "title": "What is Lorem Ipsum?",
    "description": "Lorem Ipsum is simply dummy text of the printing and typesetting industry",
    "body": "Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, Unknown printer galley of type and scrambled it to make a type specimen book."
}

{
    "_id": {
        "$oid": "610b5fa2c96a6958dc654881"
    },
    "title": "Where does it come from? book",
    "description": "It was popularised in the 1960s with the release of Letraset sheets book",
    "body": "Contrary to popular belief, Lorem Ipsum random text. It has roots in a piece of classical Latin literature from 45 BC making it over 2000 years old. Book"
}

{
    "_id": {
        "$oid": "610b5fa2c96a6958dc654881"
    },
    "title": "Why do we use it? Magic",
    "description": "It is a long established fact that a reader will be distracted. Magic",
    "body": "The point of using Lorem Ipsum is that it has a more-or-less normal distribution of letters, as opposed to using, making it look like readable English. Magic"
}

{
    "_id": {
        "$oid": "610b5fa7c96a6958dc654884"
    },
    "title": "Magic Where can I get some?",
    "description": "Magic There are many variations of passages of Lorem Ipsum available, but the majority have suffered alteration",
    "body": "If you are going to use magic Lorem Ipsum, you need to be sure there isn't anything hidden in the middle of text. All the Lorem Ipsum generators on the Internet"
}

I want to search for a word and find in all table but my problem I don't know how to order them by the object that has the word used in more fields

Example if I search for book the first result should be the second object because the second object has the word book in 3 fields so he should be the first one. If I search for Magic the first result should be the 3rd and the 4th object because the last two object has Magic in title, description and body it has it on all fields

I mean the 1st result will be the object that has the word in all fields the second will be the one with the word in two fields and last one will be the object that has the word in only in one fields

I mean if keyword found in 3 fields is the 1st if found on 2 fields is 2nd if found in one field is the 3rd.

I tried my code like :

$query->where(['or', ['like', 'title', $key ], ['like', 'description', $key ], ['like', 'body', $key ]])->all();

Programmer Analyst · Answer 1 · 2021-08-06T14:39:05.863

you can do it by using aggregation, pls refre to Mongo: how to sort by external weight

Idea here is to set weight based on match found in title, description or body.

So lets say if we find keyword in title then weight is 30 and if we find in description then 20 and if we find in body then 10 and if not found then 0!

then we can sort by weight in descending order.

Please find below aggregation code.

[{$match: {$or:[
  {"body":{$regex:"book"}},
  {"title":{$regex:"book"}},
  {"description":{$regex:"book"}}
  ]
}}, {$addFields: {
  weight:{$cond: { if: { $gte: [ {$indexOfCP:['$title','book']}, 0 ] }, then: 30, else: {$cond: { if: { $gte: [ {$indexOfCP:['$description','book']}, 0 ] }, then: 20, else: {$cond: { if: { $gte: [ {$indexOfCP:['$title','book']}, 0 ] }, then: 10, else: 0 }} }} }}
} }, {$sort: {
  weight: -1
}}]

PHP code:

$options = [];

$pipeline = [
    [
        '$match' => [
            '$or' => [
                [
                    'body' => [
                        '$regex' => 'book'
                    ]
                ],
                [
                    'title' => [
                        '$regex' => 'book'
                    ]
                ],
                [
                    'description' => [
                        '$regex' => 'book'
                    ]
                ]
            ]
        ]
    ],
    [
        '$addFields' => [
            'weight' => [
                '$cond' => [
                    'if' => [
                        '$gte' => [
                            [
                                '$indexOfCP' => [
                                    '$title',
                                    'book'
                                ]
                            ],
                            0.0
                        ]
                    ],
                    'then' => 30.0,
                    'else' => [
                        '$cond' => [
                            'if' => [
                                '$gte' => [
                                    [
                                        '$indexOfCP' => [
                                            '$description',
                                            'book'
                                        ]
                                    ],
                                    0.0
                                ]
                            ],
                            'then' => 20.0,
                            'else' => [
                                '$cond' => [
                                    'if' => [
                                        '$gte' => [
                                            [
                                                '$indexOfCP' => [
                                                    '$title',
                                                    'book'
                                                ]
                                            ],
                                            0.0
                                        ]
                                    ],
                                    'then' => 10.0,
                                    'else' => 0.0
                                ]
                            ]
                        ]
                    ]
                ]
            ]
        ]
    ],
    [
        '$sort' => [
            'weight' => -1.0
        ]
    ]
];

$cursor = $collection->aggregate($pipeline, $options);

foreach ($cursor as $document) {
    echo $document['_id'] . "\n";
}

as you look to sort based on number of fields it matched then below is the PHP aggregation:

$options = [];

$pipeline = [
    [
        '$match' => [
            '$or' => [
                [
                    'body' => [
                        '$regex' => 'book'
                    ]
                ],
                [
                    'title' => [
                        '$regex' => 'book'
                    ]
                ],
                [
                    'description' => [
                        '$regex' => 'book'
                    ]
                ]
            ]
        ]
    ],
    [
        '$addFields' => [
            'weight' => [
                '$add' => [
                    [
                        '$cond' => [
                            'if' => [
                                '$gte' => [
                                    [
                                        '$indexOfCP' => [
                                            '$title',
                                            'book'
                                        ]
                                    ],
                                    0.0
                                ]
                            ],
                            'then' => 1.0,
                            'else' => 0.0
                        ]
                    ],
                    [
                        '$cond' => [
                            'if' => [
                                '$gte' => [
                                    [
                                        '$indexOfCP' => [
                                            '$description',
                                            'book'
                                        ]
                                    ],
                                    0.0
                                ]
                            ],
                            'then' => 1.0,
                            'else' => 0.0
                        ]
                    ],
                    [
                        '$cond' => [
                            'if' => [
                                '$gte' => [
                                    [
                                        '$indexOfCP' => [
                                            '$body',
                                            'book'
                                        ]
                                    ],
                                    0.0
                                ]
                            ],
                            'then' => 1.0,
                            'else' => 0.0
                        ]
                    ]
                ]
            ]
        ]
    ],
    [
        '$sort' => [
            'weight' => -1.0
        ]
    ]
];

$cursor = $collection->aggregate($pipeline, $options);

foreach ($cursor as $document) {
    echo $document['_id'] . "\n";
}

and normal aggregation would look like below:

  [{$match: {$or:[
  {"body":{$regex:"book"}},
  {"title":{$regex:"book"}},
  {"description":{$regex:"book"}}
  ]
}}, {$addFields: {
  weight: {$add: [
  {$cond: { if: { $gte: [ {$indexOfCP:['$title','book']}, 0 ] }, then: 1, else: 0}},
  {$cond: { if: { $gte: [ {$indexOfCP:['$description','book']}, 0 ] }, then: 1, else:0}},
  {$cond: { if: { $gte: [ {$indexOfCP:['$body','book']}, 0 ] }, then: 1, else: 0 }}]} 
}}, {$sort: {
  weight: -1
}}]

not that what i'm looking for not by weight; If it found in 3 fields or two fields or only one fields — im-learning, Aug 06 '21 at 14:05

MongoDB count number of key contain the word by object

1 Answers1