Как выполнить такой sql запрос в laravel 5.3?

Question

[Александр Амплеев]

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;

вот такой запрос должен посчитать сумму лайков для определенного можно сказать альбома с фотографиями где у каждой фотографии своя сумма лайков. Но как его выполнить в контексте latavel не разобрался, пробывал так:

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;


    }

Пытался сделать это связями laravel, но получилось слишком много запросов при выводе списка альбомов чтобы сортировать их по рейтингу, который складывается из суммы лайков для самого альбома и суммы всех вложенных лайков для каждой вложенной в него фотографии.

вот так изначально было:

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

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

- это естественно дает слишком много запросов.

В идеале понять как сделать нормальное количество запросов используя стандартные связи laravel. Ну или хотя бы как выполнить чистый sql запрос выше и в него воткнуть динамические данные.

Comments

[Дмитрий]

Там фтп то про raw query должно быть

Answer 1

[Виталий Юшкевич]

Для начала стоит определиться, какую задачу решить надо: Как еще можно написать запрос? Как его написать "более стандартным функционалом Laravel"? Или же как сделать его быстрее? Или же как сделать меньше запросов?

Исходя из этих ответов, нужно выстраивать по разному решение.
Если не нравится ->exec(), то можно завернуть в DB::raw.

Ваше "изначальное" решение можно переписать через 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;

Это уже сделает запросов меньше.

После посмотреть на структуру БД, посмотреть на загрузку. Может быть есть смысл либо кэшей прикрутить, если она больше Read чем write. Либо может денормализацию сделать или вообще вынести в отдельную красивую таблицу данные под этот запрос и свести ваш запрос к

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

Comments

[Александр Амплеев]

Вот про ленивую загрузку интересно, не знал, сейчас попробую. Спасибо. Денормализовать не хотелось бы и цель, конечно, ускорить. Но похоже самое быстрое все же денормализация.

[Виталий Юшкевич]

Александр Амплеев, https://laravel.com/docs/5.7/eloquent-relationship... вот тут про ленивую загрузку детальнее.

Answer 2

[Кирилл Арутюнов]

А чем raw expressions из документации не подходят?

Comments

[Александр Амплеев]

Вот переделал с его использованием:

public function getRatingAttribute()
    {
        $result = DB::table('likes')
            ->join('media_items', 'likes.likeable_id', '=', 'media_items.id')
            ->join('places', 'media_items.place_id', '=', 'places.id')
            ->select('like')
            ->where(['media_items.place_id' => $this->id, 'likes.likeable_type' => 'App\MediaItem'])
            ->get();

        return $result->sum('like') + $this->selfRating;
    }

    public function getSelfRatingAttribute()
    {

        $result = DB::table('likes')
            ->select('like')
            ->where(['likes.likeable_id' => $this->id, 'likes.likeable_type' => 'App\Place'])
            ->get();

        return $result->sum('like');

    }

Вопрос: нормально ли при выводе 10 объектов Place и сортировке их по убыванию рейтинга делать 70 запросов к бд? Текущий код делает 70 запросов.

Но надо понимать что рейтинг каждого Place высчитывается из количества лайков и дислайков непосредственно к Place и непосредственно к каждому MediaItem который относится к данному Place.

Хочется также понять сколько запросов здесь в идеале должно делаться.

[Александр Амплеев]

И также отдельный вопрос про страницу отдельного Place:
на ней выводятся все MediaItem относящиеся к месту и у каждого MediaItem выводится количество лайков и дислайков. При этом при 156 таких элементов делается 320 запросов. При 157 уже 322.

Ниже привел в комментариях всю структуру бд и код если вдруг нужно это..

[Кирилл Арутюнов]

Вообще это много запросов, можно и оптимизировать. Если БД держит нагрузку и прироста не планируется, то можно не париться.

Но лучше оптимизировать:
– Можно прикрутить кеш;
– Можно переписать запрос, чтобы он через подзапросы тянул лайки медиаобъектов, т.е. для 10 Place должен быть 1 запрос с подзапросом, который тянет лайки внутренних медиобъектов каждого Place; это будет уже чуть лучше;
– Можно подумать над структурой БД, вероятно с ней что-то можно сделать;
– Можно отойти от нормальных форм и хранить прямо в таблице Place общее количество лайков и при лайке медиа-объекта обновлять и это число; нарушается целостность, но работает быстрее.

