Skip to content

v2.7.1

Choose a tag to compare

View all tags
@tommyknocker tommyknocker released this 29 Oct 15:49
· 390 commits to master since this release
v2.7.1
ca8a013

🚀 Release v2.7.1 - Major Query Builder Enhancements

Overview

v2.7.1 introduces five major query builder features with full cross-database support, comprehensive testing, and extensive documentation. This release significantly expands the library's capabilities for complex SQL queries and advanced analytics.

✨ New Features

📊 Window Functions Support

Advanced analytics with SQL window functions - perfect for rankings, leaderboards, and time-series analysis.

Ranking Functions:

  • Db::rowNumber() - Sequential row numbering
  • Db::rank() - Rank with gaps for ties
  • Db::denseRank() - Rank without gaps
  • Db::ntile() - Divide rows into buckets

Value Access Functions:

  • Db::lag() - Access previous row values
  • Db::lead() - Access next row values
  • Db::firstValue() - First value in window
  • Db::lastValue() - Last value in window
  • Db::nthValue() - Nth value in window

Window Aggregates:

  • Db::windowAggregate() - Running totals, moving averages

Features:

  • Support for PARTITION BY, ORDER BY, and frame clauses (ROWS BETWEEN)
  • Cross-database support (MySQL 8.0+, PostgreSQL 9.4+, SQLite 3.25+)
  • 11 helper methods in WindowHelpersTrait
  • Complete documentation (900+ lines)
  • 10 comprehensive use cases in examples

Documentation: documentation/03-query-builder/window-functions.md
Examples: examples/16-window-functions/

🔗 Common Table Expressions (CTEs)

WITH clauses for complex queries and recursive data processing.

Features:

  • QueryBuilder::with() - Basic CTEs
  • QueryBuilder::withRecursive() - Recursive CTEs for hierarchical data
  • Support for Closure, QueryBuilder, or raw SQL
  • Multiple CTEs with unique parameter scoping
  • Explicit column lists for recursive CTEs
  • Cross-database support (MySQL 8.0+, PostgreSQL 8.4+, SQLite 3.8.3+)

Use Cases:

  • Tree structures (categories, comments, file systems)
  • Organizational charts
  • Recursive calculations
  • Query organization and readability

Documentation: documentation/03-query-builder/cte.md
Examples: examples/17-cte/

🔀 Set Operations

UNION, INTERSECT, and EXCEPT operations for combining query results.

Features:

  • QueryBuilder::union() - Combine results (removes duplicates)
  • QueryBuilder::unionAll() - Combine results (keeps duplicates)
  • QueryBuilder::intersect() - Common rows in both queries
  • QueryBuilder::except() - Rows in first but not in second
  • Support for Closure and QueryBuilder instances
  • Proper ORDER BY/LIMIT/OFFSET placement (SQL standard compliance)
  • Cross-database: MySQL 8.0+, PostgreSQL, SQLite 3.8.3+

Documentation: documentation/03-query-builder/set-operations.md
Examples: examples/18-set-operations/

🔍 DISTINCT and DISTINCT ON

Remove duplicates from result sets with advanced control.

Features:

  • QueryBuilder::distinct() - Remove duplicates (all databases)
  • QueryBuilder::distinctOn() - PostgreSQL-specific distinct on columns
  • Runtime dialect validation with clear exceptions
  • DialectInterface::supportsDistinctOn() for feature detection

Documentation: documentation/03-query-builder/distinct.md
Examples: Extended examples/01-basic/05-ordering.php

🎯 FILTER Clause for Conditional Aggregates

SQL:2003 standard compliance with automatic dialect translation.

Features:

  • filter() method chainable after all aggregate functions
  • Db::count()->filter(...), Db::sum()->filter(...), etc.
  • Native FILTER (WHERE ...) clause for PostgreSQL and SQLite 3.30+
  • Automatic CASE WHEN fallback for MySQL
  • DialectInterface::supportsFilterClause() for feature detection

Example:

$db->find()
    ->from('orders')
    ->select([
        'total_orders' => Db::count('*'),
        'paid_orders' => Db::count('*')->filter('status', 'paid'),
        'pending_amount' => Db::sum('amount')->filter('status', 'pending'),
    ])
    ->groupBy('user_id')
    ->get();

Documentation: documentation/03-query-builder/filter-clause.md
Examples: Extended examples/02-intermediate/02-aggregations.php

🧪 Testing

20 new edge-case tests covering critical scenarios:

  • Empty result sets
  • NULL value handling
  • Boundary conditions
  • Unsupported feature detection

Total Coverage:

  • ✅ 574 tests with 2526 assertions
  • ✅ All tests passing on MySQL, PostgreSQL, SQLite
  • ✅ 108 examples (36 files × 3 dialects)

📚 Documentation

Added 6 new comprehensive documentation files (2,305+ lines):

  • Window Functions (923 lines)
  • CTEs (409 lines)
  • Set Operations (204 lines)
  • DISTINCT (320 lines)
  • FILTER Clause (349 lines)
  • Window Helpers Reference (550 lines)

🔧 Changes & Fixes

Changed

  • Aggregate helpers now return FilterValue instead of RawValue to support filter() chaining
  • SelectQueryBuilderInterface extended with setUnions(), setDistinct(), setDistinctOn()
  • Enhanced parameter management for UNION subqueries

Fixed

  • MySQL recursive CTE string concatenation: Fixed test failure using CONCAT() instead of || operator
  • All examples now work correctly on all three database dialects

📊 Statistics

  • 39 files changed
  • 6,507 insertions, 80 deletions
  • PHPStan Level 9 (upgraded from Level 8)
  • 100% backward compatible - no breaking changes

🔗 Links

📦 Installation

composer require tommyknocker/pdo-database-class:^2.7.1

Enjoy building powerful database queries! 🎉

Assets 2
Loading