RUEN
Package · mui-data-grid

Joined columns

In almost any real listing some columns come from elsewhere: an author’s name, a category title, a counter from a related entity. All of them are ordinary schema columns — you just map them to the right SQL expression.

You declare the join, in the base query. In the schema the column points at the joined table’s alias:

php
$repo = ArticleRepository::instance('a')
  ->select('a.id, a.title, au.name author_name')
  ->joinLeft(AuthorRepository::instance('au'), 'au.id = a.author_id');

$schema = GridSchema::make(
  GridColumn::for('title',      'a.title')->filterable(FilterType::String)->sortable(),
  GridColumn::for('authorName', 'au.name')->filterable(FilterType::String)->sortable(),
)->defaultOrder('a.created_at DESC, a.id DESC');

The user filters by authorName, the SQL uses au.name. No special support for JOINs is needed: the library inserts the column’s expression into WHERE and ORDER BY, and where it came from is none of its business.

The alias must exist in the query

A column’s expression is inserted verbatim. If the schema says au.name but the base query declares no join aliased au, the database returns an unknown-alias error. The schema and the base query are two halves of one contract — keep them together.

“Is empty” on a foreign key

With a LEFT JOIN, a missing relation leaves NULL in the joined column, so isEmpty on au.name technically works. Testing the foreign key is cheaper and more honest, though: it’s an indexed column of the main table, and its value doesn’t depend on how the join is written.

filterUsing() lets you override only the operators you care about, leaving the rest on autopilot:

php
use Flytachi\Winter\Cdo\Qb;
use Flytachi\Winter\MuiDataGrid\Entity\MGFilterItem;
use Flytachi\Winter\MuiDataGrid\Entity\MGOperator;

GridColumn::for('authorName', 'au.name')
  ->filterable(FilterType::String)
  ->sortable()
  ->filterUsing(fn (MGFilterItem $i): ?Qb => match ($i->operator) {
      MGOperator::IS_EMPTY     => Qb::isNull('a.author_id'),
      MGOperator::IS_NOT_EMPTY => Qb::isNotNull('a.author_id'),
      default                  => null,   // contains / equals / … — as usual, on au.name
  });

Returning null means “nothing special, carry on”. That way you write only the exceptions rather than restating the whole operator set. A full walkthrough is in Custom filters.

A computed expression

A column need not correspond to a single database column. Any expression valid in WHERE and ORDER BY will do:

php
GridColumn::for('fullName', "au.first_name || ' ' || au.last_name")
  ->filterable(FilterType::String)
  ->sortable();

GridColumn::for('displayName', 'coalesce(au.nick, au.name)')
  ->filterable(FilterType::String)
  ->sortable();

A contains filter on fullName matches the concatenated string — exactly as the user sees it in the table. That is often the point: search what is displayed, not how it is stored.

The expression is your code

A column’s SQL expression is inserted into the query without escaping: it must come from source, never from request data. Filter values always travel as bound parameters — those you neither need nor should assemble by hand.

Sorting separately from filtering

sortable() takes an optional expression, so a column can filter on one thing and sort on another.

php
// search the raw text, order case-insensitively
GridColumn::for('title', 'a.title')
  ->filterable(FilterType::String)
  ->sortable('lower(a.title)');

// show the full name, sort by surname
GridColumn::for('fullName', "au.first_name || ' ' || au.last_name")
  ->filterable(FilterType::String)
  ->sortable('au.last_name, au.first_name');

The second example is worth remembering: a list of people ordered by a concatenated string looks random to a user who expects alphabetical order by surname.

A comment count, an order total, a latest date — all ordinary columns, provided the aggregate is already computed in the base query. A subquery in the SELECT is the simplest route:

php
$repo = ArticleRepository::instance('a')
  ->select("a.id, a.title,
            (SELECT count(*) FROM comments c WHERE c.article_id = a.id) comment_count");

$schema = GridSchema::make(
  GridColumn::for('title', 'a.title')->filterable(FilterType::String)->sortable(),
  GridColumn::for('commentCount', '(SELECT count(*) FROM comments c WHERE c.article_id = a.id)')
      ->filterable(FilterType::Number)
      ->sortable(),
);

The schema repeats the subquery rather than referencing the comment_count alias: SELECT aliases are not available in WHERE — that is a rule of SQL, not of this library. ORDER BY would accept the alias, but keeping one expression in both roles is easier to maintain.

When a subquery is expensive

A correlated subquery is evaluated per row — noticeable on a large table, and the pagination COUNT query will run it as well. If filtering by the counter isn’t needed, declare the column sortable() only; better still, pre-aggregate with GROUP BY or a materialized field.

Filtering across a many-to-many relation

Selecting by tags, roles or another relation through a junction table is expressed with EXISTS. Such a filter is usually not a table column but a domain filter — see Filters outside the grid.

Page stability

The more columns come from a JOIN, the likelier it is that many rows share the same sort values. A unique tiebreaker in the order is mandatory:

php
->defaultOrder('a.created_at DESC, a.id DESC')

Without it, rows sharing a timestamp can swap places between two adjacent page requests, and the user sees one article twice and another not at all.

Next steps