3
votes

Récupération des contraintes de la base de données SQL vers JSON en PHP

Supposons que je dispose de la structure de base de données suivante des constructeurs automobiles et des voitures correspondantes:

constructeurs:

SELECT manufacturer, founded_in FROM manufacturers

voitures :

{
    "manufacturers": {
        "Daimler AG": {
            "founded in": "1927",
            "cars": [
                "C Class",
                "E Class"
            ]
        },
        "Volkswagen AG": {
            "founded in": "1937",
            "cars": [
                "Golf"
            ]
        }
    }
}

La colonne id des fabricants est la clé primaire et le fabricant de cars a la contrainte de clé étrangère correspondante.

Je voudrais produire la sortie JSON suivante en utilisant PHP json_encode : p>

-------------------------------------
| car     | built_in | manufacturer |
|---------|----------|---------------
| C Class | 1993     | 1            |
| E Class | 1993     | 1            |
| Golf    | 1974     | 2            |
-------------------------------------

Pour obtenir les fabricants et leur found_in , je voudrais juste effectuer:

----------------------------------
| manufacturer | founded_in | id |
|--------------|------------|----|
| Daimler AG   | 1927       | 1  |
| Volkswagen AG| 1937       | 2  |
----------------------------------

Et aller chercher les à un tableau. Mais comment attribuer correctement les voitures après avoir effectué une deuxième requête?

php json arrays sql mysql

2 commentaires

Vous pourriez le faire avec une jointure intérieure, je suppose.

Comment? Je devrais attribuer plusieurs voitures à un fabricant en une seule jointure.

3 Réponses :

2
votes

Vous pouvez d'abord interroger toutes les données

$manufacturers = [];
foreach ($rows as $row) {
    if (!array_key_exists($row['manufacturer'], $manufacturers)) {
        $manufacturers[$row['manufacturer']] = array(
           'founded_in' => $row['founded_in'],
           'cars' => array());
    }

    array_push($manufactureres[$row['manufacturer']]['cars'], $row['car']);
}

et ensuite les convertir en votre structure en php

SELECT m.manufacturer, m.founded_in
  FROM manufacturers AS m
  JOIN cars AS c ON c.manufacturer_id = m.id

Il y a, c'est sûr , une façon plus fonctionnelle de faire cela en php mais cela devrait fonctionner ...

3 commentaires

Alors suggérez-vous que je devrais joindre les deux tables dont toutes les lignes de la colonne fabricant sont remplies, même si je n'en ai que deux? Est-ce considéré comme une bonne pratique?

Eh bien, cela dépend de la quantité de données dont vous disposez. Vous pouvez également avoir deux requêtes distinctes et remplir la même structure.

Comme pratique, vous pourriez dire, laissez la base de données gérer tout, car elle est faite pour des choses comme ça. Si cela coûte trop cher, vous pouvez remplir de fausses valeurs dans une base de données de test et exécuter un benchmark.

5
votes

Si vous utilisez MySQL 5.7 ou supérieur, vous pouvez utiliser les fonctions d'agrégation JSON pour générer la sortie que vous attendez directement d'une requête SQL. Je pense que ce sera plus efficace que d'utiliser php entre les deux.

Pour commencer, considérez la requête d'agrégats suivante, que JOIN s les deux tables et crée un objet JSON pour chaque fabricant, avec la liste des noms de voitures associés dans un sous-tableau:

| myjson                                                                                                                                           |
| ------------------------------------------------------------------------------------------------------------------------------------------------ |
| {"manufacturers": {"Daimler AG": {"cars": ["C Class", "E Class"], "founded in": 1927}, "Volkswagen AG": {"cars": ["Golf"], "founded in": 1937}}} |

Ceci renvoie:

SELECT JSON_OBJECT('manufacturers', JSON_OBJECTAGG(manufacturer, js)) myjson
FROM (
    SELECT 
        m.id, 
        m.manufacturer, 
        JSON_OBJECT('founded in', m.founded_in, 'cars', JSON_ARRAYAGG(c.car)) js
    FROM manufacturers m
    INNER JOIN cars c ON c.manufacturer = m.id
    GROUP BY m.id, m.manufacturer, m.founded_in
) x

Pour produire la sortie attendue, nous pouvons tourner ceci à une sous-requête et ajouter un autre niveau d'agrégation:

| js                                                   |
| ---------------------------------------------------- |
| {"cars": ["C Class", "E Class"], "founded in": 1927} |
| {"cars": ["Golf"], "founded in": 1937}               |

Cela donne:

SELECT JSON_OBJECT('founded in', m.founded_in, 'cars', JSON_ARRAYAGG(c.car)) js
FROM manufacturers m
INNER JOIN cars c ON c.manufacturer = m.id
GROUP BY m.id, m.founded_in;

Démo sur DB Fiddle .

0 commentaires

2
votes

Vous pouvez également utiliser un mappeur relationnel d'objet tel que Respect / Relational

Vous seriez capable d'appeler simplement:

use Respect\Relational\Mapper;

$mapper  = new Mapper(new PDO($db_string, $db_user, $db_pass));
$results = $mapper->manufacturers->cars->fetchAll();
$json    = json_encode($results);

Laissez le gros du travail à l'API.

0 commentaires