Laravel 5.8 Backpack addColumn custom type

Question

We are taking over a site and before rebuilding it we need to fix a few issues using

• Laravel 5.8
• Backpack crud 3.6

The dashboard shows all users (id, name, email) from the MySql users table, which is straight forward using

$this->crud->setFromDb();

Another column is created to display the status (active/inactive) of the user in relation to subscriptions stored in a different table as the users table. This column uses the addColumn method with a custom column type like so:

$columnStat = [
                    'label' => "Status",
                    'type' => 'user_status',
                    'name' => 'id', // the db column for the foreign key
                    'key' => 'user_status', // this key helps to differentiate between column using same name
                    'entity' => 'subscriptions', // the method that defines the relationship in your Model
                    'attribute' => 'user_id', // foreign key attribute that is shown to user
                    'model' => "App\Models\UserSubscriptions" // foreign key model
            ];

$this->crud->addColumn($columnStat);

The custom type is defined in a blade file backpack/crud/columns/user_status.blade.php:

@php
    $isActive = false;
    $user_id = $entry->{$column['name']};
    if(isset($user_id) && is_int($user_id)) {
        $subscription = \App\Models\UserSubscription::where('end_date', '>' , Carbon\Carbon::today())
                         ->where('payment_status', '=' , 1)
                         ->where('user_id', '=' , $user_id)
                         ->get()->last();
        if($subscription){
            $isActive = true;
        }
    }

@endphp

{{-- regular object attribute --}}
@if($isActive)
<td>Active</td>
@else
<td>Inactive</td>
@endif

The MySql table for user_subcriptions is the following:

mysql> desc user_subscriptions;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int unsigned | NO   | PRI | NULL    | auto_increment |
| subscription      | text         | YES  |     | NULL    |                |
| start_date        | date         | YES  |     | NULL    |                |
| end_date          | date         | YES  |     | NULL    |                |
| user_id           | int unsigned | NO   | MUL | NULL    |                |
| payment_status    | tinyint(1)   | NO   |     | 0       |                |
| deleted_at        | timestamp    | YES  |     | NULL    |                |
| created_at        | timestamp    | YES  |     | NULL    |                |
| updated_at        | timestamp    | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+

This is all working fine (active and inactive are displayed correctly) except that the column can't be sorted in ascending or descending order.

The Backpackforlaravel docs show an example on how to improve the column to get a custom order: (search internet for: backpackforlaravel.com and custom-order-logic-for-columns)

$this->crud->addColumn([
    // Select
   'label'      => 'Category',
   'type'       => 'select',
   'name'       => 'category_id', // the db column for the foreign key
   'entity'     => 'category', // the method that defines the relationship in your Model
   'attribute'  => 'name', // foreign key attribute that is shown to user
   'orderable'  => true,
   'orderLogic' => function ($query, $column, $columnDirection) {
        return $query->leftJoin('categories', 'categories.id', '=', 'articles.select')
            ->orderBy('categories.name', $columnDirection)->select('articles.*');
    }
]);

I just don't understand how to implement this here in my case.

My questions:

• How can I modify the code to make the status column sortable in ascending or descending order?
• is there a way for me to output/log the query of the column so that I can understand how to modify it?

Any help would be highly appreciated.

Answer 1

To view the query behind the column and log it, view the detailed answer given here: How to see AJAX query behind list view in Backpack?. To add to this previous answer, if you have Laravel Debugbar (https://github.com/barryvdh/laravel-debugbar) installed you can display the Backpack column query there using this: \Debugbar::info(asSql($this->crud->query));