AQLearn 1.6 / Concept

Relationships

Source lesson: AQLearn 1.6: Relationships. Tangible win: query across multiple models without writing any SQL joins.

Purpose of this lesson

Have you ever tried to answer "How many users do we have in each country, and which cities drive the totals"?

You hit these pain points:

The fields live in three different models (users, cities, countries), so you must remember every join condition and join direction before you can even start counting.
Changing the breakdown from country to city, or adding category later, forces you to rewrite SELECT, JOIN, and GROUP BY together. One missed update silently changes the totals.
Multiple analysts model the same joins slightly differently, producing competing answers to the same question.

This lesson solves it: declare relationships once in the dataset, then write queries with just dimensions and measures. AQL traverses the relationship graph and generates the correct SQL JOIN and GROUP BY for you.

Why this matters for your mission: presales demos and customer dashboards almost always span several tables. If you can speak about relationships clearly, you can explain why the same metric returns different numbers in different reports.

Core idea

A dataset stores relationships between models, written like cities.country_code > countries.code. The > points from the many side to the one side. Once defined, any AQL query that mentions fields from related models is automatically joined for you.

// In the dataset definition (already set up in AQLearn)
relationship(cities.country_code > countries.code, true)
relationship(users.city_id > cities.id, true)
relationship(orders.user_id > users.id, true)
relationship(order_items.order_id > orders.id, true)
relationship(order_items.product_id > products.id, true)

Dataset diagram

Every arrow below is a many-to-one relationship. AQL walks these arrows from the many side toward the one side to gather dimensions.

Highlighted chain: users.city_id > cities.id then cities.country_code > countries.code. This is the multi-hop path the lesson queries traverse.

primary key foreign key

Worked examples

List cities and their country

explore {
  dimensions {
    countries.name,
    cities.name,
  }
}

No measure, no filter. AQL walks cities.country_code > countries.code and returns each city with its country.

Join path

Auto-generated SQL (simplified):

select
  countries.name
  , cities.name
from cities
left join countries on cities.country_code = countries.code
group by countries.name, cities.name

AQL starts from the model on the many side (cities), then joins each one-side model via its declared FK to PK.

Count users per country and city

explore {
  measures {
    count_users: users | count(users.id),
  }
  dimensions {
    countries.name,
    cities.name,
  }
}

Two hops at once: users > cities > countries. AQL emits the right JOINs and GROUP BY.

Join path

Auto-generated SQL (simplified):

select
  countries.name
  , cities.name
  , count(users.id) as count_users
from users
left join cities on users.city_id = cities.id
left join countries on cities.country_code = countries.code
group by countries.name, cities.name

Each left join follows one declared > relationship. AQL chains them automatically so you do not have to write or remember the keys.

Roll up to country level

explore {
  measures {
    count_users: users | count(users.id),
  }
  dimensions {
    countries.name,
  }
}

Remove the cities.name dimension and the count automatically rolls up. Australia goes from "89 + 103" across two cities to a single total "192".

Join path (cities used as transit, not in result)

Auto-generated SQL (simplified):

select
  countries.name
  , count(users.id) as count_users
from users
left join cities on users.city_id = cities.id
left join countries on cities.country_code = countries.code
group by countries.name

The join still goes through cities because that is the only path from users to countries. cities is traversed but does not appear in the select or group by; AQL groups by countries.name only and the count automatically rolls up.

What "many-to-one" means here

Many cities can belong to one country.
A city must not belong to more than one country.
The > in the relationship points from the many side (cities) to the one side (countries).
AQL uses this direction to know which side to join and to avoid fanouts.

Customer-safe explanation: "Once relationships are defined in the dataset, AQL writes the joins. You only describe what you want to see, not how to fetch it."

Retrieval practice

Do in AQLearn

Open AQLearn Relationships.
Run the starter query and confirm you get one row per (country, city).
Add count_users: users | count(users.id), in measures. Run.
Open the Generated SQL tab and read the JOIN and GROUP BY that AQL emitted.
Remove cities.name from dimensions and re-run. Confirm the rollup totals (e.g. Australia 192).

Next local lesson

Next: practice 1. Combine aggregate functions, dimensions, filters, and relationships in small problems on the AQLearn dataset.