0

I'm new to StackOverflow, first time posting here and I wonder if somebody can help me with a mysql query.

I have this database:

Review Table
id | user_id | from_user_id | parent_id
5       10           15           0
6       10           15           5
7       10           19           0
8       11           12           0
9       10           12           0
10      10           13           0
11      10           12           9

Basically I'm creating a review system, from_user_id is writing a review to user_id and that will have parent_id = 0 but when User_id is replying to the review, it will have the same (user_id and from_user_id ) but parent_id == id of the review that is replaying.

How can I get a query that will show a result in this order:

id | user_id | from_user_id | parent_id
5       10           15           0
6       10           15           5
7       10           19           0
9       10           12           0
11      10           12           9
10      10           13           0

Selecting all reviews from a specific user in sorted by created_at ( didn't included in my example ) but everytime a review has a parent_id to show it in the next row and after that to move to the next review row

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
mario
  • 1
  • I added the [tag:hierarchical-data] tag. There are a lot of other answers on Stack Overflow about this general topic. You might find some that are helpful. – Bill Karwin Aug 07 '20 at 18:23

1 Answers1

0

Ok guys, I solved the problem

SELECT
    reviews.id, reviews.user_id, reviews.from_user_id, reviews.parent_id, reviews.created_at,
    CASE WHEN parent.Id IS NULL THEN reviews.created_at ELSE parent.created_at END AS sort1,
    CASE WHEN parent.Id IS NULL THEN NULL ELSE reviews.created_at END AS sort2
        
FROM reviews
LEFT JOIN reviews AS parent
ON reviews.parent_id = parent.id

where reviews.user_id = '52'
ORDER BY sort1, sort2
mario
  • 1