---
title: Fix Data View Year Parameter Propagation
type: task
status: completed
description: >-
  Fix rate type CTEs to use query-level year range instead of column-level params, and parameterize
  get-players.mjs year for historical career_year queries. Resolves mismatched year filters and
  rookie-only-current-year bugs.
base_uri: user:task/league/fix-data-view-year-parameter-propagation.md
created_at: '2026-03-06T01:04:24.579Z'
entity_id: b8b71475-9251-42f4-9bde-199e6dcf4f76
estimated_total_duration: 10
observations:
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: High
public_read: true
relations:
  - >-
    relates
    [[user:task/github/mistakia/league/260-tracking-issue-for-various-data-view-errors-and-bugs.md]]
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-06-08T17:03:02.277Z'
visibility_analyzed_at: '2026-11-05T23:49:21.324Z'
---

# Implementation Plan: Fix Year Parameter Propagation in Data View CTEs

## Overview

Fix two year parameter propagation bugs in the data view query builder:

- Rate type CTEs use year filters from individual column params instead of the query-level year range, causing NULL game counts for years outside the column's own params
- `get-players.mjs` hardcodes `current_season.year` in seasonlog joins, preventing career_year filters from working for historical data

**Expected outcomes:**

- Rate type CTEs filter to the full year range when year/week splits are active
- Per-game rate calculations return correct values across multi-year queries
- `get-players.mjs` accepts an optional year parameter for historical queries
- All existing queries continue to work unchanged

## Background

### Rate Type CTE Year Mismatch

**Current flow in `get-data-view-results.mjs`:**

- Line 1243-1250: When splits include `year` or `week`, `get_year_range()` (line 323-360) computes a unified year array from ALL column params and where clauses. This becomes the `base_years` CTE.
- Line 1375-1440: Rate type columns create CTEs via `add_rate_type_cte()`. Each CTE's year filter comes from the individual column's `params.year` only.

**The mismatch scenario:** A data view with year split where Column A contributes years 2021-2023 but Column B's rate type CTE only covers year 2023. The `player_years` CTE generates rows for all three years, but the rate type CTE has game counts only for 2023, producing NULLs for 2021-2022.

**Rate type handler year filtering patterns (two variants):**

- `rate-type-per-game.mjs`: Computes `all_years` via local `get_default_params()`, applies directly with `cte_query.whereIn('nfl_games.year', all_years)` (line 115). Does NOT use `get_rate_type_denominator_params`.
- `rate-type-per-player.mjs`, `rate-type-per-player-play.mjs`, `rate-type-per-player-route.mjs`, `rate-type-per-team-play.mjs`: Call `get_rate_type_denominator_params({ params })` to extract time-scope params (year, week, year_offset, seas_type), then pass through `apply_play_by_play_column_params_to_query()` which handles year_offset expansion internally.

**Post-plan code changes (2026-03-11 to 2026-03-13):**

- `44f6157c`: Added `get_rate_type_denominator_params()` utility and `rate_type_match_column_params` toggle. Changed how 4 of 5 rate type handlers construct denominator params.
- `2305bd6b`: Removed `year_offset` expansion from `get_year_range()`. `base_years` CTE no longer includes offset-expanded years.
- `fdfa815b`: Prettier formatting pass.

### Hardcoded Year in get-players.mjs

Lines 290 and 314 use `current_season.year` for `league_format_player_seasonlogs` and `scoring_format_player_seasonlogs` joins. `career_year` filters only find players whose career_year matches in the current season, not historical rookies. File has 14 total `current_season.year` references; only the two seasonlog joins need parameterization.

## Design

### Rate Type Fix

Store the already-computed `year_range` on `data_view_options` and pass it to rate type CTE creation functions. Handlers use it as effective years when available, falling back to column-level years when splits are not active.

In `get-data-view-results.mjs`:

- Add `year_range: []` to `data_view_options` initialization (line 1146)
- Store computed year_range at line 1244: `data_view_options.year_range = year_range`
- Add `data_view_options` to args for `get_rate_type_cte_table_name()` (line 1400) and `add_rate_type_cte()` (line 1421). `join_rate_type_cte()` already receives it (line 1438).

In rate type handlers, compute effective years:

```
const effective_years = (data_view_options.year_range?.length)
  ? [...new Set([...data_view_options.year_range, ...adjusted_years])].sort((a, b) => a - b)
  : all_years
```

This preserves year_offset expansion (the `adjusted_years` from offset calculation) while using the query-level base years instead of column-level base years. When `data_view_options.year_range` is empty (no splits), falls back to column-level `all_years`.

For the 4 handlers using `get_rate_type_denominator_params` + `apply_play_by_play_column_params_to_query`: override `denominator_params.year` with `data_view_options.year_range` and delete `denominator_params.year_offset` (to prevent double-expansion in `apply_play_by_play_column_params_to_query`) when year_range is available.

