How to execute such sql query in laravel 5.3?

Question

Laravel

SELECT sum(likes.like)
  FROM places left OUTER JOIN media_items ON places.id = media_items.place_id
    left OUTER JOIN likes ON media_items.id = likes.likeable_id
  WHERE likes.likeable_type = 'App\\MediaItem'
  AND places.id = 1;

such a request should calculate the amount of likes for a certain album with photos, where each photo has its own amount of likes. But I didn’t figure out how to execute it in the context of latavel, I tried it like this:

public function getRatingAttribute()
    {


        $sql = "SELECT sum(likes.like)
  FROM places left OUTER JOIN media_items ON places.id = media_items.place_id
    left OUTER JOIN likes ON media_items.id = likes.likeable_id
  WHERE likes.likeable_type = 'App\\MediaItem'
  AND places.id = 1;";

        $result = DB::connection()->getPdo()->exec($sql);
        
        return $result;


    }

I tried to do this with laravel links, but it turned out to be too many requests when listing albums to sort them by rating, which is the sum of likes for the album itself and the sum of all nested likes for each photo nested in it.
this is how it was originally:

public function getRatingAttribute()
    {
        $mediaItems = $this->mediaItems;
        $result = 0;
        $result += $this->likes->sum('like');

        foreach ($mediaItems as $mediaItem) {
            $result += $mediaItem->likes->sum('like');
        }
        
        return $result;
    }

- it naturally gives too many requests.
Ideally, understand how to make a normal number of requests using standard laravel connections. Well, or at least how to execute a pure sql query above and insert dynamic data into it.

Answer 1

To begin with, it is worth deciding what task you need to solve: How else can you write a query? How to write it with "more standard Laravel functionality"? Or how to make it faster? Or how to make fewer requests?
Based on these answers, you need to build a different solution.
If you don't like ->exec(), then you can wrap it in DB::raw.
Your "original" solution can be rewritten via lazy load

$mediaItems = $this->mediaItems;
$mediaItems->load('likes')

        $result = 0;
        $result += $this->likes->sum('like');

        foreach ($mediaItems as $mediaItem) {
            $result += $mediaItem->likes->sum('like');
        }
        
        return $result;

This will already make requests less.
After look at the structure of the database, look at the load. Maybe it makes sense or to fasten caches if it is more Read than write. Or it can do denormalization or even put the data for this query into a separate beautiful table and reduce your query to

SELECT like
FROM reports_likes
WHERE type = 'App\\MediaItem'
AND place_id = 1;

Answer 2

Why are raw expressions from the documentation not suitable?

Answer 3

Show the database structure and relationships in Laravel.