0

I am having a problem trying to execute a simple find method using $lt. i.e. Fetch rows where Id is less than 5. Simple

Below is my query:

$result = $collection->find($data);

Where $data is:

[id] => Array
        (
            [$lt] => 5
        )

But as a result, I am getting the Id, But it compares only the first digit. Returning Ids are:

[{"_id":{"$oid":"615839a18ffb730a922af032"},"id":"1"},
{"_id":{"$oid":"615839a18ffb730a922af033"},"id":"2"},
{"_id":{"$oid":"615839a18ffb730a922af039"},"id":"15"}
{"_id":{"$oid":"615839a18ffb730a922af039"},"id":"45"}

Why it is comparing only First Digit of Id. Why $lt not working on the whole Id?

I am getting Ids 1,2,14,45,466 etc. All the first digit starts less than 5. But want the result less than 5 instead of the First digit.

turivishal
  • 34,368
  • 7
  • 36
  • 59
Shail
  • 1,565
  • 11
  • 24
  • Why do you store numeric values as strings? Store numbers (integer or double or long) then the query will work. – Wernfried Domscheit Oct 02 '21 at 12:50
  • I suspect the `5` in your query is actually a string, and MongoDB is using [lexicographical ordering](https://stackoverflow.com/questions/45950646/what-is-lexicographical-order) to compare the values. Try using `var_dump` instead of `print_r` to examine the value of `$data` – Joe Oct 03 '21 at 05:08
  • @WernfriedDomscheit, its a client database. Not under my control. I have use it like the way it is. – Shail Oct 04 '21 at 05:18

1 Answers1

0

Why it is comparing only First Digit of Id. Why $lt not working on the whole Id?

Because id has string type numbers, and you are searching for integer type numbers.

You can use $expr operator and aggregation expression syntax of $lt operator, and $toInt to convert id field's value to integer,

$data = [
  '$expr' => [
    '$lt' => [
      ['$toInt' => '$id'],
      5
    ]
  ]
]

Playground

turivishal
  • 34,368
  • 7
  • 36
  • 59