Invalid SQL Generated when using Relation Manager

Hi folks, I was interested to explore this. I issued this command

MyPropertyResource my_property_rooms room_name

MyPropertyResource my_property_rooms room_name

and then registered the relation. When I execute the app I am getting this SQL being generated:

SELECT
  count(*) AS aggregate
FROM
  `my_property_rooms`
WHERE
  `my_property_rooms`.`my_property_my_property_id` = 6
  AND `my_property_rooms`.`my_property_my_property_id` IS NOT NULL

SELECT
  count(*) AS aggregate
FROM
  `my_property_rooms`
WHERE
  `my_property_rooms`.`my_property_my_property_id` = 6
  AND `my_property_rooms`.`my_property_my_property_id` IS NOT NULL

It should read like this

SELECT
  count(*) AS aggregate
FROM
  `my_property_rooms`
WHERE
  `my_property_rooms`.`my_property_id` = 6
  AND `my_property_rooms`.`my_property_id` IS NOT NULL

SELECT
  count(*) AS aggregate
FROM
  `my_property_rooms`
WHERE
  `my_property_rooms`.`my_property_id` = 6
  AND `my_property_rooms`.`my_property_id` IS NOT NULL

I cannot see where it's looking when generating the SQL that could result in it determining that there is a column called

my_property_my_property_id

my_property_my_property_id

Anyone able to help? thx J

Solution:

It's probably or in the other order: ```php public function my_property_rooms() {...

Jump to solution

10 Replies

jjo63OP•7mo ago

Oh here is a link to the Flare - https://flareapp.io/share/o7AQBJv7

Flare

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'my_property_rooms.my_property_my_property_id' in 'where clause' (Connection: mysql, SQL: select count(*) as aggregate from my_property_rooms where my_property_rooms.my_property_my_property_id = 6 and my_property_rooms.my_property_my_property_id is not null) - The error occurred at...

Dennis Koch•7mo ago

Can you share the relationship? And the relation manager?

jjo63OP•7mo ago

