[v12][Feature Request] store fake fields in "meta" table

[eduardoarandah]

Bug report

This is not a bug, see below

What I did

I want to store fake fields in meta table instead of json

What I expected to happen

--

What happened

--

What I've already tried to fix it

--

Backpack, Laravel, PHP, DB version

PHP VERSION:

PHP 7.3.15-1+ubuntu18.04.1+deb.sury.org+1 (cli) (built: Feb 20 2020 12:23:37) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.15, Copyright (c) 1998-2018 Zend Technologies
with Zend OPcache v7.3.15-1+ubuntu18.04.1+deb.sury.org+1, Copyright (c) 1999-2018, by Zend Technologies

LARAVEL VERSION:

laravel/framework v6.17.1 The Laravel Framework.

BACKPACK VERSION:

backpack/crud 4.0.42 Quickly build an admin interfaces using Laravel 6, CoreUI, Boostrap 4 and jQuery.
backpack/generators 2.0.6 Generate files for laravel projects
backpack/permissionmanager 5.0.7 Users and permissions management interface for Laravel 5 using Backpack CRUD.

Feature Request

I have an "ad" table and an "ad_meta" table.

I want to save extra fields into ad_meta table.

Right now, there's the option of having "fake fields" which is awesome:

https://backpackforlaravel.com/docs/4.0/crud-fields#fake-fields-all-stored-as-json-in-the-database

I'm being creative and creating a mutator and an accessor so I can save and retrieve fields from meta table instead of creating an "extra" field

 public function setExtrasAttribute($value)
 {
 // save in meta table
 }
 public function getExtrasAttribute($value)
 {
// retrieve from meta table
 }

Problem is $value would be better as a simple array instead of json.

Some thoughts:

• There's advantages of having a meta table over json, for example: easier to query, translate, and manipulate in SQL

• This is a popular arquitecture, for example: wordpress (see image)

• This could have two non-breaking approaches:

1) add a as_array to field definition, to skip converting to json and let the user decide how to use data in an accesor an mutator

2) add a meta_table to automatically save in meta_table

[eduardoarandah]

I'm gonna leave this code here, this is how I solved it (and it works perfectly)

Result: data is saved into a _meta table

Define your fake fields in your crudController

 private $_fake_fields = [
 [
   'name' => 'age',
   'label' => 'Age',
   'type' => 'number',
   'tab' => 'Features',
 ],
 [
   'label' => "Skin",
   'type' => "select_from_array",
   'name' => 'skin_id',
   'tab' => 'Features',
   'options' => [
     'Blanca',
     'Morena',
     'Negra' ,
   ],
 ],
...
];

Create a saveMeta function in model

// helper for backpack backend
public function saveMeta($meta)
{
    foreach ($meta as $key => $value) {
        $this->adMetas()->updateOrCreate(
            [
            'ad_id' => $this->id,
            'key' => $key,
            ],
            [
            'value' => $value,
            ]
        );
    }
}

note: adMetas() is a hasMany relationship

 public function adMetas()
 {
     return $this->hasMany('App\AdMeta');
 }

wait... why can't it be a mutator? because the mutator will fail when creating a new model. You can't save related meta if your model doesn't exist.

In your crud controller, override your store and update method

So they call "saveMeta" and store in related table

store: get meta, remove from request, save and call meta save

public function store()
{
    // collect meta
    $meta= [];
    foreach ($this->_fake_fields as $field) {
        $key = $field['name'];
        $value = $this->crud->request->request->get($key);
        $meta[$key] = $value;

        // remove extras
        $this->crud->request->request->remove($key);
        $this->crud->removeField($key);
    }

    // save model
    $response =  $this->traitStore();

    // save meta
    $this->crud->entry->saveMeta($meta);

    // return
    return $response;
}

update: get meta, remove from request, save and call meta save

 public function update()
 {
     // collect meta
     $meta= [];
     foreach ($this->_fake_fields as $field) {
         $key = $field['name'];
         $value = $this->crud->request->request->get($key);
         $meta[$key] = $value;

         // remove extras
         $this->crud->request->request->remove($key);
         $this->crud->removeField($key);
     }

     // save model
     $response =  $this->traitUpdate();

     // save meta
     $this->crud->entry->saveMeta($meta);

     // return
     return $response;
 }

edit: in edit, we restore values from meta table

 public function edit($id)
 {
     $entry = $this->crud->getEntry($id);
     foreach ($entry->adMetas as $adMeta) {
         $entry[$adMeta->key] = $adMeta->value;
     }
     return $this->traitEdit($id);
 }

don't forget to define traitEdit, traitUpdate, traitStore

 use \Backpack\CRUD\app\Http\Controllers\Operations\CreateOperation {
     create as traitCreate;
 }
 use \Backpack\CRUD\app\Http\Controllers\Operations\UpdateOperation {
     edit as traitEdit;
     update as traitUpdate;
 }

this is an example migration of a meta table it's called ad_meta because main table is ad

class CreateAdMetaTable extends Migration
{
    public function up()
    {
        Schema::create(
            'ad_meta', function (Blueprint $table) {
                $table->bigIncrements('id');
                $table->unsignedBigInteger('ad_id');
                $table->string('key');
                $table->text('value')->nullable();
                $table->timestamps();

                $table->foreign('ad_id')->references('id')->on('ad');
            }
        );
    }
}

what do you think?

I'm sure there could be a better way to integrate this kind of functionality into backpack without all this boilerplate, what do you think?

[pxpm]

Hello @eduardoarandah

I am just wondering, if you create a model for ad_meta table, and use it as a regular crud fields wouldn't that be easier and do what you are trying to achieve ?? Is there something that prevents you from doing that ?

Best, Pedro

[eduardoarandah]

@pxpm well, take this as an example:

