Question 17168 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel JOIN with JSON string

I have two tables in Laravel, one is the comment table, and the second is the users table. In the comment table, I have this type of data.

enter image description here

For this comment table, I want to match the tags column’s userid in JSON, so how can we join that userid with the user’s table? here is what I tried, but that is not working as expected.

$messages = TopicComment::where('user_id', $currentUserId)
    ->join("users", "users.id", "=", "users.id")
    ->(function ($query) {
        $query->whereJsonContains('tags.userid', users.id);
    })
    ->
    ->get()->toArray();
Tags:

4

Answers


  1. 0

    with this package you can create a relation via a json field
    https://github.com/staudenmeir/eloquent-json-relations

    Login or Signup to reply.
  2. 0

    First, there seem to be a number of errors in your code.

    1. Judging from the DB schema, there is no user_id column in your comments table and so, ::where('user_id', $currentUserId) will not work.
    2. A similar issue occurs in your join statement. You’re joining on "users.id","=","users.id" which is the same column in the same table.
    3. There’s no method called in the line with function($query). Ideally, should be a where clause.
    4. Correct usage of whereJsonContains would be:
    $query->whereJsonContains('tags', ['userid' => $currentUserId]);
    1. Rouge arrow after that line.

    And so, your final result after correcting the changes should look like:

    use IlluminateSupportFacadesDB;
...

$messages = TopicComment::join('users', 'users.id', DB::Raw("CAST(comments.tags->'$.userid' AS UNSIGNED)"))
            ->where(function ($query) use ($currentUserId) {
                $query->whereJsonContains('tags', ['userid' => $currentUserId]);
            })
            ->get()
            ->toArray();
    Login or Signup to reply.
  3. 0

    I think the only way to extract is to use json tables.

    DB::select(DB::raw("
    SELECT document_types.*, jst.name 
    FROM document_types, 
        JSON_TABLE(settings, '$[*]' COLUMNS (
            `userid` int(11) PATH '$.userid',
            `name` varchar(255) PATH '$.name'
        )) jst 
        inner join users on jst.userid = users.id WHERE users.id = :id"   
,['id' => $currentUserId]))->get()

    credit: https://dba.stackexchange.com/questions/306938/extract-json-object-inside-of-an-json-array-then-search-base-on-id

    Unfortunately I don’t have json functions on my mysql so the code will probably fail, maybe at least it’ll help get you on the right track.

    Login or Signup to reply.
  4. 0

    You can try this way…

    eg:

    <?php
$comments = DB::table('topic_comments')
->join('users', 'users.id', '=', 'topic_comments.tags->userid')
->select('topic_comments.*', 'users.name')
->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search