What is the best way to get posts with comments from MariaDB?

Question

I have a MariaDB DB with three tables:

1. posts

ID | title         | content    | author_id | likes_count
--------------------------------------------------
1  | Abcd          | content_01 | 1         | 20
2  | BfeBB         | content_02 | 1         | 112
3  | ACFFDV        | content_03 | 3         | 0
4  | FVERVR        | content_04 | 2         | 8
5  | BGF           | content_05 | 2         | 3
6  | FGBFDG        | content_06 | 1         | 54
7  | BFD           | content_07 | 3         | 1
8  | FDBD          | content_08 | 1         | 1
9  | DFGBFG        | content_09 | 4         | 0

2. authors

ID | name          | email
----------------------------
1  | Author1       | content_01
2  | Author2       | content_02
3  | Author3       | content_03
4  | Author4       | content_04

3. comments

ID | title         | content     | post_id  | parent_comment_id
--------------------------------------------------------------
1  | Abcd          | comment_01  | 1        | NULL
2  | BfeBB         | comment_02  | 1        | 1
3  | ACFFDV        | comment_03  | 3        | 2
4  | FVERVR        | comment_04  | 2        | 1
5  | BGF           | comment_05  | 2        | NULL
6  | FGBFDG        | comment_06  | 1        | 2
7  | BFD           | comment_07  | 3        | 2
8  | FDBD          | comment_08  | 1        | 4
9  | DFGBFG        | comment_09  | 4        | NULL

Ny question is, what is the best way to get 10 posts of author #2 ordered by likes number as an array of objects where each object includes author name and an array of comments.

For example, I want to get a result like this in PHP:

[
  {
    ID -> 2,
    title -> 'BfeBB',
    content -> 'content_02',
    author_id -> 1,
    likes_count -> 112,
    author_name -> Author1,
    comments -> [
                  {
                    ID -> 4,
                    title -> 'FVERVR',
                    content -> 'comment_04',
                    parent_comment_id -> 1,
                  },
                  {
                    ID -> 5,
                    title -> 'BGF',
                    content -> 'comment_05',
                    parent_comment_id -> NULL,
                  },
               ],
  },
  {
    ID -> 6,
    title -> 'FGBFDG',
    content -> 'content_06',
    author_id -> 1,
    likes_count -> 54,
    author_name -> Author1,
    comments -> []
  },
]

UPDATE

I have tried doing it via PHP by combining 2 MariaDB queries:

1. Get posts & author data in a LEFT JOIN statement and it worked as expected.
2. However, the second query for getting comments didn't work and returned this error Error in query (1235): This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' because I added the LIMIt & ORDER BY in the subquery.

---

I'm using MariaDB 10.4, PHP 8.1 and WordPress's $wpdb.

Thanks in advance.