October: Is it really possible to use OctoberCMS if your primary keys are not named `id`?

[jfoliveira]

Hi, I'm working with OctoberCMS and I must use and existing database whose adopted naming convention for primary keys is to use the table name as the PK column name.

If I add an id column to the Category table everything works fine: a dropdown is displayed on the product form with the current category correctly selected.

Unfortunately I can't add the id column to the Category table as this is an existing production database.

Here are my models, notice the belongsTo relation definition:

<?php namespace My\Plugin\Models;
use Model;

class Product extends Model
{
    public $table = 'Product';
    protected $primaryKey = 'Product';

    protected $guarded = ['*'];
    protected $fillable = [];
    public $hasOne = [];

    public $belongsTo = [
        'category' => [
            'My\Plugin\Models\Category',
            'key' => 'Category',
            'otherKey' => 'Category',
        ],
    ];

    public $belongsToMany = [];
    public $morphTo = [];
    public $morphOne = [];
    public $morphMany = [];
    public $attachOne = [];
    public $attachMany = [];

}

And the category model:

<?php namespace My\Plugin\Models;

use Model;

class Category extends Model
{
    public $table = 'Category';
    protected $primaryKey = 'Category';

    protected $guarded = ['*'];
    protected $fillable = [];
    public $hasOne = [];
    public $hasMany = [];
    public $belongsTo = [];
    public $belongsToMany = [];
    public $morphTo = [];
    public $morphOne = [];
    public $morphMany = [];
    public $attachOne = [];
    public $attachMany = [];
}

My Product model fields.yaml file:

    category:
        label: Category
        span: left
        type: relation
        nameFrom: Description
        emptyOption: Select a category

No exception is thrown/logged when the id field doesn't exist. As can be seen by the belongsTo relation above I'm clearly specifying the Category field to be used as the relation key.

Any ideas why by just adding the id field everything works? What can I do to use OctoberCMS with PK fields that are not named id?

[tschallacka]

in the model define a property named

public $primaryKey = 'primary_key_name';

and then it'll work fine. just also modify controllers etc.. everything that defaults to id.

https://octobercms.com/forum/post/howto-use-a-different-primary-key-than-id-in-a-model-or-controller

[tschallacka]

for your use case, define a $primaryKey in your Category where you define it's key.

class Category extends Model
{
    public $table = 'Category';
    protected $primaryKey = 'Category';

}

october is then smart enough to figure out the primary key by using getKey() on the model and getKeyName()

class Product extends Model
{
    public $table = 'Product';
    protected $primaryKey = 'Product';

    protected $guarded = ['*'];
    protected $fillable = [];
    public $hasOne = [];

    public $belongsTo = [
        'category' => [ // will automagically look for a key that is $fieldname . '_id' Products.category_id
            'My\Plugin\Models\Category',
            // 'key' => 'Category' // If you use this it looks for the foreign key in Products.Category
        ],
    ];

}

that's all you need, if your Product table has a column called category_id.

If for some reason you are not able to add it add

'key' = 'Category' if the column name is category in the product database.

The belongs to looks for a foreign key stored in the product table. No need to define the other key if it's just the primary key of the Category Model.

[jfoliveira]

@tschallacka thanks a lot! Removing the otherKey and only setting the key in the belongsTo relation the dropdown works fine with this yaml settings:

category:
    label: Category
    span: left
    type: relation
    nameFrom: Description
    emptyOption: Select a category

However, if I change the field to use a recordfinder the record finder window is displayed but the selected category is not stored on the Products table - not even displayed on the field:

category:
    label: Category
    span: left
    type: recordfinder
    list: $/my/plugin/models/category/columns.yaml
    nameFrom: Description
    emptyOption: Select a category

I noticed this query using debugbar - notice the id param doesn't seem to be resolved:

select * from `Category` where `Category`.`Category` = ':id' limit 1

Any ideas what else should be changed to be able to use the recordfinder?

Thanks!

[tschallacka]

I think it's another case of a hardcoded id for primary key in a form field plugin again...

https://github.com/octobercms/october/blob/2e42ab3bce8cfe77d63489e7751662f4339f2be5/modules/backend/formwidgets/RecordFinder.php#L235

yup... $model->getKeyName() is aparently not as easy as hardcoding :id.....

It needs a pull request for it to be fixed

for now you can fix it by replacing that sentence by

$config->recordOnClick = sprintf("$('#%s').recordFinder('updateRecord', this, ':'.$config->model->getKeyName()), $this->getId());

[jfoliveira]

@tschallacka thanks for the workaround! Changing to

$config->recordOnClick = sprintf("$('#%s').recordFinder('updateRecord', this, ':" . $config->model->getKeyName() . "')", $this->getId());

fixed the mentioned query, but still the selected record is not displayed.
I'll try to find out why, but as you mentioned it seems that are plenty of hardcoded references to id across OctoberCMS code.

Unfortunately I don't think it's ready to be used with database tables that don't have an id field as every step you move forward a new obstacle is found. :-(

Thanks anyway!

[jfoliveira]

I managed to get it working by changing two things:

1) Using $this->keyFrom instead of $config->model->getKeyName():

$config->recordOnClick = sprintf("$('#%s').recordFinder('updateRecord', this, ':" . $this->keyFrom . "')", $this->getId());

2) Adding keyFrom setting to my fields.yaml file:

category:
    label: Category
    span: left
    type: relation
    nameFrom: Description
    keyFrom: Category      # <------- workaround if your table doesn't have an id field
    emptyOption: Select a category

[jfoliveira]

The keyFrom option is not documented here.
Does anyone know why do we need it?
Wouldn't it always be the same as the model primary key?

[daftspunk]

Fixed by #1768

[vanyaraspopov]

@daftspunk you should add keyFrom option in the docs https://octobercms.com/docs/backend/forms#widget-recordfinder

[LukeTowers]

@vanyaraspopov docs are open source, feel free to submit a PR to octobercms/docs!

[vanyaraspopov]

@LukeTowers держи )
https://github.com/octobercms/docs/pull/335