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

Laravel, eloquent, query: problem with retriving right data from DB

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);

  1. 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

  2. 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()

Tags:

2

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search