How to optimize this database query in Laravel?

Question

please tell me how can I improve the database query in Laravel?

there are two methods in the repository

    public function getCountAdverts(): array
    {
        return [
            Advert::STATUS_PUBLISHED => $this->getCount(Advert::STATUS_PUBLISHED),
            Advert::STATUS_CLOSED => $this->getCount(Advert::STATUS_CLOSED),
            Advert::STATUS_NOT_PUBLISHED => $this->getCount(Advert::STATUS_NOT_PUBLISHED),
            Advert::STATUS_MODERATION => $this->getCount(Advert::STATUS_MODERATION),
        ];
    }

    private function getCount(string $status): int
    {
        return Advert::where('status', $status)->count();
    }

How can I get the same array without just 4 queries?

Do everything in one quesy since there are no other changes

Akshay Khale
– Akshay Khale

2020-05-07 19:29:28 +00:00
Commented May 7, 2020 at 19:29 — Akshay Khale
– Akshay Khale, Commented May 7, 2020 at 19:29

Autista_z · Accepted Answer · 2020-05-07 19:41:32Z

3

You can use that Eloquent returns Collection. So work with collection.

public function getCountAdverts(): array
{
    $adverts = Advert::get(); //this will be collection of all Adverts(Illuminate\Support\Collection)

    return [
        Advert::STATUS_PUBLISHED => $this->getCount($adverts, Advert::STATUS_PUBLISHED),
        Advert::STATUS_CLOSED => $this->getCount($adverts, Advert::STATUS_CLOSED),
        Advert::STATUS_NOT_PUBLISHED => $this->getCount($adverts, Advert::STATUS_NOT_PUBLISHED),
        Advert::STATUS_MODERATION => $this->getCount($adverts, Advert::STATUS_MODERATION),
    ];
}

private function getCount($adverts, string $status): int
{
    return $adverts->where('status', $status)->count();
}

answered May 7, 2020 at 19:41

Autista_z

2,53118 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Akshay Khale Over a year ago

How is this different than what he already has, still 4 queries will be executed???

Autista_z Over a year ago

There wil be only 1 query - $adverts = Advert::get(); And then you will work with Collection (saved in memory). Try It, you will see it. Collections are great in Laravel.

Akshay Khale Over a year ago

This is an interesting approach, I did not know. I learned something new today. Kudos to you!!!

Vlad Over a year ago

@Autista_z tell me if there will be a lot of ads in the database, then how Advert::get() will work? Will there be a lot of time spent on the request?

Autista_z Over a year ago

@Vlad it can be problem if there will be a lot of items in database (10k+), because all will save in the ram. With a lot of records its not the best solution.

Akshay Khale · Accepted Answer · 2020-05-07 19:40:50Z

1

Instead of executing multiple queries, you can do everything in one Query

Advert::whereIn('status'[
    Advert::STATUS_PUBLISHED,
    Advert::STATUS_CLOSED,
    Advert::STATUS_NOT_PUBLISHED,
    Advert::STATUS_MODERATION
])
->groupBy('status')
->select(DB::raw('count(*)'), 'status')
->get()
->toArray();

You can remove whereIn condition and you will get the count for all the type of statuses.

Some Suggestions:

You can index status column
If statuses are prefixed then you can use Enum in the database only if they are not changng in the future.

Hope this helps!!!

answered May 7, 2020 at 19:40

Akshay Khale

8,3918 gold badges52 silver badges60 bronze badges

Collectives™ on Stack Overflow

How to optimize this database query in Laravel?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related