has_many referencing belongs_to

[josefch]

Description

I have an entry that has_many other rows that themselves refer to a belongs_to row, for example a person can have many categories that have a 1:1 relationship to category which holds the name.

When I setup (migrations/models and blueprints below) and edit a person I'm unable to save a choosen category (from the select), instead of inserting the id it tries to save [id] instead, errors like:

[19:33:08] LOG.error: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type integer: "[2]" CONTEXT: unnamed portal parameter $1 = '...' (SQL: insert into "person_category" ("category") values ([2]) returning "id")

On top (if I add manually in the database) I get

Categories 3

in the form - so instead of showing the name of the category I get the ID of it (which is clickable/editable - but leads to the above error). I hope it's understandable/reproduceble - hope there's a fix for it...

Steps to reproduce

Migrations:

    public function up()
    {
        Schema::create('person', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name')->nullable();
        });
    }
    public function up()
    {
        Schema::create('category', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name')->nullable();
        });
    }

 public function up()
    {
        Schema::create('person_category', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('person');
            $table->integer('category');
            $table->foreign('person')->references('id')->on('person');
            $table->foreign('category')->references('id')->on('category');
        });
    }

The models:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;

/**
 * Class Person
 * 
 * @property int $id
 * @property string|null $name
 * 
 * @property Collection|PersonCategory[] $person_category
 *
 * @package App\Models
 */
class Person extends Model
{
    protected $table = 'person';
    public $timestamps = false;

    protected $fillable = [
        'name'
    ];

    public function person_category()
    {
        return $this->hasMany(PersonCategory::class, 'person');
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

/**
 * Class PersonCategory
 * 
 * @property int $id
 * @property int $person
 * @property int $category
 * 
 *
 * @package App\Models
 */
class PersonCategory extends Model
{
    protected $table = 'person_category';
    public $timestamps = false;

    protected $casts = [
        'person' => 'int',
        'category' => 'int'
    ];

    protected $fillable = [
        'person',
        'category'
    ];

    public function person()
    {
        return $this->belongsTo(Person::class, 'person');
    }

    public function category()
    {
        return $this->belongsTo(Category::class, 'category');
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;

/**
 * Class Category
 * 
 * @property int $id
 * @property string|null $name
 * 
 * 
 * @package App\Models
 */
class Category extends Model
{
    protected $table = 'category';
    public $timestamps = false;

    protected $fillable = [
        'name'
    ];
}

runway.php

'resources' => [
        \App\Models\PersonCategory::class => [
            'name' => 'Test Person Categories',
            'blueprint' => 'person_category',    
            'hidden' => true,
        ],
        \App\Models\Category::class => [
            'name' => 'Categories',
            'blueprint' => 'category',      
            // 'hidden' => true,        
        ],
        \App\Models\Person::class => [
            'name' => 'Test Person',
            'blueprint' => 'person',      
            'nav' => [
               'title' => 'Test Person',
            ],
        ],
]

Blueprints

person.yaml

sections:
  main:
    fields:
      -
        handle: name
        field:
          type: text
          display: Name
          validate: required
      -
        handle: person_category
        field:
          resource: personcategory
          display: Categories
          icon: has_many
          type: has_many
          width: 66
          instructions_position: above
          visibility: visible
          listable: hidden
          always_save: false
          mode: table
          create: true
          with:
            - personcategory

person_category.yaml

sections:
  main:
    fields:
      -
        handle: category
        field:
          resource: category
          display: Category
          title_format: '{{ name }}'
          icon: belongs_to
          type: belongs_to
          width: 50
          always_save: false
          mode: select
          create: true
          with:
            - category

category.yaml

sections:
  main:
    fields:
      -
        handle: name
        field:
          type: text
          display: Name
          validate: required

Environment

Application Name: Laravel Laravel Version: 9.45.1 PHP Version: 8.1.13 Composer Version: 2.4.2 Environment: local Debug Mode: ENABLED URL: laravel Maintenance Mode: OFF

Cache Config: NOT CACHED Events: NOT CACHED Routes: NOT CACHED Views: CACHED

Drivers Broadcasting: log Cache: statamic Database: pgsql Logs: stack / single Mail: smtp Queue: sync Session: file

Statamic Addons: 1 Antlers: regex Stache Watcher: Enabled Static Caching: Disabled Version: 3.3.64 PRO

Statamic Addons doublethreedigital/runway: 2.6.3

[duncanmcclean]

Thanks for such a detailed issue - I was able to reproduce the issue straight away!

Is there any reason you aren't using native Eloquent pivot tables for the people <-> category relationships rather than creating your own intermediary model?

The reason I ask is because it would be much easier from Runway's perspective for you to have a HasMany field on both people & categories which can link to each other and get rid of the middle table (which I'm struggling to make work the way it is).

The database structure would stay pretty much the same.

[josefch]

Thanks Duncan, well I changed my models to have toMany relations, PersonCategory still has the two belogsTo - but that doesn't change anything in the problem for me, still getting [id] errors - what else would I need to change to get it working? Blueprints having the handles categories and people as named in the models but seeing numerical IDs in the form still, any help appreciated...

class Person extends Model
{
    protected $table = 'person';
    public $timestamps = false;

    protected $fillable = [
        'name'
    ];

    public function categories()
    {
        return $this->belongsToMany(Category::class, 'person_category', 'person', 'category')
                    ->withPivot('id');
    }
}

class Category extends Model
{
    protected $table = 'category';
    public $timestamps = false;

    protected $fillable = [
        'name'
    ];

    public function people()
    {
        return $this->belongsToMany(Person::class, 'person_category', 'category', 'person')
                    ->withPivot('id');
    }
}

[duncanmcclean]

Get rid of the PersonCategory model from Runway & it's blueprint.

Then change the people/category fields on both sides to HasMany fields.

[josefch]

Thanks Duncan, that did the trick, working very well now - brilliant - THANKS

[duncanmcclean]

Awesome, glad you hear it! I'll close this issue now.