sqlpostgresqlpdoredbean

How to use placeholders in JSON (RedBeanPHP)?


I have a task: to check if $username has upvoted the record with the identifier $id. To do this, I need to check that in the JSON array, which is in the "votes" cell, the "upvote" field contains the username (i.e. $username). So I wrote this code (PostgreSQL DB):

$foo = R::getRow('SELECT "votes"::jsonb @> \'{"upvote":[":username"]}\'::jsonb AS "is_upvoted" FROM "pages" WHERE "id" = :id', [":id"=>$id,":username"=>$username];

The problem is that RedBeanPHP, when substituting placeholders, wraps them in single brackets, but PostgreSQL requires me to wrap the names of fields / values ​​of a JSON array in double quotes.

So how can I make it so that RB does not wrap placeholders with single quotes? Or how to make placeholders wrap with double quotes?


Solution

  • There was no need to compose JSON manually and fill in placeholders in it via RB. It was necessary to create an array with the structure of the required JSON, and use a ready-made array / variable to fill it.

    Here's how I did it:

    $username_in_array = array('upvote' => array($username));
    $username_in_json = json_encode($username_in_array);
    $is_upvoted = R::getRow('SELECT "votes"::jsonb @> :username::jsonb AS "is_upvoted" FROM "pages" WHERE "id" = :id', [":id"=>$id,":username"=>$username_in_json];
    

    The request takes the following form:

    SELECT "votes"::jsonb @> '{"upvote":["admin"]}'::jsonb AS "is_upvoted" FROM "pages" WHERE "id" = 1
    

    I checked - it works!