0

Lets say I have this table:

------------------------------------------------
| id | user_id | message | reply_to_message_id |
------------------------------------------------
|  1 |       1 |       a |                null |
|  2 |       1 |       b |                null |
|  3 |       1 |       c |                null |
|  4 |       1 |       d |                   1 |
|  5 |       2 |       e |                null |
------------------------------------------------

My purpose of the query is to get all the messages which message col matches search query as well as all the messages which have reply_to_message_id equal to id of the result of the previous part. I hope this make sense, my brain is junk by the end of the day.

The result with search query 'a' would be:

-----------------
| id | is_reply |
-----------------
|  1 |    false |
|  4 |     true |
-----------------

I have the first part:

SELECT id FROM messages
WHERE user_id = 1 AND message LIKE "%a%"

Of course I can loop through the result of this query but it's not right and I feel like there has to be an easy way to get this result.

BTW I'm using laravel query builder but I don't think that it has what I need.

s0up
  • 372
  • 4
  • 13
  • *WHERE user_id = id* why? – forpas Feb 16 '19 at 17:29
  • Messages an their answers form a tree, so you need a tree traversal or hierarchical query. That is not simple. Luckily, SO already have answers to such questions. – Shadow Feb 16 '19 at 17:32
  • is the table you display the *messages* table? it doesn't look like it. What is that table? – Martin Feb 16 '19 at 17:32
  • @forpas id is just an extra condition to fetch only specific user messages – s0up Feb 16 '19 at 17:45
  • @Martin It is actually the messages table – s0up Feb 16 '19 at 17:46
  • @s0up `id` is a column in your table (probably the primary key), so this does not make any sense. Maybe you mean something more specific like `user_id = 1` – forpas Feb 16 '19 at 17:47
  • @forpas you are right. I've changed the question. – s0up Feb 16 '19 at 17:49
  • This does look similar to the question it is marked as a duplicate of. See this answer https://stackoverflow.com/a/33737203/813125 The name of what you are looking for is "a recursive query", it might look a bit scary but the basics are pretty logical. – Björn Nilsson Feb 17 '19 at 20:56
  • @BjörnNilsson Yes, but I was needed a solution ASAP. It is look creepy at first, especially because MySQL version is below 5.8, so I've solved it with two queries for now. I will learn about recursive queries and variables when I'll have some time. – s0up Feb 18 '19 at 22:28

0 Answers0