search

statamic / eloquent-driver

Provides support for storing your Statamic data in a database, rather than flat files.
https://statamic.dev/tips/storing-content-in-a-database
MIT License
104 stars 71 forks source link

Support mapping of entry data to database columns #273

Closed ryanmitchell closed 3 days ago

ryanmitchell commented 2 months ago

This PR makes it possible to store the contents of data handles in alternative columns in your database, eg you may want to store my_key in a column called my_key.

The benefit of this is it allows you to index that column separately which can speed up database operations.

Mapping can be added by adding a hook on the eloquent entry class, and updating the array $payload. This array is a mapping of data handles to database columns (note that only root-level data keys are supported).

The mapping 'auto-magically' happens by checking if a column in your entries table exists with the same name as your field handle, and where it finds it, it saves the field value there instead of the data column.

Following some discussion with Duncan, some additional thoughts:

  1. If you are adding a migration to add a new column your schema based on an existing blueprint column, you'll need to save the entries to repopulate them Entry::all()->each->save(); as part of your migration.

  2. We're going to wrap this in a config to avoid it being a breaking change, so to enable this feature you'll need to set statamic.eloquent-driver.entries.map_data_to_columns to be true

Closes https://github.com/statamic/eloquent-driver/issues/272

ryanmitchell commented 2 months ago

@duncanmcclean @jasonvarga would appreciate your thoughts on this when you get a chance.

jasonvarga commented 2 months ago

The statamic-5 branch has been merged, so this PR should target master.

duncanmcclean commented 3 weeks ago

Mapping can be added by adding a hook on the eloquent entry class, and updating the array $payload. This array is a mapping of data handles to database columns (note that only root-level data keys are supported).

Just thinking out loud here.... instead of mapping the columns using a hook, is there any way the Eloquent Driver could figure out those columns itself, based on the columns in the database?

Maybe it could get a list of the model's columns, filters out columns that already get mapped (like slug, data, updated_at, etc), then use the columns that are left to read/write fields from?

ryanmitchell commented 3 weeks ago

It could, but that would mean an extra query every time to get the columns. Not a hugely expensive operation but worth considering. It also limits us to root keys only - the current mapping approach could quite easily be extended to allow non-root keys.