Mysql - How to sort ID by results of another table? (time)

[QQ_cazo]

Tried queries:

//v1
         SELECT (
            SELECT chat_id FROM Chats_members WHERE `group_user_id` = **INT**
            ) AS chat_id,(
            SELECT time FROM messages WHERE chat_id = chat_id ORDER BY time DESC LIMIT 500
            ) as time;
            /** only allows 1 result otherwise returns error*/
 //v2
SELECT
    Chats_members.chat_id,
    Chats_members.group_user_id AS member,
    messages.time AS TIME
FROM
    Chats_members,
    messages
WHERE
    Chats_members.group_user_id = **INT**
GROUP BY
    Chats_members.chat_id;
 /** there should be 3 total. the time is in ms

the only issue with v2 is, the time is the same for all of them when each one should have a different time

should be: (for each) random time, not all the same

 

im using Mysql

 

testing data:

CREATE TABLE `Chats_members` (
  `member_id` bigint NOT NULL,
  `chat_id` bigint NOT NULL,
  `group_user_id` bigint NOT NULL,
  `added_by_user` bigint NOT NULL,
  `time` bigint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `messages` (
  `message_id` bigint NOT NULL,
  `chat_id` bigint NOT NULL,
  `self_id` bigint NOT NULL,
  `message` text NOT NULL,
  `self_deleted` enum('true','false') NOT NULL,
  `time` bigint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `Chats_members` (`member_id`, `chat_id`, `group_user_id`, `added_by_user`, `time`) VALUES
(1, 1, 459937721, 459937721, 1657553135);
ALTER TABLE `Chats_members`
  ADD PRIMARY KEY (`member_id`),
  ADD UNIQUE KEY `member_id` (`member_id`),
  ADD KEY `chat_id` (`chat_id`,`group_user_id`);


INSERT INTO `messages` (`message_id`, `chat_id`, `self_id`, `message`, `self_deleted`, `time`) VALUES
(1, 1, 26618662357432, 'msg', 'false', 1657553237302),
(2, 1, 459937721, 'msg', 'false', 1657553248586),
(3, 1, 404, 'msg', 'msg', 1657553341414),
(62, 3, 459937721, 'msg', 'false', 1657763308852),
(63, 3, 459937721, 'msg', 'false', 1657777720634),
(64, 1, 459937721, 'msg', 'false', 1657846608972),
(65, 1, 26618662357432, 'msg', 'false', 1657846608972);

ALTER TABLE `messages`
  ADD PRIMARY KEY (`message_id`),
  ADD UNIQUE KEY `message_id` (`message_id`),
  ADD KEY `chat_id` (`chat_id`,`self_id`);

ALTER TABLE `messages`
  MODIFY `message_id` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=66;
COMMIT;

 

[Kryptyx]

Couldn't you just select from the `messages` table directly?
 

SELECT * FROM `messages` WHERE `self_id` = 459937721 ORDER BY `time` DESC;

There are a few things I would change with the schema as a whole. I would add foreign key constraints and potentially cascade on delete. Also `self_id` should probably be `member_id` since that seems to represent the data more accurately.

To add to all of this, redis or a pub/sub system like AWS SNS or Pusher would be more ideal than MySQL.

[shadow_ray]

13 hours ago, QQ_cazo said:

(3, 1, 404, 'msg', 'msg', 1657553341414),

This is not a valid record, the second string should be either 'true' or 'false'.

 

It looks like to me that one member can be part of multiple chats (Chats_members.self_id), it makes sense so far, but there is a one-to-one relationship from the other direction (members.chat_id). What's the point of this?

[QQ_cazo]

On 7/15/2022 at 5:18 AM, Kryptyx said:

Couldn't you just select from the `messages` table directly?
 

SELECT * FROM `messages` WHERE `self_id` = 459937721 ORDER BY `time` DESC;

There are a few things I would change with the schema as a whole. I would add foreign key constraints and potentially cascade on delete. Also `self_id` should probably be `member_id` since that seems to represent the data more accurately.

To add to all of this, redis or a pub/sub system like AWS SNS or Pusher would be more ideal than MySQL.

@Kryptyxi would say yes, but i need to check weather the user is still in the group or not, which is stored as "Chats_members"

 

I am using redis pub/sub inside node js. (mysql is only for storage behind a cache)

 

by chance, could you give an example on the foreign key constraints? (as im sorta new to sql)

 

21 hours ago, shadow_ray said:

This is not a valid record, the second string should be either 'true' or 'false'.

 

It looks like to me that one member can be part of multiple chats (Chats_members.self_id), it makes sense so far, but there is a one-to-one relationship from the other direction (members.chat_id). What's the point of this?

@shadow_ray for the second record, its suppose to be a bigint going back to the chat id.

 

yes, a member can be part of upto 128 chats, when a user is "invited" to the chat, they are added to the "chat_members", allowing messaging in that "chat", if the user id is not in the "chat_members" then they arent allowed to view the chat.

 

 

i am open to changing everything as this is just a project and not full production

[mariushm]

I wouldn't use enum for the chat message status.  The enum physically still uses at least a byte in the database so you may as well use an unsigned int (32 bits) or a byte.

You could have different bits in the value use different meanings  ex b1 = deleted by user, b10 = deleted by moderator b100 = deleted by administrator,  b1000 = edited  ... and so on.

 

May be premature optimization but I have doubts you need bigint for chat_id ... are you gonna have more than 2^32 channels?

Could also start with the member_id as a 32 bit value if the ids will be incremental, but may make more sense to use unique 64 bit GUID like ids.

 

You should think if you want to accept guests or not as in temporary user accounts - in that case you could give such temporary users an id out of a pool of IDs and maybe have a parameter in the database "last activity" which you update once every 15m-1h if that someone posts a message or something, and if there's no update for a few days or whatever amount you think of, you could "recycle" that user id. If such user id is reused, you would update the "signup time" for this user so that this user won't have access to the chat history of the previous user that used this id (should you wish to implement chat history or something like that) 

 

I'll probably say channel but I mean chat

 

ex members

member_id

time_signup

time_lastseen

member_nick

 

chats

id

chat_title

chat_description

chat_mode  -  free join, requires invitation, only moderators or only administrators, restricted (ex banned chat title) or temporary disabled

chat_users - how many users are in chat

chat_lastactivity - when the chat was active last time (could be updated once every few minutes or some random interval if you have very busy chats)

 

chat_invitations

id

chat_id

member_id

invited_by

invite_code

 

when user invites someone, add entry in chat invitations and send a message to that user... he/she may not see or ignore it. At next log in, your system could check the invitations page to see if there are pending invitations and resend them to user

when user accepts one invitation, the invitation is removed and user is added to chat_members

 

can users randomly join any channel if they're not invited, are channels that don't require invitation?

Can user leave a chat (close window of that chat, or that tab) and re-enter that chat later? If so chat members should probably be a table of who's permitted to join the chat, not the actual users/people in the chat

or you could have another column called "member_active" or something like that as in "user is present in chat" - this could also be used next time user logs in, to automatically reopen the chats user had active last time.

 

chat_members

chat_id

member_id

invitedby_id

member_active

 

messages

message_id

chat_id

message_time

message_status

message_data

 

status could be deleted by user, deleted by mod, admin, edited

maybe consider wrapping the text of the message with some extra info  (or have separate columns)

message_data could contain just the text , but could be a json encoded package with the text. Could also contain some extra stuff like  "last edited at ", edit reason, extra options like "don't parse smileys", "don't make urls clickable" as this forum has option at least for the smiley part (if a user posts a lot of smileys or something a moderator could edit the message and check that option which then gets saved into the message from that user - you'll have to think if all chat members will be sent a special chat message through which locally the previous message will be changed or how you'd handle this

The client could request all the chats modified or added since last request time and then client would also receive a previously sent message but which was edited or deleted in the meantime, and the client would update what's on users screen/application.

if you bundle the text into a json or serialize it with some other things, it would make it harder to implement full text search or other features with the text of the chats. Up to you how you implement it.

 

 

 

[QQ_cazo]

10 hours ago, mariushm said:

I wouldn't use enum for the chat message status.  The enum physically still uses at least a byte in the database so you may as well use an unsigned int (32 bits) or a byte.

You could have different bits in the value use different meanings  ex b1 = deleted by user, b10 = deleted by moderator b100 = deleted by administrator,  b1000 = edited  ... and so on.

 

May be premature optimization but I have doubts you need bigint for chat_id ... are you gonna have more than 2^32 channels?

Could also start with the member_id as a 32 bit value if the ids will be incremental, but may make more sense to use unique 64 bit GUID like ids.

 

You should think if you want to accept guests or not as in temporary user accounts - in that case you could give such temporary users an id out of a pool of IDs and maybe have a parameter in the database "last activity" which you update once every 15m-1h if that someone posts a message or something, and if there's no update for a few days or whatever amount you think of, you could "recycle" that user id. If such user id is reused, you would update the "signup time" for this user so that this user won't have access to the chat history of the previous user that used this id (should you wish to implement chat history or something like that) 

 

I'll probably say channel but I mean chat

 

ex members

member_id

time_signup

time_lastseen

member_nick

 

chats

id

chat_title

chat_description

chat_mode  -  free join, requires invitation, only moderators or only administrators, restricted (ex banned chat title) or temporary disabled

chat_users - how many users are in chat

chat_lastactivity - when the chat was active last time (could be updated once every few minutes or some random interval if you have very busy chats)

 

chat_invitations

id

chat_id

member_id

invited_by

invite_code

 

when user invites someone, add entry in chat invitations and send a message to that user... he/she may not see or ignore it. At next log in, your system could check the invitations page to see if there are pending invitations and resend them to user

when user accepts one invitation, the invitation is removed and user is added to chat_members

 

can users randomly join any channel if they're not invited, are channels that don't require invitation?

Can user leave a chat (close window of that chat, or that tab) and re-enter that chat later? If so chat members should probably be a table of who's permitted to join the chat, not the actual users/people in the chat

or you could have another column called "member_active" or something like that as in "user is present in chat" - this could also be used next time user logs in, to automatically reopen the chats user had active last time.

 

chat_members

chat_id

member_id

invitedby_id

member_active

 

messages

message_id

chat_id

message_time

message_status

message_data

 

status could be deleted by user, deleted by mod, admin, edited

maybe consider wrapping the text of the message with some extra info  (or have separate columns)

message_data could contain just the text , but could be a json encoded package with the text. Could also contain some extra stuff like  "last edited at ", edit reason, extra options like "don't parse smileys", "don't make urls clickable" as this forum has option at least for the smiley part (if a user posts a lot of smileys or something a moderator could edit the message and check that option which then gets saved into the message from that user - you'll have to think if all chat members will be sent a special chat message through which locally the previous message will be changed or how you'd handle this

The client could request all the chats modified or added since last request time and then client would also receive a previously sent message but which was edited or deleted in the meantime, and the client would update what's on users screen/application.

if you bundle the text into a json or serialize it with some other things, it would make it harder to implement full text search or other features with the text of the chats. Up to you how you implement it.

 

 

 

this is amazing thank you!!!

 

a sort of follow up question, someone mentioned foreign keys, how could they be used here?