I need to optimize these queries

@Nakov

$baseInfo = Websites::query()
            ->addSelect(['reviews_count' => Reviews::query()
                                                ->whereColumn('website_id', 'websites.id')
                                                ->where('report_status', '!=', 3)
                                                ->count()])
            ->addSelect(['oneStar_count' => Reviews::query()
                                                ->whereColumn('website_id', 'websites.id')
                                                ->where('report_status', '!=', 3)
                                                ->where('review_stars', '1')
                                                ->count()])
            ->addSelect(['twoStar_count' => Reviews::query()
                                                ->whereColumn('website_id', 'websites.id')
                                                ->where('report_status', '!=', 3)
                                                ->where('review_stars', '2')
                                                ->count()])                              
            ->addSelect(['threeStar_count' => Reviews::query()
                                                ->whereColumn('website_id', 'websites.id')
                                                ->where('report_status', '!=', 3)
                                                ->where('review_stars', '3')
                                                ->count()])
            ->addSelect(['fourStar_count' => Reviews::query()
                                                ->whereColumn('website_id', 'websites.id')
                                                ->where('report_status', '!=', 3)
                                                ->where('review_stars', '4')
                                                ->count()])
            ->addSelect(['fiveStar_count' => Reviews::query()
                                                ->whereColumn('website_id', 'websites.id')
                                                ->where('report_status', '!=', 3)
                                                ->where('review_stars', '5')
                                                ->count()]) 
            ->get();

is returning me an error:

Undefined table: 7 ERROR: missing FROM-clause entry for table "websites"

@snack! Try this instead:

$baseInfo = Websites::query()
    ->select('id')
    ->addSelect(['reviews_count' => Reviews::query()
	    ->selectRaw('count(id)')
        ->whereColumn('website_id', 'websites.id')
        ->where('report_status', '!=', 3)
    ])
    ->addSelect(['oneStar_count' => Reviews::query()
	    ->selectRaw('count(id)')
        ->whereColumn('website_id', 'websites.id')
        ->where('report_status', '!=', 3)
        ->where('review_stars', '1')
    ])
          ....
    ->get();

@Nakov Well, I think this time worked, but how do I access this information when $baseInfo['reviews_count'] and either $baseInfo->reviews_count is returning me an error:

Undefined array key "reviews_count" 

or

Property [reviews_count] does not exist on this collection instance. 

?

@snack! Change ->get() to ->first() :)

@Sinnbeck Yep! @nakov dd()-ing the $baseInfo actually returns:

    "reviews_count" => 0
    "oneStar_count" => 0
    "twoStar_count" => 0
    "threeStar_count" => 0
    "fourStar_count" => 0
    "fiveStar_count" => 0

and that's not true. I currently have 1 review for the website I'm looking for.

@snack! are the columns and statuses you are checking against correct?

        ->whereColumn('reviews.website_id', 'websites.id')

table names as well?

@Nakov Yes, absolutely correct. The reviews table is named reviews and the websites table is named websites.

@snack! Unfortunately I don't have your data to check all that is going on. Now just play around with the query. I hope I helped with optimizing it.

@Sinnbeck App\Models\Websites.php

   public function reviews() {
       return $this->hasMany(Reviews::class, 'website_id');
   }

@snack! Great. Then either of my solutions should work. The second one should make the fewest queries I believe :)

@Sinnbeck Yeah, worked. But I've just noticed that on each review the database queries are incrementing with 4 which I guess is too much. Currently, with 2 reviews on the page I have 18 database queries according to Clockwork. More optimization on all queries is necessary I guess.

@snack! Check the database tab to see exactly what queries are run and where. It should give you the file + line number

@Sinnbeck I'm recieving the website information with route binded model. The clockwork says that this line:

public function index(Websites $website) {
	...............

    $reviews = $website->reviews()->where('report_status', '!=', 3)->orderBy('created_at', 'DESC')->with('reply', 'reports')->paginate(20);

    ...............
}

is executing 4 queries. I guess in this case there is no way to optimize it, right?

@snack! I only count 3. reviews, replyand reports. What is the 4th? Is it the $website? If so, then probably not. Depends on if you need reply, and reports.

@Sinnbeck

App\Models\Reviews 
SELECT count(*) as aggregate FROM "reviews" WHERE "reviews"."website_id" = 3 and "reviews"."website_id" IS not NULL and "report_status" != 3

