Can somebody help me to convert postgresql query to laravel query

Are you after an eloquent query or using relations.

If query see examples for query Builder.

If relations see examples under eloquent relations.

I not found examples in eloquent relations. But Thank you for your replied.

https://laravel.com/docs/6.x/eloquent#retrieving-models

I believe this is what you need here:

https://laravel.com/docs/master/queries#json-where-clauses

So if you have an Order model then

Order::whereJsonContains('people->position', '1')->get();

Or using the Query Builder:

DB::table('orders')->whereJsonContains('people->position', '1')->get();

Don't forget the imports of the class or a facade whichever you end up using.

I don't know how I can select from 2 tables ('order' table and 'people' column)

My database is look like this: http://sqlfiddle.com/#!17/6e17ee/

schema

CREATE TABLE orders (
   ID serial NOT NULL PRIMARY KEY,
   people json NOT NULL
);
INSERT INTO orders (people)
VALUES
   (
      '[
             {
               "name":"Alex",
               "total":5000,
               "position":1
             },
             {
               "name":"Amandra",
               "total":3000,
               "position":1
             },
             {
               "name":"Bob",
               "total":200,
               "position":2
             }
         ]'
   );

sql

SELECT
   elem
FROM
   orders, json_array_elements(people) elem
WHERE elem->>'position' = '1';

@chalahed your fiddle is empty. And you said select from 2 tables, but order is a table and people is a column so I don't understand?

I asked were you using relations, meaning is that what you were after relations.

Which means you have to use a combination of some parts of the documentation and then other parts of the documentation and put it all together.

Do you have a model setup yet.

Just take setting all this up step by step.

@chalahed I've seen your edit now, so that's one table, not two. Have you tried the code that I've shared in my first reply?

I edited reply to add build schema and sql run. use PostgreSQL 9.6

I don't know how to call this

FROM orders, json_array_elements(people)

@chalahed and does that work in PostgreSQL ? Because people is a column of the orders table, not a separate table as far as I can see from your CREATE TABLE script. So I don't get it?

It's work in Postgresql.

Can you go to sqlfidle again

http://sqlfiddle.com/#!17/6e17ee/1/0

@chalahed okay, I see it now, I haven't seen raw usage in the from method of the query builder, so does a select works in this case for you?

Order::select('people')->whereJsonContains('people->position', '1')->get();

// or 

Order::selectRaw('json_array_elements(people) as elem')->whereJsonContains('people->position', '1')->get();

Can't you use the query as is using the PDO instance. Just curious.

I like JSON but ffs, using JSON in a database table is like shooting a sprinter in both knees and expecting him to run 100m under ten seconds. In some edge cases there might be a good usecase for JSON but most likely not.

A good database model/design is very important as from what I can see from the code you have shared it's not.

In you case I would have these tables

• users (people)
• order_statistics
• orders

And build from there. It will save you a lot of headache.

It doesn't work.

I had solved with this.

Order::whereJsonContains('people', [['position' => 1]])->get();