Databases and expressions

Expressions

phplyr’s helpers build inspectable expressions whenever their arguments are column names, literals, or other helpers. col('x') refers to a column, and methods combine expressions with operators:

filter(col('price')->mul(1.1)->gt(100), col('brand')->isIn(['a', 'b']))
mutate(margin: col('price')->sub(col('cost'))->div(col('price')))
mutate(dev: col('score')->sub(mean('score')))      // mean() recycles per group

The operator methods are add, sub, mul, div, mod, pow, neg, eq, ne, lt, le, gt, ge, and, or, not, isNa, isIn, between, and desc. Scalar helpers include round, abs, sqrt, exp, log, log10, floor, ceiling, is_na, as_int, as_float, as_string, as_bool, paste, and paste0. Several of these names shadow PHP built-ins in files that import them. The string and date helpers are expressions too.

An expression is a Phibble\Expr tree. phplyr passes it to the table. The in-memory DataFrame evaluates it with dplyr’s semantics, and a database table compiles it to SQL. A helper given a PHP closure still works, but it is evaluated in PHP.

mutate(_keep: 'used') and _keep: 'unused' need expression arguments, because phplyr reads the columns an expression uses from the tree.

Database tables

Phibble\tbl($pdo, 'table') returns a lazy table for a PDO connection. phplyr functions with expression arguments add to a SQL query instead of loading rows. show_query() returns the SQL, and collect() runs it and returns a DataFrame. SQLite, PostgreSQL, and MySQL are supported.

// Load the schools data into an in-memory SQLite database.
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE schools (school TEXT, study_hours REAL, prior_score REAL, score REAL)');
$insert = $pdo->prepare('INSERT INTO schools VALUES (?, ?, ?, ?)');
foreach (DataFrame::fromCsv('schools.csv')->toRows() as $row) {
    $insert->execute(array_values($row));
}

// The same functions build a query. Nothing runs until collect().
$query = tbl($pdo, 'schools') |>
    filter(col('study_hours')->ge(5)) |>
    group_by('school') |>
    summarize(students: n(), avg_score: mean('score')) |>
    filter(col('students')->ge(12)) |>
    arrange(desc('avg_score'));

echo $query |> show_query(), "\n\n";
echo $query |> collect();

// Load the schools data into an in-memory SQLite database.
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE schools (school TEXT, study_hours REAL, prior_score REAL, score REAL)');
$insert = $pdo->prepare('INSERT INTO schools VALUES (?, ?, ?, ?)');
foreach (DataFrame::fromCsv('schools.csv')->toRows() as $row) {
    $insert->execute(array_values($row));
}

// The same functions build a query. Nothing runs until collect().
$query = phplyr(tbl($pdo, 'schools'))
    ->filter(col('study_hours')->ge(5))
    ->groupBy('school')
    ->summarize(students: n(), avg_score: mean('score'))
    ->filter(col('students')->ge(12))
    ->arrange(desc('avg_score'))
    ->get();

echo phplyr($query)->showQuery(), "\n\n";
echo phplyr($query)->collect();

SELECT "school", "students", "avg_score" FROM (SELECT * FROM (SELECT "school", "students", "avg_score", ROW_NUMBER() OVER (ORDER BY ("school" IS NULL), "school") AS "__phibble_row" FROM (SELECT "school" AS "school", COUNT(*) AS "students", (CASE WHEN COUNT(*) > COUNT("score") THEN NULL ELSE (CASE WHEN COUNT("score") = 0 THEN NULL ELSE AVG(CAST("score" AS REAL)) END) END) AS "avg_score" FROM (SELECT * FROM (SELECT *, rowid AS "__phibble_row" FROM "schools") AS q1 WHERE ("study_hours" >= ?)) AS q2 GROUP BY "school") AS q3) AS q4 WHERE ("students" >= ?)) AS q5 ORDER BY ("avg_score" IS NULL), "avg_score" DESC, ("__phibble_row" IS NULL), "__phibble_row"

# A tibble: 9 × 3
  school    students avg_score
  <chr>        <int>     <dbl>
1 school-07       14      86.9
2 school-06       20      80.9
3 school-02       12      79.7
4 school-11       13      77.6
5 school-10       13      77.5
6 school-01       12      76.6
7 school-05       13      75.8
8 school-04       20      71.1
9 school-08       12      67.5

Grouped mutate() and filter() compile aggregates and window functions to OVER (PARTITION BY ...). Window functions need an order, so call arrange() first or pass orderBy:.

A function given a PHP closure runs the query so far, loads the rows, and continues in memory, returning a DataFrame. Expressions with no faithful SQL translation do the same. They include round() (R rounds halves to even), first(), last(), nth(), iqr(), cumprod(), consecutive_id(), and median() or quantile() outside PostgreSQL.