App\Models\Reviews 
SELECT * FROM "reviews" WHERE "reviews"."website_id" = 3 and "reviews"."website_id" IS not NULL and "report_status" != 3 ORDER BY "created_at" DESC LIMIT 20 offset 0

App\Models\ReviewReplies	
SELECT * FROM "review_replies" WHERE "review_replies"."review_id" in (3, 4)

App\Models\ReviewReports 	
SELECT * FROM "review_reports" WHERE "review_reports"."review_id" in (3, 4)

@snack! This is run twice. Check where it is run

App\Models\ReviewReplies	
SELECT * FROM "review_replies" WHERE "review_replies"."review_id" in (3, 4)

App\Models\ReviewReports 	
SELECT * FROM "review_reports" WHERE "review_reports"."review_id" in (3, 4)

@Sinnbeck probably here:

ReviewReports::where('user_id', auth()->user()->id)->orderBy('id', 'DESC')->limit(1)->value('created_at');

But when I try to convert it to:

$website->reviews()->reports()->where('user_id', auth()->user()->id)->orderBy('id', 'DESC')->limit(1)->value('created_at');

I get an error:

Call to undefined method Illuminate\Database\Eloquent\Relations\HasMany::reports() 

In fact that I have this in my reviews model:

    public function reports() {
        return $this->hasMany(ReviewReports::class, 'review_id');
    }

@snack! Can you show the complete method? It is hard to know exactly what you have to work with.

@Sinnbeck

 public function index(Websites $website) {

        $reviews = $website->reviews()->where('report_status', '!=', 3)->orderBy('created_at', 'DESC')->with('reply', 'reports')->paginate(20);

        if(auth()->check()) {
            $date = ReviewReports::where('user_id', auth()->user()->id)->orderBy('id', 'DESC')->limit(1)->value('created_at');
            $date = new DateTime($date);
            $date5minRep = $date->modify('+5 minutes')->format('Y-m-d H:i:s');
        }

        $baseInfo = $website->loadCount([
            'reviews' => function ($query) {
                $query->where('report_status', '!=', 3);
            },
            'reviews as oneStar_count' => function ($query) {
                $query->where('report_status', '!=', 3)
                    ->where('review_stars', '1');
            },
            'reviews as twoStar_count' => function ($query) {
                $query->where('report_status', '!=', 3)
                    ->where('review_stars', '2');
            },
            'reviews as threeStar_count' => function ($query) {
                $query->where('report_status', '!=', 3)
                    ->where('review_stars', '3');
            },
            'reviews as fourStar_count' => function ($query) {
                $query->where('report_status', '!=', 3)
                    ->where('review_stars', '4');
            },
            'reviews as fiveStar_count' => function ($query) {
                $query->where('report_status', '!=', 3)
                    ->where('review_stars', '5');
            },
        ]);

        if(auth()->check()) {
            return view('reviews.index', compact('website', 'reviews', 'baseInfo', 'date5minRep'));
        } else {
            return view('reviews.index', compact('website', 'reviews', 'baseInfo'));
        }
    }

There are some calculations that has nothing to do with database but with the results from baseInfo. I've hidden them because it is way too long array.

@snack! Can you see in clockwork exactly what two lines are causing the queries? There should be a stack trace I think

@Sinnbeck The

$reviews = $website->reviews()->where('report_status', '!=', 3)->orderBy('created_at', 'DESC')->with('reply', 'reports')->paginate(20);

is the line causing the 4 queries I show above. Then there is

App\Models\ReviewReports
SELECT "created_at" FROM "review_reports" WHERE "user_id" = 36 ORDER BY "id" DESC LIMIT 1

which is actually the

$date = ReviewReports::where('user_id', auth()->user()->id)->orderBy('id', 'DESC')->limit(1)->value('created_at');

@snack! I honestly dont see why that first query would run SELECT * FROM "review_replies" WHERE "review_replies"."review_id" in (3, 4) twice.. There is no reason for it that I can see.

@Sinnbeck For sure there is a mistake I've made. But why is Clockwork indicating that it runs twice in the controller when it obviously doesn't run twice there?

@snack! If I were you I would test it bit by bit. Return right after the first query and see what is run. That way you can isolate the queries

@Sinnbeck Thanks!