Level 75
->selectRaw('*, SUM(IF(read = 0, 1, 0)) as unread')
Start reading a documetation.
1 like
Apr 13, 2022
40
Level 4
Problem with DB SUM
Hi all,
I am having a strange problem with this query:
DB::table('chats')
->select(DB::raw('SUM(read = 0) as unread'),
'created_at')
->where('group_id', '=', $request->user_id)
->orderBy('created_at', 'ASC')
->paginate(50);
I cannot work out why but this query only returns the records which have 'read = 0' rather than returning all records and SUM the read = 0 AS unread as they are returned.
I would like to return them unread SUM no matter what(even if the read = 0 or read = 1).
Any idea why please?
Thanks
Level 4
@MichalOravec I am not actually sure where you found this in the doc, I could only find this:
->select('department', DB::raw('SUM(price) as total_sales'))
Could you pinpoint me to it please?
Thanks
Level 75
@User1980 I meant selectRaw.
1 like
Level 4
@MichalOravec oh ok, to be honest, I would not have been good enough to work out on my own that I would have had to put "*" before SUM(IF(read = 0, 1, 0)) as unread' . Unless I see an example matching perfectly this requirement :-), so on this particular matter, it is a genuine pass for me.
Thanks again
Level 4
What I find really strange is that now the orderBy has no effect:
DB::table('chats')
->selectRaw('*, SUM(IF(read = 0, 1, 0)) as unread')
->where('group_id', '=', $request->user_id)
->orderBy('created_at', 'ASC') <-----ASC OR DESC, NO DIFFERENCE
->paginate(50);
Level 50
@User1980 Your query is weird: you're selecting all columns (*) and using SUM, which is an aggregate function, but you're not grouping the results by anything.
What information are you trying to get?
2 likes
Level 39
@JussiMannisto agree with you.
@user1980 mysql/mariadb does not a good job to allow this, better to use postgresql
Level 50
@sr57 I don't think that's great advice. The correct solution is to fix the query, not to switch to a different DBMS.
1 like
Level 39
You are right, my note is just to point the difference between the 2 db on this functionality.
1 like
Level 4
@JussiMannisto Thanks to you too!
Level 4
@jussimannisto @sr57 You are right, my query seems incorrect.
Imagine a chat, the table "chats" has:
-id
-user_id (the user who store a message)
-group_id (the group id where all the users joined)
-user_read (boolean, 0 for chat has not been seen, 1, has been seen).
-admin_read (boolean, 0 for chat has not been seen, 1, has been seen).
-chat_title (as it is a support chat, each start of a chat has a title)
-chat_message (the actual chat message)
-created_at
-updated_at
What I am trying to achieve is this:
-When the admin clicks on a single user, he has access to all the user chats grouped by "chat_title".
-While the groups of chats are showing in a list, I would also like to show the user latest single message so the admin can see the latest message without having to click on the group of chats.
-When the admin lands on the above group of chats, I am trying to also show up a red tags that counts all the messages from the user that the admin has not yet read.
My initial query was like this:
$history = DB::table('chats')
->selectRaw('*, SUM(IF(admin_read = 0, 1, 0)) as unread')
->where('group_id', '=', $request->user_id)
->orderBy('created_at', 'ASC')
->groupBy(['chat_title'])
->paginate(50);
The above is close to work, as I am getting the right number of SUM for unread messages but the "chat_message" is always showing the first one rather than the latest one. I tried to change "->orderBy('created_at', 'ASC')" to ->orderBy('created_at', 'DESC'), but it makes no difference.
I cannot work out why.
Level 39
I cannot work out why.
@jussimannisto gave you the answer in his first post. Replace * by chat_title that is the only field in the group by.
Then to get the other fields, you have to join this fist query with the data you need (from users, ....)
Level 4
Thanks for the reply, the below query nearly works. I have replaced the * by the fields I needed. My problem is that I need the orderBy just after the select and not on the "GroupBy" data.
I need the: ->select( 'id', 'chat_title', 'message', 'created_at')
To be organised DESC just before the GroupBy. I hope I explained myself better.
$history = DB::table('chats')
->select(
'id',
'chat_title',
'message',
'created_at')
/////////////ORDERBY DESC HERE///////////////////////
->selectRaw('SUM(IF(admin_read = 0, 1, 0)) as unread')
->groupBy('chat_title')
->orderBy('id', 'ASC') <--This order the group and not the chats before the group.(as I need the latest chat to show in each group).
->paginate(50);
Level 102
Can I suggest reading my post about grouping. Maybe what you want is just all messages and group in laravel afterwards? https://sinnbeck.dev/posts/laravel-groupby-error
Any chance each title refers to another table? If so this would be easier as it's just a relationship
Level 39
Level 102
@sr57 if you need to get every single column, why would you group by in the database?
Okay so you want to get all columns. Then the trick is to simply not use groupBy() on a database level
But I agree that loading all records and groupBy in a collection is also bad :)
Level 4
@Sinnbeck Thanks Sinnbeck, I think I did not explain my original query well enough. I have just posted a new code above. All I need is the orderBy to be done just before the GroupBy so in each group of chats, the admins can always sneak on the latest chat(before clicking on the group of chats).
Level 102
@User1980 does each group have a foreign key to another table? Like a chat_topic or similar? This sounds like a parent sorted by a join
Level 39
Level 4
@Sinnbeck No, this is my table:
-id
-user_id (the user who stores a message)
-group_id (the group id where all the users joined)
-user_read (boolean, 0 for chat has not been seen, 1, has been seen).
-admin_read (boolean, 0 for chat has not been seen, 1, has been seen).
-chat_title (as it is a support chat, each start of a chat has a title)
-chat_message (the actual chat message)
-created_at
-updated_at
So by grouping the "group_id" and "chat_title", I will be able to get a group of similar unique subjects per group.
Level 102
@sr57 yeah. This sounds like a relationship to me, where the parent is sorted by a join to the chat messages
1 like
Level 4
The output would be something like this:
------------group 1--------------
Title 1
latest message showing here
--------------------------------
------------group 2--------------
Title 2
latest message showing here
--------------------------------
------------group 3--------------
Title 3
latest message showing here
--------------------------------
------------group 4--------------
Title 4
latest message showing here
--------------------------------
Level 4
It is working but instead of getting the above right now I am getting this:
------------group 1--------------
Title 1
first message showing here
--------------------------------
------------group 2--------------
Title 2
first message showing here
--------------------------------
------------group 3--------------
Title 3
first message showing here
--------------------------------
------------group 4--------------
Title 4
first message showing here
--------------------------------
Level 102
@User1980 yeah but each column you need, must either be part of the groupBy, or an aggregate function like max, min etc. So the message will be a problem. Maybe you can use a sub select
1 like
Level 102
@User1980 did you turn off strict mode in mysql config?
1 like
Level 4
@Sinnbeck It is set to 'strict' => false in mysql config.
About the sub query, I was hoping for this to work:
DB::table('chats', function ($query) {
$query->selectRaw('SUM(IF(admin_read = 0, 1, 0)) as unread')
->select(
'id',
'chat_title',
'message',
'created_at')
->orderBy('id', 'DESC');
})
->groupBy('chat_title')
->paginate(50);
But I am getting:
Object of class Closure could not be converted to string
Any idea why would "$query" cause this please?
Thanks
Level 102
@User1980 I would suggest setting it to true, to force yourself to write better mysql. :)
Your syntax for subquery is wrong. Here are the docs https://laravel.com/docs/9.x/eloquent#subquery-selects
1 like
Level 102
Just to understand what it is you need to end up with. These 4 columns right?
title | message | unread_count | created_at (you need to show when the last message was added?)
1 like
Level 4
@Sinnbeck Yes
Imagine when a user clicks on a listing, it creates automatically a support chat where a title is assigned to each outgoing message (the name of the listing). So by grouping all the messages with a matching title, I will be able to pull a group of chats related to the listing. The group_id is the id of the user who enquires about the listing.
So based on your example, I am looking at grouping all the chats with the same title but also having a count of all the messages which have not been read by the admin. To make life easy for the admin, he should also see the latest message in that group(prior to opening the group of chat on click).
Yes this is correct
title | message | unread_count | created_at (last message added to the chat room in that group)
Level 102
@User1980 Ok I gave it a quick shot
$sub = \DB::table('chats as c')
->select('message')
->whereColumn('c.title', 'chats.title')
->limit(1)
->latest();
$data = \DB::table('chats')->selectRaw(
'SUM(IF(read = 1, 1, 0)) as unread, title, MAX(created_at) as latest'
)
->selectSub($sub, 'message')
->groupBy('title')
->get();
Level 4
@Sinnbeck Looking at it now, thanks, I will update you soon
Level 4
@Sinnbeck Thanks, It seems to be returning correctly the last message of a group, the only issue is that now I only see one group from $data. If someone clicks on 10 listings today, the admin should see 10 groups of chats.
Still working on it.......
Level 102
@User1980 not quite sure what you mean. I just used some test data I had lying around. It had 4 "titles" and showed 4 as expected
Is your query exactly as mine or did you add stuff? And did you turn strict mode back on? Haven't tested it with it off
1 like
Level 4
@Sinnbeck Wait wait wait!!! I think you are right, I had a mix up in my code, the old code was still being output in the page as well as yours, checking now again, let me run some test :-)
Level 102
@User1980 ok sounds good :)
1 like
Level 4
Master @Sinnbeck hat off! :-) Very well done! Thank you so much!
Level 102
@User1980 happy to help. Hope it was a learning experience :) it should show you how to work around a problem that might be possible to "hack" with strict mode off
Level 39
Level 4
@sr57 Big thank you to you too!
Level 39
Don't forget @jussimannisto
1 like
Please or to participate in this conversation.