Level 13
consider using sub queries
Mar 9, 2020
6
Level 1
Question mark in raw sql query cause problem
Hi. I have a problem in my raw query select. Example query:
DB::select( DB::raw(
"SELECT * FROM event e WHERE e.repeat_every->'daysInWeekRepeat' ? :dayNow"
),
['dayNow' => 'mon' ]
);
(Original query is much more complicate this is only example.)
Column "repeat_every" is a jsonb column, and I have to use question mark to search param value in array. This question mark cause a problem "invalid paremeter number". In output insted of question mark I get first param passed to query.
SO how to write this query to ignore question mark as a placeholder?
Thank you.
Level 1
What do you mean?
Level 13
DB::select('*')->from('event')->where('event.repeat_every->daysInWeekRepeat','mon')
Level 56
Is it postgres?
According to this link:
https://stackoverflow.com/a/36177073
You can use jsonb_exists to avoid the question mark:
DB::select("SELECT * FROM event e WHERE jsonb_exists(e.repeat_every->'daysInWeekRepeat', :dayNow)", ['dayNow' => 'mon']);
Also you don't need to wrap the query into DB::raw when running directly in the DB façade select method.
Level 1
Works fine. Thank you.
1 like
Level 56
You're welcome =)
Please or to participate in this conversation.