I need like 300 fields for a person, and those fields are better in a meta table.

If I need, let's say... another kind of information about that person, I can create another crud with only those specific fields.

Why a meta table instead of json

• Meta tables have proven to be very usable in wordpress (post-meta tag-meta user-meta comment-meta)

• Queries are easy to do in eloquent (find a person with meta key=age and value>30)

https://laravel.com/docs/5.6/eloquent-relationships#querying-relationship-existence

• Eloquent collections convert easily to json

https://laravel.com/docs/5.6/eloquent-resources#adding-meta-data

• Json is not usable via raw SQL (or is it? 🤔)

• Saving meta is dead simple, maybe this code can be futher simplified with some collection-eloquent magic to run in one query.

public function saveMeta($meta)
{
    foreach ($meta as $key => $value) {
        $this->adMetas()->updateOrCreate(
            [
            'ad_id' => $this->id,
            'key' => $key,
            ],
            [
            'value' => $value,
            ]
        );
    }
}

• Implementing could be as easy as fake=>true but using meta=>true with convention being _meta ( post saves meta in post_meta )

[lotarbo]

 @eduardoarandah u can make sql query to json field, if in your db column has type json/jsonb https://laravel.com/docs/7.x/queries#json-where-clauses (postgresql, mysql 5.7, percona 5.7) And when u need extra speed u can create virtual colum with index based on field in json https://www.compose.com/articles/mysql-for-json-generated-columns-and-indexing/

But, i think, it will be nice, if backpack can support some EAV package, like https://github.com/rinvex/laravel-attributes

[pxpm]

Have a look at my settings package.

It's pinned in my profile.

Best

A quinta, 12/03/2020, 18:01, Bogdan Lotarev notifications@github.com escreveu:

@eduardoarandah https://github.com/eduardoarandah u can make sql query to json field, if in your db column has type json/jsonb (postgresql, mysql8, percona 5.7) And when u need extra speed u can create virtual colum with index based on field in json

https://www.compose.com/articles/mysql-for-json-generated-columns-and-indexing/

But, i think, it will be nice, if backpack can support some EAV package, like https://github.com/rinvex/laravel-attributes

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/Laravel-Backpack/CRUD/issues/2543#issuecomment-598338326, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABW25P7P2HXSJ3SS7KEPVDLRHEPOHANCNFSM4LGAUEBA .

[tabacitu]

Wow - thanks a lot for sharing this @eduardoarandah . It looks great! I agree with most of your assessment about JSON vs meta table, except for a few:

• JSON querying is possible as @lotarbo mentioned, and most of the times faster than a JOIN + text search;
• while the implementation above seems pretty simple for one CrudController, that serves one Model, things would get a little more complicated if that table were to serve any number of Models; the Meta table would have to become polymorphic; which is not a problem in itself, but it will increase the querying problem;

That being said, I'm not excluding this, and the EAV package @lotarbo mentioned looks very similar in concept - maybe we can support it as-is or use it as inspiration.

But we can't work on this in the next few weeks either, we're hard at work trying to get 4.1 out the door, and this being a "could have" feature, we can't prioritise it over older feature requests. I'll take another look at this once 4.1 is launched, sorry.

Until then, at first glance, a few ways your implementation could be improved (not 100% sure):

• I don't think it's needed to change the controller store() or update() methods; maybe that can be better done inside the model, by overwriting the save() method?
• then the save(), the getter, the setter and the relationship could be put together in a simple trait like HasMetas, that can be used on the model;
• so then you'd only have two things to do when using meta fields:
  • add the trait on the model;
  • add the actual fields (which would still need a way to be differentiated, but I think a faster way would be to just make them start with some string, like meta_);

That's all I've got right now 😄 I'm pretty sure there are more ways to clean this up, though. Or maybe better ways to do this altogether. But for now, in 4.1, JSON will do.

[tabacitu]

Now that I think about it, there has to be a package out there doing metas for Eloquent, in a separate table, like you want them. I've just searched packagist for "laravel metas" and sure enough, here are a few:

• https://github.com/plank/laravel-metable - nice syntax getMeta() and setMeta();
• https://github.com/kodeine/laravel-meta - seems to require a separate table for each model, like your implementation;

Maybe they help 😉

[eduardoarandah]

@tabacitu

• I like the idea of not having a polymorphic table, just a tablename_meta table

• "I don't think it's needed to change the controller store() or update() methods; maybe that can be better done inside the model, by overwriting the save() method?" yes, agree on that, it's just that "meta" information has to be saved AFTER the parent method first insertion, otherwise we don't have an ID. (is there an event for that?)

Questions

• Is there a way of grabbing "fake" fields and send them as an array to a model mutator? (without backpack trying to save them to an "extras" db column)

• Also, reading back fake fields from a model accesor?

This way, this idea of meta tables can be implemented with one of those packages, or a custom solution I guess

[lotarbo]

@tabacitu I'm mentioned EAV mb its offtopic: we have crud page and table pages:

• home page
• about
• contact
• etc

All pages - has common fields like title, is_active and etc. But in most cases, they have many specific fields and it's difficult to manage all this using only fake fields. Also, when using fake and translate - all fake fields automatically marks as need to translate. If we have field, common for all language but marked as fake - this field becomes multilingual. So i think, it would be nice to support some eav package in future)

[tabacitu]

@eduardoarandah :

• yes, afaik there's an event on Eloquent model called saved;
• not that I know of, no; by the time the info reaches the model, all information about what "field" was used gets lost; so you don't know if it was fake or not; you only get the name and the value; so the only way to get a list/array of metas would be if you followed a convention like I was saying earlier, like prefixing everything with meta_; then inside the Model you could have a catch-all method by overwriting the magic __call() method, to get all calls to the meta_smth accessors and mutators;