Level 23
orderBy('ticket_status', 'ASC') should not give you all new tickets.. did you even try this? it will give you tickets ordered by status -
Nov 14, 2018
6
Level 2
Retrieve model with where conditions
Hi everyone,
I'm writing what is essentially a support ticket system. At the moment I retrieve tickets similar to this:
$tickets = Ticket::where('user_id', \Auth::user()->id)->orderBy('created_at', 'DESC')->get();
So this gets me all tickets that were created by the currently logged in user. Now each ticket has a ticket_status_id column which is an integer, similar to the following:
10 - new tickets 11 - re-opened tickets 20 - awaiting customer response 99 - closed
What I would like to do, rather than just sorting by the date the ticket was created is to group them by ticket status first, so anything that isn't ticket_status_id 99 appears first thus moving all closed tickets to the end of the list.
I don't think I simply want to orderBy('ticket_status', 'ASC') as this will give me all the new tickets, then the others, what i want is to essentially treat anything that isn't 99 as open (thus the same and order by created_at) and anything that is 99 as closed.
I think this is the query I want to run in terms of MySQL:
SELECT if (ticket_status_id = 99, 1, 0) as is_closed, tickets.* FROMticketsORDER BY is_closed ASC, created_at DESC
However, I'm not sure how I can manipulate a model to run this query for me and return a collection as objects as expected?
I hope that makes sense, any help is greatly appreciated.
Level 51
If you want your tickets grouped into sub-collections, and ordered by the creation date, you could do:
$tickets = Ticket::where('user_id', \Auth::user()->id)->get()->groupBy('status_id')->each(function ($group) {
$group->orderByDesc('created_at');
});
Level 2
@SHEZ1983 - Hi,
Sorry, i don't think i've explained it properly or maybe you've mis-read but I don't simply want them in ticket_status order, if I do this then all the 10s will be together, then the 11's then the 12's etc, I want them ordered by created_at DESC order but with the ticket_status 99's last.
In my original statement when I said "I don't think I simply want to orderBy('ticket_status', 'ASC') as this will give me all the new tickets" i was refering to ordering by ticket status will give me all the tickets with status 10 first (this is the status code for a "New Ticket" as mentioned in my original question).
Not really sure if the "did you even try this" was warranted and maybe I'm reading it in a tone it wasn't intended, but thank you for taking the time to reply.
Level 2
I decided to approach this a different way, unfortunately running two queries but it seems to work.
$other_tickets_open = Ticket::open()
->where('user_id', '!=', \Auth::user()->id)
->whereIn('client_id', $linked_clients)
->orderBy('created_at', 'DESC')
->get();
$other_tickets_closed = Ticket::closed()
->where('user_id', '!=', \Auth::user()->id)
->whereIn('client_id', $linked_clients)
->orderBy('created_at', 'DESC')
->get();
$other_tickets = $other_tickets_open->merge($other_tickets_closed);
the open and closed local scopes are as:
public function scopeOpen($query)
{
return $query->where('ticket_status_id', '!=', 99);
}
public function scopeClosed($query)
{
return $query->where('ticket_status_id', '=', 99);
}
so basically get all open tickets (everything != 99) ordered by date descending, then get all closed tickets (= 99) ordered by date descending then merge the two collections together.
Level 23
I am not sure how 'fast/efficient' this will be but you could do:
// get all tickets
// then:
$tickets->groupBy(function ($item, $key) {
return $item->status !== 99;
});
then you will get two groups..
Level 2
@SHEZ1983 - Thanks, that looks like a nicer way to do it in one query, I'll definitely give that a try when I can and see how it works out.
Please or to participate in this conversation.