Всё зависит от вашей задачи и условий, в которых вы решаете задачу.

Answer 3

[miki131]

Покажите структуру БД и связи в Laravel.

Comments

[Александр Амплеев]

[Александр Амплеев]

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Place extends Model
{
    protected $table = 'places';
    protected $fillable = ['type_id', 'name'];

    public function type()
    {
        return $this->belongsTo('App\TypePlace');
    }

    public function mediaItems()
    {
        return $this->hasMany('App\MediaItem');
    }

    public function likes()
    {
        return $this->morphMany(Like::class, 'likeable');
    }

    public function getCountLikesAttribute()
    {
        return Like::where([
            'likeable_id' => $this->id,
            'likeable_type' => 'App\Place',
            'like' => 1,
        ])->count();
    }

    public function getCountDislikesAttribute()
    {
        return Like::where([
            'likeable_id' => $this->id,
            'likeable_type' => 'App\Place',
            'like' => -1,
        ])->count();
    }

    public function getRatingAttribute()
    {
        $result = DB::table('likes')
            ->join('media_items', 'likes.likeable_id', '=', 'media_items.id')
            ->join('places', 'media_items.place_id', '=', 'places.id')
            ->select('like')
            ->where(['media_items.place_id' => $this->id, 'likes.likeable_type' => 'App\MediaItem'])
            ->get();

        return $result->sum('like') + $this->selfRating;
    }

    public function getSelfRatingAttribute()
    {

        $result = DB::table('likes')
            ->select('like')
            ->where(['likes.likeable_id' => $this->id, 'likes.likeable_type' => 'App\Place'])
            ->get();

        return $result->sum('like');

    }

}

class PlaceController extends Controller
{

    public function show_places()
    {
        $places = Place::with(['type', 'likes', 'mediaItems'])->get();
        $sorted = $places->sortByDesc('rating');
        $places = $sorted->values()->all();

        return view('index', compact('places'));
    }}

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use App\NewTime;

class MediaItem extends Model
{
    protected $table = 'media_items';

    public static function createMediaItem($request)
    {
        $mediaItem = new MediaItem();

        $extention_file = $request->file('path')->extension();

        if ($extention_file == 'jpeg'
            || $extention_file == 'png'
        ) {

            $mediaItem->type = 1;

        } elseif ($extention_file == 'mp4'
            || $extention_file == 'mov'
            || $extention_file == 'qt'
        ) {

            $mediaItem->type = 2;

        } else {

            $mediaItem->type = 3;

        }

        $mediaItem->place_id = $request->place_id;

        $fileName = $request->file('path')->store('images', 'public');
        $fileName = substr($fileName, 7);

        $url = '/storage/images/' . $fileName;


        $mediaItem->path = $url;
        $mediaItem->save();

        return $mediaItem->place_id;
    }

    function getTypeOfMediaAttribute()
    {
        switch ($this->type) {
            case 1:
                return 'фото';

            case 2:
                return 'видео';

            default:
                return 'не определено';

        }
    }

    function getCreatedAtNiceAttribute()
    {
        return NewTime::new_time(strtotime($this->created_at));
    }

    function getUpdatedAtNiceAttribute()
    {
        return NewTime::new_time(strtotime($this->updated_at));
    }

    public function getCountLikesAttribute()
    {
        return Like::where([
            'likeable_id' => $this->id,
            'likeable_type' => 'App\MediaItem',
            'like' => 1,
        ])->count();
    }

    public function getCountDislikesAttribute()
    {
        return Like::where([
            'likeable_id' => $this->id,
            'likeable_type' => 'App\MediaItem',
            'like' => -1,
        ])->count();
    }

    public function getRatingAttribute()
    {
        return 123;
    }

    public function place()
    {
        return $this->belongsTo('App\Place');
    }

    public function likes()
    {
        return $this->morphMany(Like::class, 'likeable');
    }
}

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Like extends Model
{
    protected $guarded = [];
    protected $table = 'likes';
}