phibble for Laravel developers

The example data

The examples on this page use Eloquent with an in-memory SQLite database that holds penguins.csv. The model has a measured() scope that keeps rows with a body mass:

use Illuminate\Database\Eloquent\Model;

final class Penguin extends Model
{
    protected $table = 'penguins';

    public $timestamps = false;

    public function scopeMeasured($query)
    {
        return $query->whereNotNull('body_mass');
    }
}

docs/examples/support/laravel.php creates the table, loads the CSV, and registers the macros outside a Laravel application, so the examples run as plain PHP scripts.

From a collection or query to a DataFrame

toDataFrame() builds a data frame from the rows of a collection or a query. Column types are inferred from the values, as for DataFrame::fromRows().

$df = Penguin::query()
    ->where('year', 2009)
    ->get()
    ->toDataFrame();

echo $df->select(['species', 'island', 'body_mass'])->head(3);

# A tibble: 3 × 3
  species island body_mass
  <chr>   <chr>      <int>
1 Adelie  Biscoe      3725
2 Adelie  Biscoe      4725
3 Adelie  Biscoe      3075

A collection of models becomes rows of their attributes. Eloquent casts and accessors apply only when the models are loaded first, as in the example above; Penguin::query()->toDataFrame() reads the rows directly.

Queries that stay in the database

phibble() turns a builder into a lazy table. The builder’s SQL, bindings, and scopes become the table’s source, and phibble’s verbs add to that query. Nothing runs until the rows are needed, for example with collect(), printing, or nrow().

$summary = Penguin::query()
    ->measured()
    ->where('year', '>=', 2008)
    ->phibble()
    ->groupBy(['species', 'sex'])
    ->summarize([
        'n' => n(),
        'mass' => mean(col('body_mass')),
    ]);

echo $summary->collect();

# A tibble: 7 × 4
# Groups:   species [3]
  species   sex        n  mass
  <chr>     <chr>  <int> <dbl>
1 Adelie    female    51 3360.
2 Adelie    male      51 4046.
3 Chinstrap female    21 3501.
4 Chinstrap male      21 4013.
5 Gentoo    female    42 4703.
6 Gentoo    male      44 5459.
7 Gentoo    <NA>       3 4750

Global scopes such as SoftDeletes apply, because phibble reads the query through Eloquent’s toBase(). Attribute casts and accessors do not apply, because the rows come from SQL.

showQuery() returns the SQL that collect() would run. In this example a grouped filter keeps the heaviest penguin on each island. The comparison with the group maximum compiles to a window function:

$heaviest = Penguin::query()
    ->measured()
    ->phibble()
    ->groupBy(['island'])
    ->filter(col('body_mass')->eq(max(col('body_mass'))))
    ->select(['island', 'species', 'body_mass']);

echo $heaviest->showQuery(), "\n\n";
echo $heaviest->arrange(['island'])->collect();

SELECT "island", "species", "body_mass" FROM (SELECT "island", "species", "body_mass", "__phibble_row" FROM (SELECT "id", "species", "island", "bill_len", "bill_dep", "flipper_len", "body_mass", "sex", "year", "__phibble_row" FROM (SELECT *, ("body_mass" = (CASE WHEN COUNT(*) OVER (PARTITION BY "island") > COUNT("body_mass") OVER (PARTITION BY "island") THEN NULL ELSE (CASE WHEN COUNT("body_mass") OVER (PARTITION BY "island") = 0 THEN -9e999 ELSE MAX("body_mass") OVER (PARTITION BY "island") END) END)) AS "__phibble_keep" FROM (SELECT *, ROW_NUMBER() OVER () AS "__phibble_row" FROM (select * from "penguins" where "body_mass" is not null) AS phibble_source) AS q1) AS q2 WHERE "__phibble_keep" = TRUE) AS q3) AS q4 ORDER BY ("__phibble_row" IS NULL), "__phibble_row"

# A tibble: 3 × 3
# Groups:   island [3]
  island    species   body_mass
  <chr>     <chr>         <int>
1 Biscoe    Gentoo         6300
2 Dream     Chinstrap      4800
3 Torgersen Adelie         4700

Expressions that SQL cannot evaluate exactly as R does, and any PHP closure, make the table collect its rows and continue in memory. The result is then a DataFrame. The SQL tables page lists these cases and the few results that differ from an in-memory run.

With phplyr

phplyr provides dplyr’s verbs as functions for PHP 8.5’s pipe operator and as a method chain on PHP 8.3, 8.4, and 8.5. Its verbs call the same table methods, so a phplyr pipeline on phibble() also runs in the database. phplyr’s own col() and aggregates take camelCase options such as naRm::

use function Phplyr\{arrange, col, desc, filter, group_by, mean, n, summarize};

$summary = Penguin::query()->phibble() |>
    filter(col('year')->ge(2008)) |>
    group_by('species', 'sex') |>
    summarize(n: n(), mass: mean(col('body_mass'), naRm: true)) |>
    arrange(desc('mass'));

use function Phplyr\{col, desc, mean, n, phplyr};

$summary = phplyr(Penguin::query()->phibble())
    ->filter(col('year')->ge(2008))
    ->groupBy('species', 'sex')
    ->summarize(n: n(), mass: mean(col('body_mass'), naRm: true))
    ->arrange(desc('mass'))
    ->get();

Charts with phiz

phiz is a grammar of graphics in the style of ggplot2 that produces Vega-Lite specifications. Plot::of() accepts a phibble DataFrame directly. A query can select the needed columns in the database, collect them, and plot them:

use Phiz\Plot;

$penguins = Penguin::query()
    ->measured()
    ->phibble()
    ->select(['species', 'flipper_len', 'body_mass'])
    ->collect();

$plot = Plot::of($penguins)
    ->aes(x: 'flipper_len', y: 'body_mass', color: 'species')
    ->point(alpha: 0.5)
    ->smooth(method: 'lm')
    ->labs(title: 'Body mass by flipper length', x: 'Flipper length (mm)', y: 'Body mass (g)');

For a summary, the grouping and aggregation can also run in the database, so only the summary rows reach PHP:

use Phiz\Plot;

// The grouping and the means run in the database.
$byYear = Penguin::query()
    ->measured()
    ->phibble()
    ->groupBy(['species', 'year'])
    ->summarize(['mass' => mean(col('body_mass'))])
    ->collect();

echo $byYear->head(3), "\n";

$plot = Plot::of($byYear)
    ->aes(x: 'year:O', y: 'mass', color: 'species')
    ->line()
    ->point()
    ->labs(title: 'Mean body mass by year', x: 'Year', y: 'Mean body mass (g)');

# A tibble: 3 × 3
# Groups:   species [1]
  species  year  mass
  <chr>   <int> <dbl>
1 Adelie   2007 3696.
2 Adelie   2008 3742 
3 Adelie   2009 3665.

In a Laravel view, phiz’s Blade component renders a plot, and @phizScripts loads the Vega libraries once per page:

<x-phiz :plot="$plot" />

@phizScripts

phiz also has helpers that plot phit model results, such as coefplot($fit) and effect_plot($fit, $data, 'x', by: 'g'). Its documentation covers them.

Using the result

A collected data frame can be returned from a controller or passed to a view:

• $df->toRows() gives a list of associative arrays, for JSON responses and Blade loops.
• $df->toJson() gives the same rows as a JSON string, or the columns with toJson('columns').
• $df->toCollection() returns a Laravel collection of rows.
• $df->toModelData() gives the columns in the form phit’s model functions accept.