Fetch data from database where logged in user id exist in an column that stores ids as an array Lara-CodePudding

I want to get the rows in a database where logged in user exists. Here is my structure

Table = Meetings
Column = Participants (stores an array of users eg.["1","2","3"]);
Auth()->id() = "1"

I want a query that will fetch rows if Auth()->id() exist in participants which stores an array of User id.

here is my code:

    $meetings = Meeting::join('venues', 'meetings.meeting_venue', '=', 'venues.id')                             
              ->join('organizers','meetings.meeting_organizer','=','organizers.id') 
              ->join('users', 'meetings.user_id','=','users.id')  
              ->where('meetings.participants', '=', auth()->id())                                                  
              ->get(['meetings.*', 'venues.venue_name', 'organizers.organizer_name','users.name', ]);

Here is my participants column from the database:

array:1 [▼
  0 => array:1 [▼
    "participants" => array:3 [▼
      0 => "52"
      1 => "56"
      2 => "57"
    ]
  ]
]

$meetings = DB::table('meetings')->whereJsonContains('meetings.participants', auth()->id())->get()->toArray();

dump returns empty array.

CodePudding user response：

You can use a trick, here it is:

$meetings = Meeting::join('venues', 'meetings.meeting_venue', '=', 'venues.id')                             
          ->join('organizers','meetings.meeting_organizer','=','organizers.id') 
          ->join('users', 'meetings.user_id','=','users.id')  
          ->where('meetings.participants', 'like', "%\"{auth()->id()}\"%")
          ->get(['meetings.*', 'venues.venue_name', 'organizers.organizer_name','users.name']);

or You can use whereJsonContains(), like so:

 $meetings = Meeting::join('venues', 'meetings.meeting_venue', '=', 'venues.id')
     ->join('organizers','meetings.meeting_organizer','=','organizers.id') 
     ->join('users', 'meetings.user_id','=','users.id')  
     -> whereJsonContains('meetings.participants', auth()->id())
     ->get(['meetings.*', 'venues.venue_name', 'organizers.organizer_name','users.name']);

Hope this helps you. Greetings

CodePudding user response：

found a way around it I assigned auth()->id() to a variable then added double quote in the query. Below is how I did it

$uid = auth()->id();
-> whereJsonContains('meetings.participants', "$uid");

this works fine