0

I'm new to cakephp and I'm trying to do a search area to list some products that have their prices greater than a value, or lower than a value, and paginate it.

But when I try to do so, the result is totally different from what I've expected. When trying to get to know why was this happening, I had found out that my query was treating the value as String type instead of a Float, and it was messing up with the results.

No matter what I try, the type of value doesn't change, how do I make to change the type of the value on the query of cakePHP?

// the price value that comes from the input is formatted like this: '1.000,00'

$productsModel = $this->loadModel('Products');

$this->paginate = [
    'contain' => [
       'Archives', 'Categories', 'Favorites'
    ],
    'conditions' => [
        //here I've tried to do some conversions manually
        'Products.price >=' => floatVal(str_replace(",", ".", str_replace(".", "", $data['lower_price']))),
        'Products.price <=' => floatVal(str_replace(",", ".", str_replace(".", "", $data['greater_price']))
    ],
    'limit' => 16
];
$products = $this->paginate($productsModel);

$this->set(compact('products'));

I was expecting that the query was generated like this:

SELECT * FROM products WHERE price >= 29.90;

But I have this instead:

SELECT * FROM products WHERE price >= '29.90';
Willian
  • 1
  • 1
  • What is the type of the database fields? Float should be a `DOUBLE` or `FLOAT`, check this link: https://book.cakephp.org/3.0/en/orm/database-basics.html#data-types – GreyRoofPigeon Jun 18 '19 at 12:44
  • Assuming your table schema correctly declares the column as a numeric type, this is a PDO issue, unfortunately it's not possible to bind values as floats, they have to be bound as strings. https://stackoverflow.com/questions/2718628/pdoparam-for-type-decimal – ndm Jun 18 '19 at 12:45
  • If your are using a float type in your shema, switch to a decimal type to avoid floating point rounding issues. https://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency – ndm Jun 18 '19 at 12:51
  • actually I'm not using float type on my database schema, I was wondering if there was a way to convert the value to float before the select, but guess the type will always be linked to what is defined on the schema, from what said on the docs that TED posted – Willian Jun 18 '19 at 12:59
  • Well, there's no way around this if you are using value binding, as mentioned, that's a PDO limitation. What type exactly are you using in your schema? And what exactly is the problem with the results, what do you receive, and what do you expect to receive instead? – ndm Jun 18 '19 at 13:27
  • I'm using VARCHAR field to test an import data scenario, the problem is that when you try to compare 'number' with number, it doesn't filter at all. So I'll be changing the field type to decimal instead, since theres no workaround this. That's weird that even changing to decimals the problem persists, although it seems to be working with the float field. – Willian Jun 18 '19 at 14:13
  • How about this: https://book.cakephp.org/3.0/en/orm/entities.html#accessors-mutators . You can manipulate how the field is read. I guess that it probably only changes how the value is displayed, and that is doesn't affect on how it is read in the query. But you could give it a shot... otherwise I recommend to change your DB scheme – GreyRoofPigeon Jun 18 '19 at 15:04

0 Answers0