John Davidson

Message:

The query I need to do is something like this

select `id` ,

json_extract(meta,"$.A") as a,

json_extract(meta,"$.B") as b

 from `C` 

where json_unquote(json_extract(meta, '$."A"'))>

json_unquote(json_extract(meta,'$.B'))

running it on workbrench gives me 2 results

Results

ID   a

414 2000    1500

426 2000    1500

But when I pass it to the orm of eloquent, the result is empty

DB::table('C')->select('meta->A')->

        where('meta->A','>',"json_unquote(json_extract(meta,'$.B'))")->get();

Debugging the library, I see that within the PDO functions, somehow it is not properly binding the parameters

array:9 [

  "select" => []

  "from" => []

  "join" => []

  "where" => array:1 [

    0 => "json_unquote(json_extract(meta,'$.B'))"

  ]

  "groupBy" => []

  "having" => []

  "order" => []

  "union" => []

  "unionOrder" => []

]





array:1 [

  0 => "json_unquote(json_extract(meta,'$.B'))"

]

"statement bind value"

PDOStatement {#3745

  +queryString: "select json_unquote(json_extract(`meta`, '$."A"')) from `C` where json_unquote(json_extract(`meta`, '$."A"')) > ?"

}

How could I modify it to do exactly the query I need? , I tried using whereRaw, but I have the same problem with binding

---

My Answer:
You can use the `whereRaw` method in Eloquent to directly write the raw SQL query without parameter binding issues. Here's how you can modify your query:

php
$results = DB::table('C')
->select('id', DB::raw("json_extract(meta, '$.A') as a"), DB::raw("json_extract(meta, '$.B') as b"))
->whereRaw("json_unquote(json_extract(meta, '$.\"A\"')) > json_unquote(json_extract(meta, '$.B'))")
->get();


This way, you can write the raw SQL query without worrying about parameter binding issues.