Making tree query

Following is available without writing any code

Zazler learns also primary and foreign keys and they can be used to automatically make tree-like queries.

SQL:

CREATE TABLE person(id INTEGER PRIMARY KEY, name text);
CREATE TABLE skills(
     id     INTEGER PRIMARY KEY,
     person INTEGER NOT NULL,
     skill  text,
     FOREIGN KEY (person) REFERENCES person(id));
 
INSERT INTO person (id, name) VALUES (1, 'Johnny'), (2, 'Mary');
INSERT INTO skills (id, person, skill) VALUES
   (1, 1, 'programming'), (2, 1, 'skiing'),
   (3, 2, 'singing')    , (4, 2, 'dancing');

/db/person.tree.json?sub=skills

[
  {
    "id": 1,
    "name": "Johnny",
    "@skills": [
      {
        "person": 1,
        "skill": "programming"
      },
      {
        "person": 1,
        "skill": "skiing"
      }
    ]
  },
  {
    "id": 2,
    "name": "Mary",
    "@skills": [
      {
        "person": 2,
        "skill": "singing"
      },
      {
        "person": 2,
        "skill": "dancing"
      }
    ]
  }
]

Recursion and many references

To add many tables separate tables with comma in sub variable (sub=table1,table2). Tables can be referenced to root table (person in this case) or to sub-tables (skill in this example). As many tables from variable sub are used as possible.

To influence subqueries use variable namespaces referencing to table. Add skills:select=skill in this example if you want to avoid passing redundant field person in @skills. Notice that you still must query fields that are needed to make subquery.

For better example, add one more table refencing to skills:

CREATE TABLE skill_tags(
  skill INTEGER,
  tag   text,
  FOREIGN KEY (skill) REFERENCES skills(id));
INSERT INTO skill_tags(skill, tag) VALUES
  (1, 'brain'),
  (2, 'healthy'),
  (2, 'sport'),
  (3, 'arts'),
  (4, 'healthy');

person.tree.json?sub=skills,skill_tags&skill_tags:select=tag&skills:select=skill,id

[
  {
    "id": 1,
    "name": "Johnny",
    "@skills": [
      {
        "skill": "programming",
        "id": 1,
        "@skill_tags": [
          {
            "tag": "brain"
          }
        ]
      },
      {
        "skill": "skiing",
        "id": 2,
        "@skill_tags": [
          {
            "tag": "healthy"
          },
          {
            "tag": "sport"
          }
        ]
      }
    ]
  },
  {
    "id": 2,
    "name": "Mary",
    "@skills": [
      {
        "skill": "singing",
        "id": 3,
        "@skill_tags": [
          {
            "tag": "arts"
          }
        ]
      },
      {
        "skill": "dancing",
        "id": 4,
        "@skill_tags": [
          {
            "tag": "healthy"
          }
        ]
      }
    ]
  }
]

Store as alias

For longer tree queries you may want to put this into database configuration as alias. To specify this query under /db/persontree.tree.json add to database config this:

var zazler = require('zazler');
zazler.db("db", "mysql://host/db", { read: "person skills skill_tags",
  alias: [
     { name: "persontree",
       table: "person",
       vars: {
         "sub":               "skills,skill_tags",
         "skills:select":     "skill,id",  // don't remove "id" (used for skill_tags query)
         "skill_tags:select": "tag"
       }
  ]
})