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 stackoverflow.com/q/36075918/4848587?

Jonas Staudenmeir
– Jonas Staudenmeir

2018-09-16 19:19:52 +00:00
Commented Sep 16, 2018 at 19:19 — Jonas Staudenmeir
– Jonas Staudenmeir, Commented Sep 16, 2018 at 19:19

syam · Accepted Answer · 2018-09-15 05:45:27Z

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, 2018 at 5:45

syam

8929 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kristly D'sena Over a year ago

query in question works perfectly but i just want to reduce execution time duration by creating index

syam Over a year ago

i suggested you how to use @> and anyways your index would work

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related