How to join based on json column in Laravel QueryBuilder

Question

posts table

    |id|value|
    |1 |hoge |
    |2 |fuga |
    |3 |piyo |

some_posts table

    |id|post_ids|
    |1 |[1, 2]  |
    |2 |[1]     |
    |3 |[1, 2, 3]|

I want to join using QueryBuilder. What should I do?

That was not possible.

DB::table('some_posts')
  ->join('posts', 'some_posts.post_ids', 'posts.id');

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json = integer\n LINE 1: ...ts" inner join "posts" on "some_posts"."post_ids" = "posts"....\n

HINT: No operator matches the given name and argument types. You might need to add explicit type casts. (SQL: select "some_posts".* from "some_posts" inner join "posts" on "some_posts"."post_ids" = "posts"."id" where "some_posts"."some_id" = 384)

Thank you,

Why wasn't that possible? What error/unwanted result did you get? — Itamar Mushkin, Oct 29 '19 at 07:23
thanks comment! I added error description SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: json = integer\n LINE 1: ...ts" inner join "posts" on "some_posts"."post_ids" = "posts"....\n ^\n HINT: No operator matches the given name and argument types. You might need to add explicit type casts. (SQL: select "some_posts".* from "some_posts" inner join "posts" on "some_posts"."post_ids" = "posts"."id" where "some_posts"."some_id" = 384) — じゃれみー, Oct 29 '19 at 07:26

Eyad Jaabo · Answer 1 · 2019-10-29T08:07:52.177

0

You can use a collection in the Post model

Post.php

Class Post extends model

public function somePosts()
{
  return $this->belongsTo('some_posts`, 'post_ids');
}

Then, change this

DB::table('some_posts')
->join('posts', 'some_posts.post_ids', 'posts.id');

to

Post::with('somePosts')->get();

edited Oct 29 '19 at 08:07

answered Oct 29 '19 at 08:01

Eyad Jaabo

391
3
13

thank you answer. but, I want to do it with QueryBuilder. Originally I wrote in Eloquent Model, but there was a performance problem with Eloquent. – じゃれみー Oct 29 '19 at 08:07
Try to use `ANY` like this `->join('posts', 'some_posts.post_ids', ANY('posts'.id); – Eyad Jaabo Oct 29 '19 at 08:17
Is there a method definition called ANY in Laravel? I couldn't find it. please tell me the namespace... – じゃれみー Oct 29 '19 at 08:30
Please see this answer https://stackoverflow.com/questions/42043739/setting-up-foreign-key-with-an-array-of-integers – Eyad Jaabo Oct 29 '19 at 08:42

sibabrat swain · Accepted Answer · 2019-10-29T08:25:53.447

0

You can achieve this by whereJsonContains in laravel. Join with a condition which looks similar to this.

$query->whereJsonContains('options->languages', ['en', 'de'])

You should not save the ids in JSON object.

edited Oct 29 '19 at 08:25

answered Oct 29 '19 at 08:10

sibabrat swain

1,277
8
20

> should not save the ids in JSON object. yes, that's right... sry... Could you tell me how to use whereJsonContains in a join closure? – じゃれみー Oct 29 '19 at 08:32

How to join based on json column in Laravel QueryBuilder

2 Answers2