2

I'm trying to run a query with jsonb ? operator https://www.postgresql.org/docs/9.6/static/functions-json.html. In database console its ok, but i can't prepare it in laravel. Query like this:

SELECT * FROM game_user WHERE roles ? 'TRAINER'

I try DB::raw(), DB::statement() etc, but every time i have error:

SQLSTATE[42601]: Syntax error: 7 ERROR: Syntax error (near: "$1") LINE 1: SELECT id FROM game_user WHERE roles $1 'TRAINER' ^ (SQL: SELECT id FROM game_user WHERE roles ? 'TRAINER')

How i must do query without prepearing for arguments - ? to $1

aynber
  • 22,380
  • 8
  • 50
  • 63
eupathy
  • 21
  • 2
  • 1
    Show us **how** you used `DB::raw`. – ceejayoz Oct 06 '17 at 18:46
  • I'm trying this: $trainers = DB::table('game_user') ->select('*') ->whereRaw("roles ? '$trainerRole' AND game_id = $gameId") ->get()->all(); and this: $trainers = DB::table(DB::raw("SELECT id FROM game_user WHERE roles ? '$trainerRoleName' AND game_id = $gameId")); and this – eupathy Oct 06 '17 at 19:09
  • and: $trainers = DB::table('game_user') ->select('*') ->whereRaw(DB::raw(" roles ? '$trainerRole' AND game_id = $gameId"))->get()->all(); – eupathy Oct 06 '17 at 19:10
  • This link may help: https://stackoverflow.com/questions/27573778/postgresql-jsonb-and-jdbc/27580137#27580137 – aynber Oct 06 '17 at 19:22
  • As well as this one: https://stackoverflow.com/questions/16311939/how-to-prevent-pdo-from-interpreting-a-question-mark-as-a-placeholder – aynber Oct 06 '17 at 19:23
  • Thank you! Last link is perfect solution. – eupathy Oct 07 '17 at 09:38

0 Answers0