Joins in SLayer¶

orders_model = next(m for m in models if m.name == "orders")

print("orders model joins:")
for j in orders_model.joins:
    print(f"  -> {j.target_model}  ON {j.join_pairs}")

# Query using a joined dimension
result = engine.execute_sync(
    query={
        "source_model": "orders",
        "measures": ["*:count", "order_total:sum"],
        "dimensions": ["customers.name"],
        "order": [{"column": "order_total_sum", "direction": "desc"}],
        "limit": 5,
    }
)

print("\nTop 5 customers by revenue:")
for row in result.data:
    print(f"  {row['orders.customers.name']}: {row['orders._count']} orders, ${row['orders.order_total_sum']:,.2f}")

# 1-hop: orders -> stores
result = engine.execute_sync(
    query={
        "source_model": "orders",
        "measures": ["*:count"],
        "dimensions": ["stores.name"],
    }
)

print("1-hop (orders -> stores):")
for row in result.data:
    print(f"  {row['orders.stores.name']}: {row['orders._count']} orders")

# Multi-hop: items -> orders -> customers
result = engine.execute_sync(
    query={
        "source_model": "items",
        "measures": ["*:count"],
        "dimensions": ["orders.customers.name"],
        "order": [{"column": "_count", "direction": "desc"}],
        "limit": 5,
    }
)

print("Multi-hop (items -> orders -> customers):")
for row in result.data:
    print(
        f"  {row['items.orders.customers.name']}: {row['items._count']} line items"
    )

# The __ alias convention is for model-level SQL definitions (YAML / Python).
# Auto-ingestion doesn't generate these — joined columns are resolved at query time.
#
# Here's a manual example showing the two syntaxes side by side:

from slayer.core.models import Column

manual_col = Column(
    name="customer_name",
    sql="orders__customers.name",  # __ alias: used in model SQL definitions
    type="string",
)
print(f"Model-level SQL (__ alias):  sql={manual_col.sql!r}")
print("Query-level (dot syntax):    orders.customers.name")

# At query time, use dot syntax — SLayer resolves the __ aliases in generated SQL:
result = engine.execute_sync(
    query={
        "source_model": "items",
        "measures": ["*:count"],
        "dimensions": ["orders.customers.name"],
        "limit": 3,
    }
)

print(f"\nResult keys: {list(result.data[0].keys())}")
for row in result.data:
    print(f"  {row['items.orders.customers.name']}: {row['items._count']} items")

import sqlalchemy as sa

from setup_jaffle import DB_PATH
from slayer.core.models import DatasourceConfig
from slayer.engine.ingestion import _build_fk_graph

ds = DatasourceConfig(name="jaffle_shop", type="duckdb", database=DB_PATH)
sa_engine = sa.create_engine(ds.resolve_env_vars().get_connection_string())
inspector = sa.inspect(sa_engine)
fk_graph = _build_fk_graph(inspector=inspector, table_names=inspector.get_table_names(), schema=None)
sa_engine.dispose()

print("FK graph:")
for table in sorted(fk_graph):
    print(f"  {table} -> {sorted(fk_graph[table])}")

items_model = next(m for m in models if m.name == "items")
orders_model = next(m for m in models if m.name == "orders")

print("\nitems joins (direct FKs only):")
for j in items_model.joins:
    src, tgt = j.join_pairs[0]
    print(f"  -> {j.target_model:<12} ON {src:<20} = {tgt}")

print("\norders joins (direct FKs only):")
for j in orders_model.joins:
    src, tgt = j.join_pairs[0]
    print(f"  -> {j.target_model:<12} ON {src:<20} = {tgt}")

print("\nMulti-hop: items reaches customers via orders.joins")

from joins_utils import setup_diamond_example

diamond_engine, diamond_storage, diamond_models, diamond_db_path, diamond_work_dir = setup_diamond_example()

diamond_orders = next(m for m in diamond_models if m.name == "orders")
diamond_customers = next(m for m in diamond_models if m.name == "customers")
diamond_warehouses = next(m for m in diamond_models if m.name == "warehouses")

print("orders joins (direct FKs only):")
for j in diamond_orders.joins:
    src, tgt = j.join_pairs[0]
    print(f"  -> {j.target_model:<12} ON {src:<25} = {tgt}")

print("\ncustomers joins:")
for j in diamond_customers.joins:
    src, tgt = j.join_pairs[0]
    print(f"  -> {j.target_model:<12} ON {src:<25} = {tgt}")

print("\nwarehouses joins:")
for j in diamond_warehouses.joins:
    src, tgt = j.join_pairs[0]
    print(f"  -> {j.target_model:<12} ON {src:<25} = {tgt}")

print("\nDiamond: orders reaches regions via customers.joins AND warehouses.joins")
print("Each path gets a unique alias: customers__regions vs warehouses__regions")

# Query both paths simultaneously
result = diamond_engine.execute_sync(
    query={
        "source_model": "orders",
        "measures": ["*:count", "amount:sum"],
        "dimensions": [
            "customers.regions.name",
            "warehouses.regions.name",
        ],
    }
)

print(f"{'Customer Region':<18} {'Warehouse Region':<18} {'Orders':>7} {'Amount':>10}")
print("-" * 56)
for row in result.data:
    cr = row["orders.customers.regions.name"]
    wr = row["orders.warehouses.regions.name"]
    print(f"{cr:<18} {wr:<18} {row['orders._count']:>7} ${row['orders.amount_sum']:>9,.2f}")

# Only keep orders where customer and warehouse are in the same region.
# The filter equates two SQL aliases, so it goes on the model via ModelExtension.
result = diamond_engine.execute_sync(
    query={
        "source_model": {
            "source_name": "orders",
            "filters": ["customers__regions.name = warehouses__regions.name"],
        },
        "measures": ["*:count", "amount:sum"],
        "dimensions": ["customers.regions.name"],
    }
)

print("Orders where customer and warehouse are in the same region:")
for row in result.data:
    print(f"  {row['orders.customers.regions.name']}: {row['orders._count']} orders, ${row['orders.amount_sum']:,.2f}")