CODEWITHSUNDEEP
mysqlsqldatabase
Shooting Stars
Shooting Stars

Reputation: 835

Determining if all messages are read within a thread MySQL

Lets say you had a database schema that looked like this:

Message: (id: int PK, read: boolean, thread_id: int FK, ...)

Thread: (thread_id: int PK, ...)

in Message the read value represents if both parties have viewed the message.

How would you construct a query that returns every thread_id, and another value that represents if every message belonging to the thread has been read. EG: Messages: (1, 1, 1), (2, 1, 1), (3, 0, 1), (4, 1, 2), (5, 1, 2), (6, 1, 2)

Querying this would return as (read, thread_ID) => (0, 1), (1, 2) Thread 1 has a read value of 0 because a single message was unread. Thread 2 has a read value of 1 because every message was read.

How would you make this query?

Upvotes: 0

Views: 40

Answers (3)

Faeeria
Faeeria

Reputation: 826

You could also use the BIT_AND() aggregation function. But it seems to be heavier than using MIN https://www.db-fiddle.com/f/fNAh2VkpN9TeyDR7pqBCSR/1#

Upvotes: 0

ysth
ysth

Reputation: 98398

select thread_id, min(`read`) as `read` from message group by thread_id

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Hmmm . . . One method is a correlated subquery:

select t.*,
       (select min(m.read)
        from message m
        where m.thread_id = t.thread_id
       ) as flag
from thread t;

Note that a very similar method is:

select t.*,
       (not exists (select 1
                    from message m
                    where m.thread_id = t.thread_id and
                          not m.read
                   )
       ) as flag
from thread t;

Although this should be a wee bit faster with an index on message(thread_id, read).

Upvotes: 0

Related Questions