Completely stuck with GroupBy and orderBy

@vincent15000

Sorry, this is my structure:

CREATE TABLE IF NOT EXISTS `chats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `group_id` bigint(20) unsigned NOT NULL,
  `admin_read` tinyint(1) NOT NULL DEFAULT 0,
  `chat_object` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `chat_title` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,

  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3488 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

@sr57 Strict or non strict mode by default ?

@sr57 Thanks for the reply. I was trying to build my query with DBForge query builder(for mysql), and while selecting all the columns and adding the clauses, this is the code that it gave me.

Question for you, I was always told in this forum to use strict mode with Laravel, is this correct?

I am set on strict mode right now

@vincent15000

non strict mode by default ... that's bad

@User476820 It's better to use strict mode, but it's not obligatory.

@sr57 Sure ... but I thought MySQL was in strict mode by default.

@sr57 Not sure I understood, I said I was set on strict mode, this is correct no?

@vincent15000

thought MySQL was in strict mode by default.

It can depend of your provider, but if you install it (on debian) it should be on "non strict mode"

@User476820

I was always told in this forum to use strict mode with Laravel, is this correct?

With or without Laravel, don't use "non strict mode" except if you know what you do ...

@sr57 Let me try with the second select, not an easy one I have to admit as I tried earlier on but the results was a double SUM of admin_read

@User476820

Take your time, I'm still here for about an hour.

I tried this:

DB::select("SELECT
  C1.chat_title,
  C1.chat_object,
  C1.created_at,
  C1.id,
  MAX(C1.message) AS messages,
  C2.unread,
  C1.group_id
FROM chats C1
  INNER JOIN (SELECT
      C2.created_at,
      C2.chat_object,
      SUM(IF(C2.admin_read = 0, 1, 0)) AS unread
    FROM chats C2
    GROUP BY C2.chat_object) C2
    ON C1.chat_object = C2.chat_object
WHERE C1.group_id = 82
GROUP BY C1.chat_object,
         C1.group_id
ORDER BY C1.id DESC")->paginate(50);

But I am getting this: Call to a member function paginate() on array

@sr57 Thanks, I will open another thread for the pagination.

@sr57 Thank you so much let me check

@sr57 Do you mean:

  C2.chat_object,
  SUM(IF(C2.admin_read = 0, 1, 0)) AS unread
FROM chats C2
GROUP BY C2.chat_object, C2.created_at

I am not sure I understood as I try to add MAX to C2.created_at but I am still have the same issue.

@sr57 Been all day on it, I don't get it. Let's make it super short as I am not sure I explain the issue properly.

Let's say you have this setup:

TABLE 'CHATS'

ID | CHAT_ID | MESSAGES  |  UNREAD_MESSAGES | CREATED_AT
1   | POIPOIY-099-07 | hEY |  0  |  2022-06-02 13:16:42
2  | POIPOIY-099-07 | HI    |  0  |  2022-06-02 13:16:42
3  | POIPOIY-099-08 | HI    |  1   | 2022-06-02 13:16:42

And what I would like to achieve is:

-GROUP all the CHAT_UUID by their respective unique UUIDs but also get the latest messages "MESSAGES" per group. -SUM all the UNREAD_MESSAGES as 'IF 0 ? 1 : 0 (SUM( IF(C2.admin_read = 0, 1, 0))

I am not understanding if self referencing on the same table should be done via a join or sub query, I am honestly totally confused on this.

Took like 8 coffees, now starting wine as I am close to having a break-down ............:-( :-( :-( :-( :-(

@sr57 Thank you so much(You are my master lol), let me try now :-)

@sr57 LOL about chocolate :-)

I tried your query and I am having a different problem, the output is:

ID | CHAT_ID | MESSAGES  |  UNREAD_MESSAGES | CREATED_AT
1   | POIPOIY-099-07 | hEY |  0  |  2022-06-02 13:16:42
2  | POIPOIY-099-07 | HI    |  0  |  2022-06-02 13:16:42
3  | POIPOIY-099-08 | HI    |  1   | 2022-06-02 13:16:42
3  | POIPOIY-099-08 | HI    |  1   | 2022-06-02 13:16:42
3  | POIPOIY-099-09 | HI    |  1   | 2022-06-02 13:16:42

The "MESSAGES" ARE GOOD, all showing the latest one but the CHAT_ID is not grouped. I really was expecting it to be grouped with "FROM chats GROUP BY chat_id"

@sr57 What a day! lol

Something is not right, And I think that's why I spent so much time on this, I have never struggled so much on a query before. Here is my new query based on yours:

SELECT DISTINCT
  C1.chat_id,
  C2.latest,
  C1.messages
FROM chats C1
  INNER JOIN (SELECT
      chats.chat_id,
      MAX(chats.created_at) AS latest
    FROM chats
    GROUP BY chats.chat_id) C2
    ON C1.chat_id = C2.chat_id
    AND C1.created_at = C2.latest

But the result is the same, the GROUP BY chats.chat_id of the INNER JOIN is not grouping them. I cannot work out why. I tried like 5 query builders today to get some help, they all give me the same result.

@sr57 Leave it for today ahahaha :-) I have already took so much energy out of you, it will take me some time to get a proper example running here: https://www.db-fiddle.com/

Thank you so much again for your time.

@sr57 Thank you so much! I really appreciate your help.

@sr57 I will check this out tomorrow(it is 1 am), thank you so much! I will post back.

@sr57 I was thinking, why the join is on the created_at and not on the id? As the Ids are incremental and can be reorganized by the latest it should be better no?

@sr57 Yesterday I felt like dead meat and when it is like that...my brain does not think anymore as I worked 7 days and 12 hours each day. Instead of Chocolate I take GABA + B6 together(when mega stressed with work), look for it online, it is magical(but I ran out this week). And when I want to push myself hard on low moral days I take the opposite which are "Mucuna pruriens".

By the way, about C1.message, C1.updated_at,

It is a mistake, my visual query builder added them as default. Each time I do a group, it adds all the selected fields in the group, no idea why this happens all the time. Good night and thanks again for all your help! Not sure what I should do about the best answer as I would like to give you credits too, please let me know what I should add as a best answer.

@sr57 Ok, for the grouping issues, I checked on stack overflow and people are saying that Laravel should be set this way:

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'modes' => [
                //'ONLY_FULL_GROUP_BY', // Disable this to allow grouping by one column
                'STRICT_TRANS_TABLES',
                'NO_ZERO_IN_DATE',
                'NO_ZERO_DATE',
                'ERROR_FOR_DIVISION_BY_ZERO',
                'NO_AUTO_CREATE_USER',
                'NO_ENGINE_SUBSTITUTION'
            ],
            'engine' => 'InnoDB',
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
            PDO::ATTR_PERSISTENT => true,
        ],

After that, no more errors on grouping with 1 column and my script started to work a lot better. Here more information about it: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html#:~:text=If%20the%20ONLY_FULL_GROUP_BY%20SQL%20mode,are%20functionally%20dependent%20on%20them.

@sr57 Sorry I could not reply earlier....Covid got me and I ended up pretty bad....I wanted to say thank you for everything.

@User476820

@user476820 Thank for your feedback, hope you are doing better now, should be good to close this thread.

@sr57 Thanks, I was not really sure what to add as the best answer. I chose the Mysql settings in the config as this is what seems to have stopped the groupBy Issues I was having.

@sr57 Oh sorry I see, the best answer is based on appreciation and not on what solved the issue, sorry, this is opposite of Stack overflow. Ok, so in this case not sure, I will choose anything you answered because the actual original issue was the mysql settings and not the queries. When I ran my old queries, everything worked ok with these settings.

@User476820 It is usually a good practice to give the reply that helped you arrive to the final solution the best reply, and it's also a good idea to show the finished solution to the problem as well.

@Tray2 Thanks I posted the full solution above already which has:

            'modes' => [
                //'ONLY_FULL_GROUP_BY', // <----Disable this to allow grouping by one column
                'STRICT_TRANS_TABLES',
                'NO_ZERO_IN_DATE',
                'NO_ZERO_DATE',
                'ERROR_FOR_DIVISION_BY_ZERO',
                'NO_AUTO_CREATE_USER',
                'NO_ENGINE_SUBSTITUTION'
            ],

I was unable to have a single groupBy using a single column, no matter what I was trying from the above answers. What solved the issue is what they told me to do on Stackoverflow which is //'ONLY_FULL_GROUP_BY', // <----Disable this to allow grouping by one column . My entire app started to work again. Now what I am not sure about is....this solved my issue yes, but should users do this? From what people say on Stackoverflow, yes, it is fine. But is it really? This is why I am confused about what should be the correct answer.

@User476820 I would not have disabled that, but rather rewritten the query. However that is your decision.

@Tray2

I agree with you, but it's not easy for everyone to understand the good use of "group by" when mysql* allows, with the non-strict mode, to create "simpler" queries that seem/can work. And when, on top of that, you want to create a "Laravel query" it can become a nightmare.

*better to use postgresql.

@sr57 Any idea why in a database you should not use groupBy for a single column? What is the technical issue behind it? This is the part I am not understanding at all(if mysql technically allows you to do it, and it works).

@User476820 If I'm not completely wrong, the SQL standard requires all columns in the group by, changing that is more of a lazy hack,

@sr57 My current query completely changed from the original as It has been some time now.

But for example, let's say you have this:

select
id1,
id2,
id3
FROM chats
where id1 = ?
GROUP BY
id1,
id2,
id3

VS This:

select
id1,
id2,
id3
FROM chats
where id1 = ?
GROUP BY
id1

Why would the first groupBy gives no warnings and the second does(unless you uncomment this little settings).

The reason this is super annoying is....if you have select * and have a huge table, you have to add each all of the selected columns to the groupBy or get this warning. Making life so much more complicated as you have to write a lot more. I see this as strange and cannot understand the technical need behind this.

Let's say you have apples, oranges and bananas, why would you need to tell the script that you need to groupBy by "apples, oranges and bananas", you could just tell it to group all the columns by "apples" and not look at the other columns as you are only interested in grouping by Apples.

I hope I explained the question properly :-)