Fix League Data View Query Issues

[mistakia]

Overview

This issue addresses two critical query generation problems in the league data views system that impact performance and data accuracy:

1. From-Table Optimization Missing Scoring Format Hash: Fantasy scoring columns can't be used as from-tables due to missing scoring format hash conditions
2. Duplicate Rows in Year Offset Queries: Queries with year_offset ranges and splits produce duplicate rows

Problem Details

1. From-Table Optimization Scoring Format Hash

Issue: The from-table optimization feature currently only supports player_fantasy_points_from_plays due to missing scoring format hash conditions. When scoring format columns like player_fantasy_points_from_seasonlogs are used as the from-table, the required scoring_format_hash join conditions are not properly applied to the base query.

Affected Columns:

• player_fantasy_points_from_seasonlogs
• player_fantasy_points_per_game_from_seasonlogs
• player_fantasy_points_rank_from_seasonlogs
• player_fantasy_points_position_rank_from_seasonlogs
• All scoring_format_player_seasonlogs based columns
• All scoring_format_player_careerlogs based columns
• All league_format_player_seasonlogs based columns

Root Cause: The scoring_format_player_seasonlogs_join function includes additional_conditions that sets the scoring format hash condition during joins. When these columns are used as the from-table, this join logic isn't applied to the base query setup.

2. Duplicate Rows in Year Offset Queries

Issue: Queries with year_offset ranges and splits are producing duplicate rows. When using year_offset: [1, 3] with splits: ["year"], the generated query returns multiple identical rows for the same player-year combination.

Affected Cases:

• Queries with year_offset ranges and splits
• Test case: year-offset-range-with-where-filters.json

Root Cause: The GROUP BY clause or JOIN conditions in year offset queries are not properly eliminating duplicates when aggregating across offset years.

Current State

• From-table optimization whitelist: Only player_fantasy_points_from_plays ✅
• All other columns fall back to default table setup
• Infrastructure exists but needs condition bridging

Technical Requirements

From-Table Optimization Fix

1. Apply additional_conditions logic when setting up from-table base queries
2. Ensure scoring_format_hash parameters flow through to from-table setup
3. Handle transition from join conditions to from-table base conditions
4. Maintain compatibility with existing join scenarios

Duplicate Rows Fix

1. Investigate GROUP BY clause in year offset queries
2. Review JOIN conditions for offset year aggregations
3. Ensure proper deduplication when multiple years are involved
4. Fix test case year-offset-range-with-where-filters.json

Related Files

• libs-server/get-data-view-results.mjs (from-table optimization logic)
• libs-server/data-views-column-definitions/player-scoring-format-logs-column-definitions.mjs (join functions)
• libs-server/apply-play-by-play-column-params-to-query.mjs (year offset logic)
• test/data-view-queries/year-offset-range-with-where-filters.json (duplicate rows test)

Success Criteria

• Fantasy points columns work correctly as from-tables with proper scoring format filtering
• Query performance improves for common fantasy points sorts
• No duplicate rows in year offset queries with splits
• All existing functionality remains intact
• Systematic testing validates each fix

Subtasks

Fix From-Table Scoring Format Hash

• Implement condition bridging for from-table scenarios
• Test with scoring format columns
• Expand whitelist systematically

Fix Duplicate Rows in Year Offset Queries

• Debug GROUP BY and JOIN logic
• Test with year-offset-range-with-where-filters.json
• Ensure correct aggregation across offset years

[mistakia]

Resolution

This issue tracked two problems:

1. Duplicate Rows in Year Offset Queries - Resolved

Investigation confirmed this issue is already fixed. All 109 data view tests pass, including the specific test case year-offset-range-with-where-filters.json that validates correct GROUP BY and JOIN logic for year offset ranges with splits.

2. From-Table Optimization Missing Scoring Format Hash - Implementation Planned

A comprehensive implementation plan has been created for a Unified Condition System that will:

• Abstract filtering conditions into a declarative format
• Enable conditions to work in both JOIN and FROM-table contexts
• Eliminate duplicated condition logic across 10+ column definition files
• Unlock from-table optimization for scoring format columns

The implementation plan covers 22 tasks including core utility implementation, query builder integration, and migration of all 10 affected column definition files.

Tracking: The remaining work is tracked separately in the task management system with a detailed implementation plan.

[mistakia]

Implementation Complete

The from-table optimization for scoring format columns has been implemented and merged.

Commit: 58145737 - feat: enhance from-table optimization and introduce new utility functions

Changes

• Added get_table_conditions() pattern for declarative condition definition
• Replaced manual whitelist with capability detection (can_use_as_from_table)
• Created param-utils.mjs for scoring/league format hash extraction
• Updated scoring format and league format column definitions

Performance Results

Query Type	Before	After	Speedup
Single year sort	~33ms	~22ms	1.5x
Multi-year with splits (8 years)	~540ms	~22ms	24.5x

Tests

• All 111 data view tests passing
• Added 3 new test cases for from-table optimization scenarios