mySQL determine if first element of resulted array is equal to given value

Question

here's my table msg_to_user and imagine it's a chat app or a simple forum website

|msg_id|sender_id|receiver_id| subject_id |msg           |time               |
--------------------------------------------------------------------------
|   1  |  123    |  456      |     3      |"yadda yadd.."|2016-01-31 00:27:16|
|   2  |  456    |  123      |     3      |"ladida   .." |2016-01-31 00:37:16|
|   3  |  456    |  123      |     3      |"johndo   .." |2016-01-31 01:47:04|
|   4  |  123    |  456      |     3      |"xxxxxx   .." |2016-01-31 02:47:04|
|   5  |  456    |  123      |     3      |"qwerty   .." |2016-01-31 03:47:04|
|   6  |  789    |  456      |     9      |"dadda kadd.."|2016-01-31 00:11:16|
|   7  |  789    |  456      |     9      |"fadda jadd.."|2016-01-31 00:12:16|
|   8  |  123    |  789      |     9      |"fadda jadd.."|2016-01-31 00:13:16|

below is mysql query, that i use to determine how many, among all the threads i participated in a forum, are the one I actually started:

SELECT *
FROM msg_to_user 
GROUP BY subject_id // a given conversation unique id
ORDER BY time ASC

Then i would use a PHP for-loop to check in every mtu_thread who is the first sender.

For instance for the conversation with subject_id = 3 you can see that user 123 actually started the thread

my question is: whether it's possible to just use MYSQL without using PHP

can you post explained `msg_to_user` table structure? (and any other table affected) — fusion3k, Feb 09 '16 at 21:42
Doing this kind of logic in mysql is not a good idea. Why not just use php for this? Also, a better approach, which would make things a lot easier, is to add a thread_started_by field which would store the ID of the user who started the thread. — AnchovyLegend, Feb 09 '16 at 21:51
i don't mind using PHP.. i was just wondering if this is something that can be done or not... i have no idea about implications on performances etc.. — Francesco, Feb 09 '16 at 21:55
Do you have a subject (thread) table? If so you should show that, as it could potentially be used in this query. — Mike Brant, Feb 09 '16 at 22:18

score 3 · Accepted Answer · edited Feb 09 '16 at 22:02

3

what about:

    SELECT x.*
    FROM ( 
        SELECT * 
        FROM msg_to_user 
        GROUP BY subject_id 
        ORDER BY time ASC ) AS x
    WHERE x.sender_id = 123

edited Feb 09 '16 at 22:02

Francesco

24,839
29
105
152

answered Feb 09 '16 at 21:51

Nechemya Kanelsky

663
7
17

1

with this aproche is better use mysql instad of php – Nechemya Kanelsky Feb 09 '16 at 22:05
I am not sure if this is reliable. You are not providing any means to guarantee that the `time` value that is calculated when grouping is performed will be the lowest time (i.e. start of thread). – Mike Brant Feb 09 '16 at 22:20
1

@Francesco That ordering does absolutely nothing to determine which value goes into the grouping, as the ordering is applied after the `GROUP BY` operation. You need to use some sort of aggregation function to make sure you can guarantee the behavior you intend to happen during group aggregation. Without this, you will get unpredictable results from the `GROUP BY` operation. – Mike Brant Feb 09 '16 at 22:31

score 0 · Answer 2 · answered Feb 09 '16 at 21:51

0

I would structure the table so that you have a column first_sender_id or even better is_first_msg_in_thread as a boolean.

Then you could easily change the query to be

... where first_sender_id = 123

or

... where sender_id = 123 and is_first_msg_in_thread= 1

If I understand what you're trying to do, this should eliminate the need to loop.

answered Feb 09 '16 at 21:51

conductr

81
1
4

yes i know, but let's say in my example i cannot edit the table because i'm inheriting the site as is – Francesco Feb 09 '16 at 21:53
@Francesco oh that makes me sad, it's possible but way more complicated for my liking. Maybe you will find this a [helpful start](http://stackoverflow.com/questions/5817395/how-can-i-loop-through-all-rows-of-a-table-mysql) – conductr Feb 09 '16 at 21:59

Mike Brant · Answer 3 · 2016-02-09T22:43:00.943

Here is a more reliable choice than your selected answer. The subquery would identify the lowest numbered msg_id for each subject_id group. You then join against this result set to return the full message data for each of these first thread posts that match the criteria for sender_id

SELECT
    msg_to_user.*
FROM msg_to_user
INNER JOIN (
    SELECT MIN(msg_id) AS thread_start_id
    FROM msg_to_user
    GROUP BY subject_id
) AS first_thread_posts
    ON msg_to_user.msg_id = first_thread_posts.thread_start_id
WHERE msg_to_user.sender_id = ?
ORDER BY msg_to_user.`time` ASC

Note: Nn the subquery, I am assuming that msg_id is guaranteed to be ascending based on insert order (i.e. typical autoincrement field behavior, with no backfilling of id's if records were deleted from the system for some reason).

That being said, if you have some other table (i.e. subject table) which contains information at the thread level, you should add this to your question, as you will likely get a query which performs better if you can perform a simple join using this table rather than a subquery as I am showing. Also, per other posts, if this is a query you expect your application to run often, you should consider modifying the schema to either provide data at the subject/thread level or in this message table to indicate who started a thread (if applied at subject level) or which message indicates that start of the thread (if applied at message table level).

To me an ideal schema might look something like this

Subject table (thread)
-------------------
subject_id
started_by_user_id
started_at
[other fields as needed]

Message table can pretty much remain as is.

With this schema, you would actually query your subject table to get the list of threads started by the user with a very simple query like

SELECT *
FROM subjects
WHERE started_by_user_id = ?
ORDER BY started_at ASC

mySQL determine if first element of resulted array is equal to given value

3 Answers3