Echo nested JSON array from related database tables with a single query?

Question

I have two database tables that contain information about land contracts. They are related with land_contract_annual_price.land_contract_id -> land_contract.land_contract_id.

Table 'land_contract'

Table 'land_contract_annual_price'

If a land contract has the value "Rörligt pris" in the field land_contract_price_type, there are related values in the table land_contract_annual_price. At the moment I'm doing two queries, one to each table. I then merge the results and present the land contract as a nested JSON array like this:

Version 1

[  
 {  
  "land_contract_id":118,
  "land_contract_name":"Avtalsnamn",
  "location_id":71,
  "land_contract_link":"",
  "land_contract_notes":"",
  "land_owner_id":2,
  "land_contract_start_date":"2019-07-25",
  "land_contract_end_date":"2023-07-25",
  "land_contract_terminated":"false",
  "land_contract_payment_interval":"Halv\u00e5rsvis",
  "land_contract_price_type":"R\u00f6rligt \u00e5rspris",
  "land_contract_fixed_annual_price":null,
  "land_contract_annual_prices":[  
    {"year":1, "price":873.00},
    {"year":2, "price":77289.00},
    {"year":3, "price":8.00},
    {"year":4, "price":0.00},
    {"year":5, "price":8729.00}
  ]
 }
]

If a land contract has the value "Fast pris" in the field land_contract_price_type, there are no related values in the table land_contract_annual_price. In that case I present the land contract like this (without the extra array at the end):

Version 2

[
 {
  "land_contract_id":13,
  "land_contract_name":null,
  "location_id":null,
  "land_contract_link":"https:\/\/www.something.com\/preview\/Sl%C3%A4pvdam%20Edda\/Kddal\/Bddkta\/Besika%20Markavtal%20%20Halmstad%202016-03-08.pdf?role=personal",
  "land_contract_notes":"",
  "land_owner_id":null,
  "land_contract_start_date":"2016-03-08",
  "land_contract_end_date":"2026-03-08",
  "land_contract_terminated":"true",
  "land_contract_payment_interval":"\u00c5rsvis",
  "land_contract_price_type":"Fast \u00e5rspris",
  "land_contract_fixed_annual_price":"6000.00"
 }
]

What I didn't think of, is that this solution is bad when I'm fetchin ALL the land contracts. If I'm going to do a second query to another table whenever a land contract has the value "Rörligt pris" in the field land_contract_price_type, I'm going to do hundreds of extra queries.

Is there a way to create the nested JSON array with one (1) query when a land contract has the value "Rörligt pris" in the field land_contract_price_type?

Thanks!

Below is my current code.

function read($pdo, $Id = null, $ResponseMessage = null) {

    $params = [];
    $array = [];

    $sql = "SELECT  lc.Id, lc.Name, lc.LocationId, l.Name AS LocationName, lc.Notes, lc.LandOwnerId, lo.Name AS LandOwnerName, lc.StartDate, lc.EndDate, lc.IsTerminated, lc.PaymentInterval, lc.PriceType, lc.FixedAnnualPrice, lc.Link, lc.Created, lc.Updated, lcap.AnnualPriceYear AS Year, lcap.AnnualPriceAmount AS Amount
            FROM LandContract lc
            LEFT JOIN Location l ON l.Id = lc.LocationId
            LEFT JOIN LandOwner lo ON lo.Id = lc.LandOwnerId
            LEFT JOIN LandContractAnnualPrice lcap ON lcap.LandContractId = lc.Id  
            ORDER BY lc.Id  DESC, lcap.AnnualPriceYear DESC
            ";
    if ($Id) {
        $sql .= 'WHERE lc.Id = ?';
        $params[] = $Id;
    }

    echo $sql;

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    while ($row = $stmt->fetch()) {
        // Fields we want to extract from the select statement into the array 
        $select_fields = ['Id', 'Name', 'LocationId', 'LocationName', 'Link', 'Notes', 'LandOwnerId', 'LandOwnerName',
                            'StartDate', 'EndDate', 'IsTerminated', 'PaymentInterval', 
                            'PriceType', 'FixedAnnualPrice ', 'Created', 'Updated'];

        if (!isset($array[$row['Id']])) {
            // initialize the subarray if it has not been set already 
            $array[$row['Id']] = array_intersect_key($row, array_flip($select_fields));

            if ($row['Year'] != null) {
                $array[$row['Id']]['AnnualPrices'] = [];
            } else {
                $array[$row['Id']]['AnnualPrice'] = $row['FixedAnnualPrice'];
            }
        }

        if ($row['Year'] != null) {
            $array[$row['Id']]['AnnualPrices'][] = ['Year' => $row['Year'], 'Amount' => $row['Amount']];
        }

    }

    if (empty($array)) {
        $ResponseMessage = new ResponseMessage();
        $ResponseMessage->Status = 'Error';
        $ResponseMessage->Message = 'No results';
        echo json_encode($ResponseMessage, JSON_UNESCAPED_UNICODE);
        exit;
    }

    $Response = array();

    if ($ResponseMessage) {
        $Response['Status'] = $ResponseMessage->Status;
        $Response['Message'] = $ResponseMessage->Message;
    }

    $Response['LandContracts'] = array_values($array);

    echo json_encode($Response, JSON_UNESCAPED_UNICODE);

    $stmt = null;
}

