How to use postgresql "@>" in query in laravel

Question

query:

$msg = XYZ::where(function ($q) use ($chat) {
                    $q->where('msg->x', $chat['xId'])->where('msg->y', $chat['yId']);
                })->orWhere(function ($q) use ($chat) {
                    $q->where('msg->x', $chat['yId'])->where('msg->y', $chat['xId']);
                })->orderBy('msg->sTime')->first();

Index:

CREATE INDEX msg ON XYZ USING GIN ("msg" jsonb_path_ops);
CREATE INDEX msg_x ON XYZ USING gin (("msg" -> 'x'));
CREATE INDEX msg_y ON XYZ USING gin (("msg" -> 'y'));
CREATE INDEX msg_stime ON XYZ USING gin (("msg" -> 'sTime'));

i created index on jsonb field "msg" and i am trying to use @> notation in query in laravel. query in question works perfectly but i just want to reduce execution time duration by creating index.

Did your read https://stackoverflow.com/q/36075918/4848587? – Jonas Staudenmeir Sep 16 '18 at 19:19 — Jonas Staudenmeir, Sep 16 '18 at 19:19

score 0 · Answer 1 · answered Sep 15 '18 at 05:45

0

You can go for following approach for example

$users = User::whereRaw("jsonb_exists(json -> 'skills', 'drink beer')")->get();

Since @> just checks if json contains, this will have similar effect.

There is this link https://laravel.com/api/5.6/Illuminate/Database/Query/Builder.html#method_whereJsonContains and with this, this is possible

User::WhereJsonContains('json->skills', 'drink beer')->get();

But i have not tried it yet.

Hope this helps you.

answered Sep 15 '18 at 05:45

syam

892
8
19

query in question works perfectly but i just want to reduce execution time duration by creating index – Kristly D'sena Sep 15 '18 at 07:05
i suggested you how to use @> and anyways your index would work – syam Sep 15 '18 at 07:07

How to use postgresql "@>" in query in laravel

1 Answers1