I'm trying to get the right data from the database, I'm retriving a model with a media relation via eloquent, but I want to return a photo that contains the 'main' tag stored in JSON, if this tag is missing, then I would like to return the first photo assigned to this model. how i assign tags to media

I had 3 ideas:

1. Use orWhere() method, but i want more likely 'xor' than 'or'

$models = Model::with(['media' => function ($query) {
    $query->whereJsonContains('custom_properties->tags', 'main')->orWhere();
}]);


return $models->paginate(self::PER_PAGE);

2. Raw SQL, but i don't really know how to do this i tried something with JSON_EXTRACT and IF/ELSE statement, but it was to hard for me and it was a disaster

3. Last idea was to make 2 queries and just add media from second query if there is no tag 'main'

$models = Model::with(['media' => function ($query) {
    $query->whereJsonContains('custom_properties->tags', 'main');
}]);

$models_all_media = Model:: with(['media']);

return $models->paginate(self::PER_PAGE);

but i tried something like

for($i=0; $i<count($models); $i  ) {
  $models->media = $models_all_media
}

but i can't do this without get() method, beacuse i don't know how to change this to LengthAwarePaginator class after using get()

CodePudding user response：

try using whereHas https://laravel.com/docs/9.x/eloquent-relationships

Model::with('media')
     ->whereHas('media',fn($media)=>$media->whereJsonContains('custom_properties->tags', 'main'))
     ->paginate(self::PER_PAGE);

CodePudding user response：

as per your comment you can use

$models = Model::with(['media' => function ($query) {
    $query->whereJsonContains('custom_properties->tags', 'main');
}])
->leftJoin('media', function ($join) {
    $join->on('models.id', '=', 'media.model_id')
         ->whereNull('media.custom_properties->tags->main');
})
->groupBy('models.id')
->paginate(self::PER_PAGE);

return $models;