It seems you would benefit from implementing SQL joins. Assuming you're using MySQL, you might want to use JSON functions in your query. — Ro Achterberg
– Ro Achterberg, Commented Jul 25, 2019 at 20:14
@RoAchterberg Thanks for your answer. I used to have query with LEFT JOIN but I didn't know if, or how, I could get that JSON structure. Any suggestions? — Rawland Hustle
– Rawland Hustle, Commented Jul 25, 2019 at 20:58
I suggest you take a look at the various JSON_* functions provided by MySQL to retrieve your result as a JSON-encoded representation of the column data. — Ro Achterberg
– Ro Achterberg, Commented Jul 25, 2019 at 21:01
@RoAchterberg The JSON function did not work. Turns out my host is running Maria DB. I really thought it was MySQL. — Rawland Hustle
– Rawland Hustle, Commented Jul 26, 2019 at 9:02
@RoAchterberg Is there a way to create the JSON I want with PHP (from one sql query)? — Rawland Hustle
– Rawland Hustle, Commented Jul 26, 2019 at 9:07

Qirel · Accepted Answer · 2019-07-26 13:51:57Z

2

You are better off using a JOIN query, and then structure your array from the result - having a query within a loop is often a very bad idea, and an indicator that you can use a JOIN instead.

You want to use a LEFT JOIN, joining them on the land_contract_id in both tables.

Then loop your results, and construct your array, which you can end up encoding into a JSON string once done.

$params = [];
$array = [];

$sql = "SELECT lc.*, 
               py.land_contract_annual_price_year AS `year`,  
               py.land_contract_annual_price_amount AS `amount`
        FROM land_contract AS lc
        LEFT JOIN land_contract_annual_price AS py 
            ON py.land_contract_id = lc.land_contract_id
        ";
if (isset($_POST['land_contract_id'])) {
    $sql .= 'WHERE lc.land_contract_id = ?';
    $params[] = $_POST["land_contract_id"];
}

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
while ($row = $stmt->fetch()) {
    // Fields we want to extract from the select statement into the array 
    $select_fields = ['land_contract_id', 'land_contract_name', 'location_id', 'land_contract_link', 'land_contract_notes', 'land_owner_id', 
                        'land_contract_start_date', 'land_contract_end_date', 'land_contract_terminated', 'land_contract_payment_interval', 
                        'land_contract_price_type', 'land_contract_fixed_annual_price '];

    if (!isset($array[$row['land_contract_id']])) {
        // initialize the subarray if it has not been set already 
        $array[$row['land_contract_id']] = array_intersect_key($row, array_flip($select_fields));

        if ($row['year'] != null) {
            $array[$row['land_contract_id']]['land_contract_annual_prices'] = [];
        } else {
            $array[$row['land_contract_id']]['land_contract_annual_price'] = $row['land_contract_fixed_annual_price'];
        }
    }

    if ($row['year'] != null) {
        $array[$row['land_contract_id']]['land_contract_annual_prices'][] = ['year' => $row['year'], 'amount' => $row['amount']];
    }

}

if (empty($array)) {
    echo "No results";
    exit;
}

echo json_encode($array, JSON_UNESCAPED_UNICODE);

edited Jul 26, 2019 at 13:51

answered Jul 26, 2019 at 11:42

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Rawland Hustle Over a year ago

Thanks! PHP is complaining about this line: $array[$row['land_contract_id']] = array_intersect_key($row, array_flip($select_fields);. It says "Parse error: syntax error, unexpected ';', expecting ')'.

Rawland Hustle Over a year ago

Fixed it (added a paranthesis). I got another error. Let me check.

Rawland Hustle Over a year ago

Also changed LEFT JOIN land_contract_annual_price_year to land_contract_annual_price. Now it works! However, "You seem to want a LEFT JOIN, as you want results in land_contract regardless if there's a match in land_contract_annual_price or not." is not true. I did not know that LEFT JOIN worked like that. I only want the results if there is a match in land_contract_annual_price. Is that possible?

Qirel Over a year ago

Yes, then you want an INNER JOIN (which is the same as JOIN).

Qirel Over a year ago

As a sidenote, you don't have to name your columns with the prefix of the table-name. For example, land_contract_annual_price_amount is rather long - you can simply use amount. The schema then becomes land_contract_annual_price.amount (table.column instead of table.table_column), which is much easier to work with.

|

Collectives™ on Stack Overflow

Echo nested JSON array from related database tables with a single query?

1 Answer 1

16 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related