CODEWITHSUNDEEP
sqllaravellaravel-5.1
Epsilon47
Epsilon47

Reputation: 838

Format decimal value in database in where clause

I want to get data from database but the format of values that are compared do not tally.

The numbers in database are saved in weird format - sometimes are rounded and some of those are decimals (10, 14, 15, 12.44, 16.10, etc.)

In array I have values rounded on 2 decimals though.

I want to get data from database like so:

$foundPayments = $this>paymentsRepo>newQuery()>whereRaw("CONVERT(DECIMAL(10,2),payments.amount) = :payments", ['payments' => $payments]);

Althought, I end up with an error like this: Syntax error: 7 ERROR: syntax error at or near ","

I am not sure if the code works in laravel 5.1 with sending params like I do but thats not really what I do care for in that - the same error apperars even when I do not use variable.

EDIT:

I am using Postgres.

Upvotes: 0

Views: 727

Answers (1)

nakov
nakov

Reputation: 14288

I think that the syntax that you are using for the convert function is wrong. You should first pass the value then the type to which you are converting to. But please try the cast function, I think that it should give you the expected result.

$foundPayments = $this->paymentsRepo->newQuery()
    ->whereRaw("CAST(payments.amount as DECIMAL(10,2)) = :payments", 
              ['payments' => $payments]);

Upvotes: 1

Related Questions