CODEWITHSUNDEEP
phpmysql
Francesco
Francesco

Reputation: 25239

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

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

Upvotes: 1

Views: 165

Answers (3)

Mike Brant
Mike Brant

Reputation: 71384

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

Upvotes: 0

Nechemya Kanelsky
Nechemya Kanelsky

Reputation: 673

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

Upvotes: 3

conductr
conductr

Reputation: 81

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.

Upvotes: 0

Related Questions