This is an extract of one of my php functions. I'm struggling to get this optimized as is super slow, as I need to load all interview records, and then check if the current user is either assigned as user_id on the interview record, or if the user->id is in the interviews shared_user_ids which is json

$interviews___ = interviews::whereIn('archived', [0, false, null])->sortBy('created_at')->reverse();
        $interviews__ = [];
        foreach($interviews___ as $in){
            $sameUser = $in->user_id == $user->id; // this line to...
            $ids_ = json_decode($in->shared_user_ids, true);
            $ids = [];
            foreach($ids_ as $id){
                $ids[] = (int)($id);
            }
            $containsUser = in_array($user->id, $ids);  // ...to this line
            if($sameUser || $containsUser){
                $interviews__[] = [
                    "id"=>$in->id,
                    "title"=>$in->title,
                    "created" => $in->created_at,                
                    "open_rate" => $in->getOpenRates(),
                    'total_leads' => $in->getLeads(),
                    'newOpensCounter' => $in->getNewOpens(),
                    'openDiff' => $in->getOpenDiff(),
                    'interview_count' => $in->getInterviewCount(),
                    'sent_count' => $in->getCount(),
                    'sent_count_marketing' => $in->getCountMarketing(),
                    'sent_count_vlog' => $in->sent_count_vlog,
                    'openedInterview' => $in->openedInterview ?? 0,
                    'openedBespoke' => $in->openedBespoke ?? 0,
                    'openedMarketing' => $in->openedMarketing ?? 0,
                    'openedVlog' => $in->openedVlog ?? 0,
                    'archived' => $in->archived
                ];
            }
        }

I'm also using PostgreSQL

Edit - expected result

somehow create a sql query which does the user id checks for the user_id and the shared_user_id

then return only those which match the requirements

1. archived has a value of (0, false, null) <- required
2. user_id matches user->id [optional]
3. user->id in shared_user_ids [optional]

CodePudding user response：

You should be able to query the columns in the interview query itself. You can read more about querying JSON columns here.

You should also change the sortBy to orderBy since sortBy can only be used on collections.

$interviews___ = interviews::whereIn('archived', [0, false, null])
    ->where(function($query) {
        $query->whereJsonContains('shared_user_ids', $user->id)
            ->orWhere('user_id', $user->id);
    })
    ->orderBy('created_at', 'DESC')
    ->get();