The relationship is my_properties (parent - primary key is

my_property_id

my_property_id

) and the child is my_property_rooms (primary key is

property_room_id

property_room_id

and has an FK to my_properties - in MySQL terms it is defined as

CONSTRAINT `fk_t_my_property_rooms_t_my_properties1` FOREIGN KEY (`my_property_id`) REFERENCES `my_properties` (`my_property_id`),

CONSTRAINT `fk_t_my_property_rooms_t_my_properties1` FOREIGN KEY (`my_property_id`) REFERENCES `my_properties` (`my_property_id`),

The relation manager is defined as:

<?php

namespace App\Filament\App\Resources\MyPropertyResource\RelationManagers;

use Filament\Forms;
use Filament\Forms\Form;
use Filament\Resources\RelationManagers\RelationManager;
use Filament\Tables;
use Filament\Tables\Table;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\SoftDeletingScope;

class MyPropertyRoomsRelationManager extends RelationManager
{
    protected static string $relationship = 'my_property_rooms';

    public function form(Form $form): Form
    {
        return $form
            ->schema([
                Forms\Components\TextInput::make('room_name')
                    ->required()
                    ->maxLength(255),
            ]);
    }

    public function table(Table $table): Table
    {
        return $table
            ->recordTitleAttribute('room_name')
            ->columns([
                Tables\Columns\TextColumn::make('room_name'),
            ])
            ->filters([
                //
            ])
            ->headerActions([
                Tables\Actions\CreateAction::make(),
            ])
            ->actions([
                Tables\Actions\EditAction::make(),
                Tables\Actions\DeleteAction::make(),
            ])
            ->bulkActions([
                Tables\Actions\BulkActionGroup::make([
                    Tables\Actions\DeleteBulkAction::make(),
                ]),
            ]);
    }
}

<?php

namespace App\Filament\App\Resources\MyPropertyResource\RelationManagers;

use Filament\Forms;
use Filament\Forms\Form;
use Filament\Resources\RelationManagers\RelationManager;
use Filament\Tables;
use Filament\Tables\Table;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\SoftDeletingScope;

class MyPropertyRoomsRelationManager extends RelationManager
{
    protected static string $relationship = 'my_property_rooms';

    public function form(Form $form): Form
    {
        return $form
            ->schema([
                Forms\Components\TextInput::make('room_name')
                    ->required()
                    ->maxLength(255),
            ]);
    }

    public function table(Table $table): Table
    {
        return $table
            ->recordTitleAttribute('room_name')
            ->columns([
                Tables\Columns\TextColumn::make('room_name'),
            ])
            ->filters([
                //
            ])
            ->headerActions([
                Tables\Actions\CreateAction::make(),
            ])
            ->actions([
                Tables\Actions\EditAction::make(),
                Tables\Actions\DeleteAction::make(),
            ])
            ->bulkActions([
                Tables\Actions\BulkActionGroup::make([
                    Tables\Actions\DeleteBulkAction::make(),
                ]),
            ]);
    }
}

Dennis Koch•7mo ago

I mean the relationship definition on your Model

jjo63OP•7mo ago

Entire MyProperty model:

<?php

/**
 * Created by Reliese Model.
 */

namespace App\Models;

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

/**
 * Class MyProperty
 * 
 * @property int $my_property_id
 * @property int $user_id
 * @property int $client_id
 * @property string $friendly_name
 * @property string|null $address1
 * @property string|null $address2
 * @property string|null $city
 * @property string|null $county
 * @property string $country
 * @property string|null $postal_code
 * @property string|null $property_status
 * @property string|null $photo
 * @property string|null $currency
 * @property Carbon $cre_date
 * @property int|null $cre_user_id
 * @property Carbon|null $upd_date
 * @property int|null $upd_user_id
 * 
 * @property User $user
 * @property Collection|MyItem[] $my_items
 * @property Collection|MyPropertyRoom[] $my_property_rooms
 *
 * @package App\Models
 */
class MyProperty extends Model
{
    protected $table = 'my_properties';
    protected $primaryKey = 'my_property_id';
    public $timestamps = false;

    protected $casts = [
        'user_id' => 'int',
        'client_id' => 'int',
        'cre_date' => 'datetime',
        'cre_user_id' => 'int',
        'upd_date' => 'datetime',
        'upd_user_id' => 'int'
    ];

    protected $fillable = [
        'user_id',
        'client_id',
        'friendly_name',
        'address1',
        'address2',
        'city',
        'county',
        'country',
        'postal_code',
        'property_status',
        'photo',
        'currency',
        'cre_date',
        'cre_user_id',
        'upd_date',
        'upd_user_id'
    ];

    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function my_items()
    {
        return $this->hasMany(MyItem::class);
    }

    public function my_property_rooms()
    {
        return $this->hasMany(MyPropertyRoom::class);
    }
}

<?php

/**
 * Created by Reliese Model.
 */

namespace App\Models;

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

/**
 * Class MyProperty
 * 
 * @property int $my_property_id
 * @property int $user_id
 * @property int $client_id
 * @property string $friendly_name
 * @property string|null $address1
 * @property string|null $address2
 * @property string|null $city
 * @property string|null $county
 * @property string $country
 * @property string|null $postal_code
 * @property string|null $property_status
 * @property string|null $photo
 * @property string|null $currency
 * @property Carbon $cre_date
 * @property int|null $cre_user_id
 * @property Carbon|null $upd_date
 * @property int|null $upd_user_id
 * 
 * @property User $user
 * @property Collection|MyItem[] $my_items
 * @property Collection|MyPropertyRoom[] $my_property_rooms
 *
 * @package App\Models
 */
class MyProperty extends Model
{
    protected $table = 'my_properties';
    protected $primaryKey = 'my_property_id';
    public $timestamps = false;

    protected $casts = [
        'user_id' => 'int',
        'client_id' => 'int',
        'cre_date' => 'datetime',
        'cre_user_id' => 'int',
        'upd_date' => 'datetime',
        'upd_user_id' => 'int'
    ];

    protected $fillable = [
        'user_id',
        'client_id',
        'friendly_name',
        'address1',
        'address2',
        'city',
        'county',
        'country',
        'postal_code',
        'property_status',
        'photo',
        'currency',
        'cre_date',
        'cre_user_id',
        'upd_date',
        'upd_user_id'
    ];

    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function my_items()
    {
        return $this->hasMany(MyItem::class);
    }

    public function my_property_rooms()
    {
        return $this->hasMany(MyPropertyRoom::class);
    }
}

Dennis Koch•7mo ago

I guess the issue is that you don't follow Laravel conventions but rely on them. This part assumes you are following Laravle conventions. You need to pass your keys as second and maybe third param.

public function my_property_rooms()
{
  return $this->hasMany(MyPropertyRoom::class);
}

public function my_property_rooms()
{
  return $this->hasMany(MyPropertyRoom::class);
}

jjo63OP•7mo ago

Ah OK - the db design & naming conventions didn't explicitly align with any conventions (no tool specifically in mind at the time). As a newbie also to Laravel and PHP I'm finding my feet still. Where would I be passing these keys - within the "relation manager"?

Dennis Koch•7mo ago

No, this is totally unrelated to Filament. It's about your Laravel relationships.

Solution

Dennis Koch•7mo ago

It's probably or in the other order:

public function my_property_rooms()
{
  return $this->hasMany(MyPropertyRoom::class, 'property_room_id', 'my_property_id');
}

public function my_property_rooms()
{
  return $this->hasMany(MyPropertyRoom::class, 'property_room_id', 'my_property_id');
}

jjo63OP•7mo ago

Thanks Dennis, I have this working now - really do appreciate your guidance on that & appreciate that it's core Laravel knowledge I'm missing (which I fully understand is itself a prereq to using Filament effectively). cheers, j

Gaming

Programming

Invalid SQL Generated when using Relation Manager