CTE table name hash functions also accept `data_view_options` and apply the same effective_years logic for hash consistency.

### get-players.mjs Fix

Add `year = current_season.year` to function parameters. Replace `current_season.year` at lines 290 and 314 with `year`. Leave all other references unchanged.

## Notes

### Edge Cases

- **No splits active:** `data_view_options.year_range` stays empty, handlers use column-level `all_years`. Current behavior preserved.
- **Rate type column with no year params + year splits:** Currently `all_years` is `[]` and no year filter is applied, scanning all years. With fix, `data_view_options.year_range` provides correct scope from `get_year_range()`. Strictly better.
- **Year offset with year splits:** `get_year_range()` no longer expands offsets (per `2305bd6b`), but adjusted_years from the handler's local offset calculation are merged with the query-level range, producing the correct expanded set.
- **Week split strips per_game rate type:** At line 1316-1329, per_game rate type params are removed when week splits are active, before rate type CTEs are created. No interaction with this fix.
- **Rate type in WHERE clause:** The loop at lines 1375-1414 iterates over `[...prefix_columns, ...columns, ...where]`, so WHERE clause rate types are covered by the same fix.
- **`get_year_range` fallback (no year params anywhere):** Returns `[2000...current_year]`. Using this as effective_years replaces "no year filter" with an explicit broad range. Not worse than current behavior.

### Risk

- **Rate type fix (medium):** Changes CTE construction for all data views with rate types. Year ranges become strictly more correct. CTE table name hashes change, causing one-time cache misses.
- **get-players.mjs fix (low):** Backward compatible via default parameter.

### Career Year Limitation

The get-players.mjs fix enables per-year queries but does not solve "show all rookies from any year" in a single query. That would require removing the year constraint from seasonlog joins entirely -- a potential follow-up.

### Index Dispatcher

`rate-type/index.mjs` passes args through via spread (`handler[█████████](████)`), so `data_view_options` flows through without changes to the dispatcher.

## Plan

### Core Implementation -- Year Range Propagation

- [x] **Store year_range on data_view_options and pass to rate type functions**
  - File: `libs-server/get-data-view-results.mjs`
  - Add `year_range: []` to `data_view_options` initialization (line 1146)
  - Store `year_range` at line 1244 where it is already computed for `base_years`
  - Add `data_view_options` to args for `get_rate_type_cte_table_name()` (line 1400) and `add_rate_type_cte()` (line 1421)

- [x] **Update rate-type-per-game.mjs to use query year range**
  - File: `libs-server/data-views/rate-type/rate-type-per-game.mjs`
  - Accept `data_view_options = {}` in `get_per_game_cte_table_name`, `add_player_per_game_cte`, `add_team_per_game_cte`, and the `add_per_game_cte` wrapper
  - Compute `effective_years` from `data_view_options.year_range` merged with local `adjusted_years`, falling back to `all_years`
  - Use `effective_years` in `whereIn` (line 115, line 174) and in hash string computation (line 77)

- [x] **Update rate-type-per-player.mjs to use query year range**
  - File: `libs-server/data-views/rate-type/rate-type-per-player.mjs`
  - Accept `data_view_options = {}` in `get_per_player_cte_table_name` and `add_per_player_cte`
  - When `data_view_options.year_range` is non-empty, override `denominator_params.year` with year_range and delete `year_offset` before passing to `apply_play_by_play_column_params_to_query`
  - Apply same effective_years logic in hash function

- [x] **Update rate-type-per-player-play.mjs, rate-type-per-player-route.mjs, rate-type-per-team-play.mjs with same pattern**
  - Files: `libs-server/data-views/rate-type/rate-type-per-player-play.mjs`, `rate-type-per-player-route.mjs`, `rate-type-per-team-play.mjs`
  - Same changes as rate-type-per-player.mjs: accept `data_view_options`, override denominator year params, update hash functions

### Core Implementation -- get-players.mjs Year Parameter

- [x] **Add year parameter to getPlayers function**
  - File: `libs-server/get-players.mjs`
  - Add `year = current_season.year` to destructured parameters (line 16-26)
  - Replace `current_season.year` with `year` at line 290 (`league_format_player_seasonlogs` join) and line 314 (`scoring_format_player_seasonlogs` join)

### Testing

- [x] **Add test fixture for mismatched year params with rate type and year splits**
  - File: `test/data-view-queries/` (new JSON fixture)
  - Configure columns with different year params where one column contributes a wider year range than the rate type column
  - Verify rate type CTE WHERE clause includes the full query-level year range

- [x] **Update existing test fixtures affected by year range changes**
  - Review `single-year-offset-with-rate-type.json`, `year-split-with-multiple-rate-type-with-statements.json`, and `year-offset-range-with-rate-type-and-where-filter.json`
  - Update expected queries if CTE year ranges change due to the fix (they should not change when all columns share the same year params)

- [x] **Run regression tests**
  - Execute `yarn test --reporter min` to verify no existing tests break