0

I am designing data model in following way:

Table 1:
     id: number
    somedata: String
    somedata: String

Table 2:
    id: number
    Table1_id: number
    somedata: String
    somedata: String

Table 3:
    id: number
    Table2_id: number
    somedata: String

1st table is joined to 2nd table with one to many relationship. 2nd table is joined to 3rd table with one to many relationship. I am using java, JPA. I have to return JSON via rest api in following format:

{
  "table1_id": 1,
  "somedata": "somedata",
  "table2_data": [
    {
      "table2_id": 1,
      "somedata": "somedata",
      "table3_data": [
        {
          "table3_id": 1,
          "somedata": "somedata"
        },
        {
          "table3_id": 2,
          "somedata": "somedata"
        }
      ]
    },
    {
      "table2_id": 2,
      "somedata": "somedata",
      "table3_data": [
        {
          "table3_id": 3,
          "somedata": "somedata"
        },
        {
          "table3_id": 4,
          "somedata": "somedata"
        }
      ]
    }
  ]
}

If I use mysql and above table structure I will need to use minimum 3 database calls to fetch from 3 tables separately and will have to construct my json. Is there a way where I can reduce database calls and also reduce effort to construct my json? I am open to change database(mysql) also. I just need fast, optimised and best solution. Note that table1, table2 and table3 has different fields so I can not denormalize them to single table.

I already have seen this question and my question is different than this: https://stackoverflow.com/questions/11047151/nested-json-from-3-one-to-many-tables

2
  • Which are the primary keys of your tables? And the foreigh keys?
    – joanolo
    Feb 15, 2017 at 8:17
  • id field from each table is primary key of that table. Table1_id and Table2_id are the foreign keys. Feb 15, 2017 at 9:14

1 Answer 1

0

In PostgreSQL:

SELECT
    row_to_json (r1.*)
FROM
    (
        SELECT 
            table_1_id, 
            somedata,
            array(
                SELECT
                    row_to_json (r2.*)
                FROM
                (
                SELECT 
                        table_2_id, 
                        somedata,
                        array(
                            SELECT
                                row_to_json(r3.*, true)
                            FROM
                            (
                                SELECT
                                    table_3.table_3_id,
                                    table_3.somedata
                                FROM
                                    table_3
                                WHERE
                                    table_3.table_2_id = table_2.table_2_id
                                ORDER BY
                                    table_3_id
                            ) r3

                        ) AS table_2_data
                  FROM 
                        table_2  
                 WHERE 
                        table_2.table_1_id = table_1.table_1_id 
                 ORDER BY 
                        table_2_id
                ) r2
           ) AS table_2_data
        FROM 
            table_1
    ) AS r1

(with a few caveats, because your first level response is not an array; and I think it should).

The syntax is a bit convoluted and can probably be simplified

Test it at http://rextester.com/SEZ32413

3
  • Thanks joanolo. I will try this solution and will update here. Feb 15, 2017 at 9:16
  • Thanks joanolo. It is giving me expected result in single database call. But this query contains many nested queries. Will it be efficient solution ? Feb 15, 2017 at 16:58
  • You need to check three tables... no matter what.
    – joanolo
    Feb 15, 2017 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.