Mastering Custom Sort and Search Logic in Beartropy Tables

One of the most powerful features of Beartropy Tables is the ability to display computed data using customData(). However, when the data shown to the user differs from the raw database column, standard sorting and searching strategies often fall short.

The Challenge

Imagine a Settings table where a value column stores raw data (like "1" or "opt_key"), but the UI displays formatted text (like "True" or "Dark Mode").

If you simply add ->sortable(), the database will sort by the raw ID, not the human-readable label. To fix this, we need to teach the query builder how to interpret this data.

The Solution: Closure Callbacks

Both sortable() and searchable() accept a closure, allowing you to inject raw SQL or complex logic directly into the query builder.

Here is a real-world example handling dynamic values stored in JSON:

 1Column::make('value')
 2    ->customData(function ($row) {
 3        // Display Logic: Show human-readable labels
 4        if ($row->type == "select") {
 5            return $row->options[$row->value] ?? $row->value;
 6        } elseif ($row->type == "boolean") {
 7            return $row->value ? 'True' : 'False';
 8        }
 9        return $row->value;
10    })
11    ->searchable(function ($query, $searchTerm) {
12        // Search Logic: Look inside the JSON structure
13        $query->orWhere(function ($q) use ($searchTerm) {
14            $q->where('value', 'like', "%{$searchTerm}%");
15 
16            // Database agnostic JSON extraction
17            if (\config('database.default') === 'sqlite') {
18                $q->orWhereRaw("json_extract(options, '$.' || \"value\") LIKE ?", ["%{$searchTerm}%"]);
19            } else {
20                $q->orWhereRaw("LOWER(JSON_UNQUOTE(JSON_EXTRACT(options, CONCAT('$.', `value`)))) LIKE ?", ["%" . strtolower($searchTerm) . "%"]);
21            }
22        });
23    })
24    ->sortable(function ($query, $direction) {
25        // Sort Logic: Order by the resolved label, not the raw key
26        if (\config('database.default') === 'sqlite') {
27            $sql = "COALESCE(json_extract(options, '$.' || \"value\"), value)";
28        } else {
29            $sql = "COALESCE(JSON_UNQUOTE(JSON_EXTRACT(options, CONCAT('$.', `value`))), `value`)";
30        }
31        $query->orderByRaw("$sql $direction");
32    });

Key Takeaways

1. Decouple Display from Query: customData handles what PHP renders, while the callbacks handle what SQL executes.
2. Raw SQL Power: The callbacks give you access to the underlying query builder instance, allowing for orWhereRaw, orderByRaw, and advanced JSON operations.
3. Cross-Database Compatibility: You can even write conditional logic (SQLite vs MySQL) inside the column definition to ensure your package runs everywhere.

This approach ensures that what the user sees is exactly what they are sorting and searching for.