Level 1
Anyone can help ?
Nov 18, 2020
11
Level 1
how I can get all tickets with count replies on it for the current user
Hi guys,
I have 3 tables
-
users
-
tickets ---user_id
-
replies ---user_id ---ticket_id
I get user tickets using (user_id) on the tickets table
and I want count replies on the ticket and show the ticket with the number of replies on it
i have user model and ticket model and replies model
but i don't know how the relationship should be
Level 63
Hello,
What you can do is a query like this one.
$tickets = DB::table('tickets')
->leftJoin('replies', 'replies.ticket_id', '=', 'tickets.id')
->select('tickets.id', 'tickets.property as ticket_property', ..., DB::raw(count(*) as replies_count))
->where('tickets.user_id', $user_id)
->groupBy('replies.ticket_id')
->get();
I noticed that this code could have a problem in another project, but I don't understand why ... I got an error because of the groupBy() parameters, so perhaps you will have to add some other parameters in it like tickets.property, ... and the other select parameters, and then it works fine.
Tell me if it helps ;).
Level 14
So each user has multiple tickets? and each ticket has multiple replies? Also its not clear if you want the replies for only that user or just all the replies by all users. I think your table for replies isn't setup properly. The setup you have describes a many to many relationship with users and tickets rather than replies. Also I don't know what other columns are on the replies table.
If that is the case on your user model you need:
public function tickets()
{
return $this->hasMany('App\Models\Ticket');
}
In your ticket model:
public function replies()
{
return $this->belongsTo('App\Models\Replies');
}
Then in your view you could call like this:
$user->tickets // all the users tickets
$user->tickets->first() // the first ticket in that collection
$user->tickets->first()->replies // the replies for that ticket
$user->tickets->first->replies->where('user_id', $user->id) // the users first ticket with only replies from that user
Then to count:
count($user->tickets->first()->replies)
count($user->tickets->first->replies->where('user_id', $user->id))
Level 1
i want get each user tickets and count the replies for each ticket
I get the tickets but i don't know how i should get replies and count them
This is the code that i use to get all tickets
$all_tickets = DB::table('tickets')
->orderBy('id', 'DESC')
->where([['user_id', '=', $userId], ])
->paginate(10, ['*'], 'all');
Level 14
Did you try what I mentioned above?
Level 1
I do not really understand the models
Can you give me an example code to get all tickets with replies count using where user_id = current user
Level 14
I did above.
do you have a file App\Models\User.php, App\Models\Ticket.php, and App\Models\Reply.php?
Can you please post those files?
Level 1
User
public function tickets() {
return $this->hasMany('App\Models\Ticket');
}
Ticket
public function replies() {
return $this->belongsTo('App\Models\Reply');
}
Reply
function user(){
return $this->belongsTo('App\Models\User','user_id','id');
}
Level 14
Okay now are you trying to render a view displaying the number of replies for each ticket?
Level 1
yes can you give me an example code
Level 14
Okay can you post your controller code and the view code you are returning from the controller method?
Please or to participate in this conversation.