Less than in query

Need Help
1

Using cake 3 (no choice), I have:

        $c = [
            'Items.is_active' => true,
            'Items.age' => $this->request->getData('max_age')
        ];

    $this->set('conditions', $conditions);

    $items = $this->Items->find()
        ->where($c)

This returns active items where age is equal to the input.

I would like to select where age is less than or equal to. I try to change the conditions to:

        $c = [
            'Item.is_active' => true,
            'Item.age <=' => $this->request->getData('max_age')
        ];

but that does not work (appears to returns everything).

I am aware of lte() and gte() but am not sure how to use them in this case.

Goal is to return items with a max age less than or equal to the input rather than strictly equal to.

Thanks for any help!

2

If your Items.age field is actually a integer field inside your DB then the ORM should know that the values being passed to the query should be parsed as integer as well :thinking:

At least I wasn’t able to reproduce that in Cake 4.

But anyways let me try to help you with the following info…

Request data is mostly being parsed as string, even if your inital form input field is of type="number"

Therefore your value could be also interpreted as a string, not an integer. Have you look at the generated SQL via debug_kit or enabling the query log? Maybe you see your WHERE value being wrapped in two '

Try to do a intval($this->request->getData('max_age')) so its actually a number in PHP, not a string.

Otherwise if you want to use the lte() method it could look like this.

$query = $this->Items->find();
$queryValue = intval($this->request->getData('max_age'));
$items = $query->where(function (\Cake\Database\Expression\QueryExpression $exp, \Cake\ORM\Query $innerQuery) use ($queryValue) {
    return $exp->and([
        'Item.is_active' => true,
        $innerQuery->newExpr()->lte('Item.age', $queryValue)
    ]);
});

See Query Builder - 4.x for more.

1 Like
3

Thanks for your help. As a note to any future reader, it turns out that indeed my initial query did work. The issue was code afterward causing problems. The lesson? When in doubt check the raw query results before any additional processing. In my case a count of the results revealed that the query was returning what I needed even if later code was hiding that fact.

Thanks again.