0

I'm trying to implement filter for logs in the Laravel5 model. Logs stored in mongodb collection.

The record has this structure. But this structure is not fixed! It can be just an object with not all of these properties. Moreover it can be very different

So here is one possible:

{
   "_id": ObjectId("5950cacd64b6bd1324005095"),
   "request": "44b2fd871f989bb5a1416cbee2dbae5f",
   "type": "calculate",
   "data": {
     "request": {
       "request_uri": "http://api.server/method/packet/260c013a7b791b93997dd7b9abf15f68/go",
       "region_id": "249",
       "country_id": "",
       "count_insureds": "1",
       "citizenship_id": "198",
       "period": "18",
       "date_from": "2017-06-30",
       "date_till": "2017-07-17",
       "last_name": "Asdasd",
       "first_name": "Sasd",
       "birthday": "1978-06-08",
       "passport": "WE211",
       "email": "",
       "address": "",
       "identific": "",
       "persons": {
         "1": {
           "last_name": "",
           "first_name": "",
           "birthday": "",
           "passport": "",
        } 
      },
       "igree_1": "1",
       "igree_2": "1",
       "gateway": "mygateway",
       "packet_id": "99",
       "_token": "5288e7875e97d165443fba0e6b5577ff" 
    },
     "response": {
       "hash": "44Qwf5T6znXx2",
       "sn": "КМ5000073",
       "total_price": "899.87000000",
       "total_price_formatted": "899.87",
       "currency": "EUR",
       "total_base_price": 14.58,
       "rate": "29.0445" 
    } 
  },
   "updated_at": ISODate("2017-06-26T08:50:21.0Z"),
   "created_at": ISODate("2017-06-26T08:50:21.0Z") 
}

And here is another one:

{
   "_id": ObjectId("5950cac964b6bd1324005093"),
   "request": "094932d4f83069b9e0ac759277a557b0",
   "type": "draft",
   "data": {
     "BILL_AMOUNT": "899.87",
     "SHOPORDERNUMBER": "357397a1fdf22a4daac6c955e1bd71-S9F8wGTjBRzGAVYw-1498466995",
     "APPROVALCODE": "TESTPM",
     "RECEIPT_URL": "https://www.website.com/services/receipts/get-receipts/shop-bill-id/35343744448e8c7f62e266e9ddb9345785357cf7b526bdca57e971720a7dfff4c5850f2e24a [...]",
     "TOKEN": "18323839313034333331160415C2A559EBAF18E61D2739BBEF7AB5681A10B15A7CB29E6ACC12EACAC46C0C0F297EEC04918B4811E223656B776214E5B2EC5818E7394698D5BF07FB17699E [...]",
     "CARD_PAYMENT_SYSTEM": "VISA",
     "CARD_LAST_DIGITS": "1111",
     "RESULT": "0" 
  },
   "updated_at": ISODate("2017-06-26T08:50:17.947Z"),
   "created_at": ISODate("2017-06-26T08:50:17.947Z") 
}

I've wrote scope-method for filtering data. It looks like this:

public function scopeFilter($query, $filters)
{
    if ($requestType = array_get($filters, 'request_type')) {
        $query->where('type', '=', $requestType);
    }

    if ($data = array_get($filters, 'data')) {
        //$query->where('data', 'like', '%' . trim($data) . '%');
        $query->raw(function ($collection) use ($data) {
            return $collection->find([
                'data' => [
                    '$in' => [$data]
                ]
            ]);
        });
    }
    // ...
}

The problem is: I can't filter data because it has different structure. As you can see I've tried to search with LIKE statement but it is useless. And filtering with $in return me all records.

Any suggestion will be appreciated.

Thanks in advance.

Alex Slipknot
  • 2,439
  • 1
  • 18
  • 26
  • The problem is therefore the structure. Named keys are bad news for databases. Instead you should put the items in an array where the key name is a value of a property as is the data you need to search. i.e `"data": [ { "key": "BILL_AMOUNT". "value": 899.87 }. { "key": "APPROVALCODE", "value": "TESTPM" }]` Then you can search on a consistent path: `find([ "data.value": "TESTPM" ]). Easy. – Neil Lunn Jun 29 '17 at 12:16
  • @NeilLunn unfortunately I can't modify structure due to requirements. It should be in this ugly structure – Alex Slipknot Jun 29 '17 at 12:20
  • 1
    Unfortunately there really is no other way. Unless you resort to evaluation of JavaScript to iterate and search the keys. Modern MongoDB 3.4 has a `$objectToArray` aggregation operator, but even that will not help with varying nested structure if it's not all :"flat". Both methods are horrible anyway since you need to scan the whole collection to get a result. Like it or not, the structure is flawed and needs to change in order to effectively query. Bottom line. Cannot perform miracles here. – Neil Lunn Jun 29 '17 at 12:27
  • @NeilLunn okay... That's sad :( Thank you anyway. To be honest I was wonder why mongo hasn't objectToString or some kind of filter method to perform operations in it's closure... – Alex Slipknot Jun 29 '17 at 12:41
  • It doesn't have it because it's a really stupid thing to do. Honestly, you are trying to bang a square peg in a round hole here. The rest of the world is not the problem. The "problem" is that this design does not fit into the way things actually work. Rather than sitting on your hands and complaining things don't work as you think they should, try to understand how they work and work to the benefit of those constraints. That's how the rest of us do it, and you need to start thinking like that as well. I've told you how to solve the problem. Do something about it. – Neil Lunn Jun 29 '17 at 12:46
  • @NeilLunn Well I know that it is not in the mongo-style but in case like that it can be a great feature. Sometimes when you rotate a picture you can discovery new things. – Alex Slipknot Jun 29 '17 at 13:16

0 Answers0