Wonderful Relations Logo

Get Wonderful Relations

Subqueries

Function: SUBQUERIES

Subqueries in Wonderful Relations are a key mechanism for enabling nested or related data structures, especially in DataTables with subtables. They allow a query to include and render related datasets directly alongside the main query result, using a special GROUP_CONCAT strategy.

This makes subqueries essential for features like expandable rows in tables, nested result sets, and parent-child relationships.

Purpose

Subqueries are primarily used when a DataTable includes subtables (i.e., expandable children per row). These subtables require pre-processed data that can be grouped, joined, and searched efficiently.

In SQL, this is typically done via joins and GROUP_CONCAT. Wonderful Relations automates this logic using query definitions and subquery metadata.

Parameters

Subquery

The actual query that should be included as a subtable result.

Temporary Table Approach

Whether a temporary table should be used internally to hold subquery results.

Defines whether the subquery should be automatically linked to the main query using defined entity relationships.

Condition

An optional WHERE condition for the subquery, applied dynamically at render time.

Special Syntax

Subqueries support dynamic table references using the placeholder:

##RESULT_TABLE_NAME##

This will be replaced with the corresponding table alias during query generation.

Example Use Case

Imagine a query that returns a list of projects, and for each project, you want to show the list of assigned team members in a subtable.

The subquery fetches these team members and is embedded in the main query using GROUP_CONCAT:

SELECT
  projects.title,
  GROUP_CONCAT(
    CONCAT(
      'name::', team_member.name, '||',
      'role::', team_member.role
    )
    SEPARATOR '&&'
  ) AS team
FROM projects
LEFT JOIN team_member ON team_member.project_id = projects.id
GROUP BY projects.id

This logic is auto-generated by Wonderful Relations.

Visual Example in Wonderful Relations

Subtables are used extensively in Wonderful Relations itself. A simple example: Groups – each group row displays the linked WordPress users as a subtable. This means every group has a nested subtable containing multiple user rows.

If Subtable Search is enabled in a query, the search function will also include all searchable fields of the subqueries.

When a match is found in a subtable (e.g., a team member’s name), the parent row (e.g., the project) will be displayed—along with the entire subtable, not just the matching subentry.

Example:

In a project list with subtables showing team members, a search for a specific name will show the corresponding project row and the full list of team members assigned to it.

Summary

✅ Enables nested subtables inside DataTables
✅ Based on reusable query definitions
✅ Supports linking, filtering, and sorting of subquery data
✅ Optimized via SQL GROUP_CONCAT logic and dynamic aliasing

Subqueries are a cornerstone feature for rich data interfaces in Wonderful Relations, making it possible to build complex, yet performant nested table views without manually writing SQL logic.

Graph View