CODEWITHSUNDEEP
phplaraveleloquent
Terumi
Terumi

Reputation: 347

Getting the latest of relations in Laravel

I have this schema:

Ticket
----------
id, description

TicketStatus
-----------
id, name

Ticket_TicketStatus
-----------
id, ticket_id, status_id, latest (bool)

on Ticket model

public function statuses()
{
    return $this->belongsToMany(TicketStatus::class, 'ticket_ticket_status', 'ticket_id', 'status_id');
 }

public function getCurrentStatusAttribute()
{
    return $this->statuses()->wherePivot('latest', 1)->first();
}

public function getStatusAttribute()
    {
        return $this->current_status->name;
    }

    public function scopeOpen($query)
    {
        return $query->whereHas('statuses', function (Builder $sub_query) {
            $sub_query->where('latest', true)->where('is_open', true);
        });
    }

    public function scopeClosed($query)
    {
        return $query->whereHas('statuses', function (Builder $sub_query) {
            $sub_query->where('latest', true)->where('is_open', true);
        });
    }

What I'm trying to accomplish is to get all the tickets of a certain status that are the latest ones.

So I do something like this:

 $not_mine_open_tickets = Ticket::open()
            ->orderBy('updated_at', 'desc')
            ->get();

But this is taking a lot of time to be executed on my database. Anyone knows what's wrong?

Upvotes: 0

Views: 35

Answers (1)

James
James

Reputation: 16339

But this is taking a lot of time to be executed on my database. Anyone knows what's wrong?

It could be many things, and it depends on a lot of factors.

  • What DB engine are you using?
  • How many rows are there?
  • What indexes are set up?
  • What is the current load of the database?

I'd start by looking at what the execution plan of your queries are and see what the estimated time to query is and what (if any) indexes are being used.

To get the query that would be executed, you can dump this out in your code:

dump(Ticket::open()
    ->orderBy('updated_at', 'desc')
    ->toSql());

dump(Ticket::open()
    ->orderBy('updated_at', 'desc')
    ->getBindings());

I'd then look to run this through some database software and look at the execution plan.

If you do some more research for your specific database engine you can find specific advice for next steps.

Upvotes: 1

Related Questions