Published by cce on 2012-03-23
HTSQL now supports nested queries! Generation of multi-layered output is now trivial.
Often you want to organize summary and detailed data in a single hierarchical structure. In HTSQL, the segment (/) operator embeds the result of a correlated query as a nested list. For instance, a list of school records could include associated department records:
code | name | campus | department | ||
---|---|---|---|---|---|
code | name | school_code | |||
art | School of Art & Design | old | stdart | Studio Art | art |
bus | School of Business | south | acc | Accounting | bus |
econ | Economics | bus | |||
mm | Management & Marketing | bus | |||
edu | College of Education | old | edpol | Educational Policy | edu |
tched | Teacher Education | edu | |||
This mechanism is not a special case, it is an integral part of our query language. For example, it works with projections.
campus | school |
---|---|
name | |
north | School of Engineering |
old | School of Art & Design |
College of Education | |
School of Arts and Humanities | |
School of Natural Sciences | |
south | School of Business |
School of Music & Dance |
Nested segments play well with aggregates and variables.
name | $avg_credits | course | |
---|---|---|---|
credits | title | ||
Accounting | 3.5 | 5 | Financial Accounting |
5 | Audit | ||
6 | Accounting Internship | ||
Art History | 3.5 | 4 | History of Art Criticism I |
6 | Antique Art: Greece and Rome | ||
4 | Antique Art: The Middle East | ||
5 | Islamic Art | ||
5 | Art of Photography | ||
4 | Museum and Gallery Management | ||
Nesting can be arbitrarily deep.
name | department | |
---|---|---|
name | course | |
title | ||
College of Education | Educational Policy | Introduction to Education |
Contemporary Society | ||
Sociology of Childhood | ||
Technology in the Classroom | ||
Technology, Society and Schools | ||
Economics and Education Policy | ||
Politics and Education Policy | ||
Education Policy Analysis | ||
Children’s Literature | ||
Education Policy and Practice | ||
Social Analysis of Education Policy | ||
Classroom Visit | ||
Organizational Analysis of Education Policy | ||
Seminar in Education Policy I | ||
Seminar in Education Policy II | ||
Qualitative Research in Education Policy | ||
Teacher Education | Teaching Methodology | |
Theory and Practice of Early Childhood Education | ||
Methods of Early Science Education | ||
Play as Education Method | ||
Developmental Psychology | ||
Selection of Learning Resources | ||
Teacher Identity | ||
Problems in Education Management | ||
Challenges of Teaching the Gifted and Talented | ||
Techniques of Mathematics Teaching | ||
Techniques of Science Teaching | ||
Techniques of Language Teaching | ||
Problems in Education | ||
Public School Internship | ||
Preschool Internship | ||
Special Topics in Teacher Education | ||
Practice of Mathematics Teaching | ||
Practice of Science Teaching | ||
Practice of Language Teaching | ||
A query may have adjacent nested segments.
name | department | program |
---|---|---|
name | title | |
School of Art & Design | Studio Art | Post Baccalaureate in Art History |
Bachelor of Arts in Art History | ||
Bachelor of Arts in Studio Art | ||
School of Business | Accounting | Master of Arts in Economics |
Economics | Graduate Certificate in Accounting | |
Management & Marketing | Certificate in Business Administration | |
B.S. in Accounting | ||
Bachelor of Business Administration | ||
Bachelor of Arts in Economics | ||
For tabular formats, we show parallel tables adjacent to each other. In the query above, programs and departments are shown side-by-side even though they are not correlated with each other.
Nested segments are supported by all HTSQL output formats. The hierarchical structure of the output is very suitable for XML and JSON formats.
<?xml version="1.0" encoding="UTF-8" ?>
<htsql:result xmlns:htsql="http://htsql.org/2010/xml">
<school>
<code>art</code>
<name>School of Art & Design</name>
<campus>old</campus>
<department>
<code>stdart</code>
<name>Studio Art</name>
<school_code>art</school_code>
</department>
</school>
<school>
<code>bus</code>
<name>School of Business</name>
<campus>south</campus>
<department>
<code>acc</code>
<name>Accounting</name>
<school_code>bus</school_code>
</department>
<department>
<code>econ</code>
<name>Economics</name>
<school_code>bus</school_code>
</department>
<department>
<code>mm</code>
<name>Management & Marketing</name>
<school_code>bus</school_code>
</department>
</school>
…
{
"school": [
{
"code": "art",
"name": "School of Art & Design",
"campus": "old",
"department": [
{
"code": "stdart",
"name": "Studio Art",
"school_code": "art"
}
]
},
{
"code": "bus",
"name": "School of Business",
"campus": "south",
"department": [
{
"code": "acc",
"name": "Accounting",
"school_code": "bus"
},
{
"code": "econ",
"name": "Economics",
"school_code": "bus"
},
{
"code": "mm",
"name": "Management & Marketing",
"school_code": "bus"
}
]
},
…