Saving Post Type Fields To A Custom Database Table

In the previous tutorial, we covered creating a seat post type. Let's extend the functionality of that post type and save its input fields to a custom database table instead of post meta. But why save to custom fields to anything other than post meta? Great question:

1. Post meta is very slow when doing complex queries.
2. Post meta does not provide the ability to normalize structure.
3. Post meta is prone to data inconsistencies.
4. Post meta makes it very difficult to both sort and limit queries.

On the other hand, custom tables give us a lot of options:

1. Custom tables can be normalized and reduce data entry errors.
2. Custom tables can be optimized and indexed for greater performance.
3. Custom tables make it extremely easy to make complex queries.

#Why Use Post Meta?

So, why use post meta ever? Also, a good question.

1. Because post meta is a known structure, plugins can integrate with post meta. Custom tables do not provide this ability because they are unknown to anyone other than you.
2. Custom tables can not be easily integrated into WordPress core queries like the main loop in the admin. Post meta can.

While there are many reasons to use custom tables, there are also many to use post meta. Depending on your needs, you might want to use post meta. However, I've found a mixture of post meta and custom tables is a great blend.

#Making A Custom Table

Using the galaxy CLI, run the following commands to make a model and migration for the custom table. The table will be used by the seat post type to store custom field data.

#Migration

php galaxy make:migration add_pt_seat_data

In the migration file ad the following:

-- Description: Seat Data
-- >>> Up >>>
CREATE TABLE `{
Pro Only Feature
prefix
Pro Only Feature
}pt_seat_data` (
`id` int NOT NULL AUTO_INCREMENT,
`seat_id` int DEFAULT NULL,
`row` int NOT NULL,
`letter` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- >>> Down >>>
DROP TABLE IF EXISTS `{
Pro Only Feature
prefix
Pro Only Feature
}pt_seat_data`

Now, run the migration:

php galaxy migrate up

#Model

Next, make the model for the custom table.

php galaxy make:model base SeatData

Now, connect the \App\Models\Seat model to the new \App\Models\SeatData.

<?php
namespace App\Models;

use App\Models\SeatData;
use TypeRocket\Models\WPPost;

// ...

class Seat extends WPPost
{
    public const POST_TYPE = 'seat';

    public function data()
    {
        return $this->hasOne(SeatData::class, 'seat_id');
    }

    // ...
}

And not SeatData to the Seat. We also need to change the resource property from seatdatas to pt_seat_data.

<?php
namespace App\Models;

use App\Models\Seat;
use TypeRocket\Models\Model;

class SeatData extends Model
{
    protected $resource = 'pt_seat_data';

    public function seat()
    {
        $this->belongsTo(Seat::class, 'seat_id');
    }
}

#Seat Post Type Updates

Again, coming from our provious tutorial we covered creating a seat post type. Update your seat post type registration code to the following:

<?php
$seat = tr_post_type('Seat')
            ->setHandler(\App\Controllers\SeatController::class)
            ->setIcon('dashicons-tickets-alt')
            ->featureless()
            ->setAdminOnly()
            ->addColumn('data.Row')
            ->addColumn('data.Letter')
            ->setMainForm(function() {
                $form = tr_form();
                echo $form->select('data.Row')->searchable()->setOptions(range(1, 15), 'flat');
                echo $form->radio('data.Letter')->setOptions(['A','C','D','F'], 'flat')->setDefault('A');
            })
            ->setModelClass(\App\Models\Seat::class)
            ->saveTitleAs(function (\App\Models\Seat $seat) {
                return $seat->data->row.$seat->data->letter;
            });

Here we have added a few new methods.

// Tell the post type what model and controller
// it belongs too.
$seat->setHandler(\App\Controllers\SeatController::class);
$seat->setModelClass(\App\Models\Seat::class);

We also, replace the previous meta with data in the saveTitleAs() method. By switching to data we access the data() method on the Seat model. This points us away from post meta and into our custom table columns row and letter.

// From
return $seat->meta->row.$seat->meta->letter;
// To
return $seat->data->row.$seat->data->letter;

Finally, we prefixed the fields and addColumn() names with data.Field Name (custom table fields can not be sorted in the UI). Doing this does the same as switching away from meta in the saveTitleAs() method. We reach into data instead of defaulting to meta. However, there is one more vital step to swatch away from the post meta default setting. We need to declare the row and letter fields as no longer being meta fields (TypeRocket assumes custom fields on post types will be post meta).

Update the Seat model and tell it those fields are not to save as meta using the $metaless protected property.

class Seat extends WPPost
{
    protected $metaless = [
        'letter',
        'row',
        'data' // Also, do not save the group
    ];

    // ...
}

Now, we need to tell the \App\Controllers\SeatController how to save the custom fields when they are submitted on the post type admin page. We can do this using the onActionSave() method. onActionSave() fires after a post type is updated or created.

<?php
namespace App\Controllers;

use App\Models\Seat;
use App\Models\SeatData;
use TypeRocket\Controllers\WPPostController;
use TypeRocket\Http\Request;

class SeatController extends WPPostController
{
    protected $modelClass = Seat::class;

    public function onActionSave($type, Seat $seat, SeatData $data, Request $request)
    {
        $data
            ->findFirstWhereOrNewWith('seat_id', $seat->getID())
            ->save($request->fields('data'));
    }
}

When we save the data, we use the model method findFirstWhereOrNewWith(). This method looks for the first record it finds a match for and returned its model, and if it does not, it creates a new model with the where clause assigned to it.

// This is
$data
    ->findFirstWhereOrNewWith('seat_id', $seat->getID())
    ->save($request->fields('data'));

// The same as
$id = $seat->getID();
$item = $data->findFirstWhereOrNew('seat_id', $id);
$item->seat_id = $id;
item->save($request->fields('data'));