how to join multiple tables and sort the result as a json like format

Question

I'm super new to mysql and I'm pretty sure this question might seem too elementary for some and I'm sorry about that

I have 3 tables: Portfolio, PortfolioFiles and PortfolioImages. The table Portfolio has one-to-many relationship with PortfolioFiles and PortfolioImages

What i need to do is to first get the list of last 10 Portfolios by date as well as their files and images. I tried RIGHT JOIN and it works but I am facing multiple problems

1. When I sort set OFFSET and LIMIT it's only fetching 1 or 2 Portfolios because some Portfolios have multiple image and files in aforementioned tables so I don't know how to do that right
2. when i get the results they are all rows. I started working with noSQL databases like mongo and firebase so this SQL databases look mystery to me. So i would appreciate if someone could point out how i can query in a way that i don't have to sort out the fetched result

Table Portfolio

id                // primary key
uid               // foreign key reference User(id)
title
content
createdAt

Table PortfolioFiles

id               // primary key
uid              // foreign key references User(id) 
portfolioId      // foreign key references Portfolio(id) 
url 
createdAt

Table PortfolioImages

id               // primary key
uid              // foreign key references User(id)
portfolioId      // foreign key references Portfolio(id)
url
createdAt

This is what I did:

            SELECT 
                Portfolio.*, 
                PortfolioFiles.* , 
                PortfolioImages.* 
            FROM 
                Portfolio
            RIGHT JOIN 
                (PortfolioFiles, PortfolioImages)
            ON 
                PortfolioFiles.portfolioId=Portfolio.id 
            AND 
                PortfolioImages.portfolioId=Portfolio.id
            WHERE 
                Portfolio.uid = 49
            ORDER BY 
                Portfolio.createdAt DESC 

UPDATE: ADDED SAMPLE DATA

this is how i want my output to be like:

            {
                "id": 52,
                "uid": 49,
                "title": "hey this is new title",
                "content":"this is my content",
                "createdAt": "2020-09-08 17:34:23",
                "files": [{
                        "id": 12,
                        "portfolioId": 52,
                        "uid": 49,
                        "url": "some random file url",
                        "createdAt": "2020-09-08 17:34:23"
                    },
                    {
                        "id": 16,
                        "portfolioId": 52,
                        "uid": 49,
                        "url": "some random file url",
                        "createdAt": "2020-09-08 17:34:23"
                    },],
                "images": [
                    {
                        "id": 44,
                        "portfolioId": 52,
                        "uid": 49,
                        "url": "some random file url",
                        "createdAt": "2020-09-08 17:34:23"
                    },
                    {
                        "id": 45,
                        "portfolioId": 52,
                        "uid": 49,
                        "url": "some random image url",
                        "createdAt": "2020-09-08 17:34:23"
                    },
                    {
                        "id": 46,
                        "portfolioId": 52,
                        "uid": 49,
                        "url": "some random file url",
                        "createdAt": "2020-09-08 17:34:23"
                    },
                ]
            }

this is the current result:

id|uid|       title        |       content    |    createdAt     |id|pid|uid|     url           |     createdAt    |id|pid|uid|      url           |    createdAt                                                                
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|52|49|hey this is new title|this is my content|2020-09-08 17:34:23|12|52|49|some random file url|2020-09-08 17:34:23|12|52|49|some random image url|2020-09-08 17:34:23|    
|52|49|hey this is new title|this is my content|2020-09-08 17:34:23|12|52|49|some random file url|2020-09-08 17:34:23|12|52|49|some random image url|2020-09-08 17:34:23|
|52|49|hey this is new title|this is my content|2020-09-08 17:34:23|12|52|49|some random file url|2020-09-08 17:34:23|12|52|49|some random image url|2020-09-08 17:34:23|
|52|49|hey this is new title|this is my content|2020-09-08 17:34:23|12|52|49|some random file url|2020-09-08 17:34:23|12|52|49|some random image url|2020-09-08 17:34:23|
|52|49|hey this is new title|this is my content|2020-09-08 17:34:23|12|52|49|some random file url|2020-09-08 17:34:23|12|52|49|some random image url|2020-09-08 17:34:23|
|52|49|hey this is new title|this is my content|2020-09-08 17:34:23|12|52|49|some random file url|2020-09-08 17:34:23|12|52|49|some random image url|2020-09-08 17:34:23|
 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------

this is the data in the DB PortfolioImages table

     id|portfolioId|uid|       url           |     createdAt     |
     ------------------------------------------------------------
    |44|     52    |49 |some random image url|2020-09-08 17:34:23|
    |45|     52    |49 |some random image url|2020-09-08 17:34:23|
    |46|     52    |49 |some random image url|2020-09-08 17:34:23|
     ------------------------------------------------------------

PortfolioFiles table

     id|portfolioId|uid|       url           |     createdAt     |
     ------------------------------------------------------------
    |16|     52    |49 | some random file url|2020-09-08 17:34:23|
    |12|     52    |49 | some random file url|2020-09-08 17:34:23|
     ------------------------------------------------------------

Portfolio table

      id|uid|       title        |       content    |    createdAt     
   ------------------------------------------------------------------
  |52|49|hey this is new title|this is my content|2020-09-08 17:34:23
   ------------------------------------------------------------------
            

Answer 1

The query you are trying to write is:

SELECT p.*, pf.*, pi.* 
FROM Portfolio p LEFT JOIN
     PortfolioFiles pf
     ON pf.portfolioId = p.id LEFT JOIN
     PortfolioImages pi
     ON pi.portfolioId = p.id
WHERE p.uid = 49
ORDER BY p.createdAt DESC ;

I don't think this is what you really want, because this produces a Cartesian product between the files and images for a given portfolio. So, if there are 4 files and 3 images, you get 12 rows. Well, actually, you would get 10 rows with a LIMIT.

However, your question is not clear on what results actually want. I would advise you to ask a new question -- but a bit simpler. Sample data, desired results, and an explanation of the logic you want suffices. A DB Fiddle is also appreciated.