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

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

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

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

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:

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');
}

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');
}

Jump to solution

jjo63OP•5/18/24, 11:15 AM

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_roomsmy_property_rooms where my_property_roomsmy_property_rooms.my_property_my_property_idmy_property_my_property_id = 6 and my_property_roomsmy_property_rooms.my_property_my_property_idmy_property_my_property_id is not null) - The error occurred at...

Dennis Koch•5/18/24, 12:06 PM

Can you share the relationship? And the relation manager?

jjo63OP•5/18/24, 12:53 PM

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(),
                ]),
            ]);
    }
}

Jjjo63 The relationship is my_properties (parent - primary key is ```my_property_id```)...

Dennis Koch•5/18/24, 1:34 PM

I mean the relationship definition on your Model

jjo63OP•5/18/24, 1:36 PM

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•5/18/24, 1:36 PM

I guess the issue is that you don't follow Laravel conventions but rely on them.

Dennis Koch•5/18/24, 1:37 PM

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•5/18/24, 2:11 PM

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"?

Jjjo63 Ah OK - the db design & naming conventions didn't explicitly align with any conv...

Dennis Koch•5/18/24, 2:22 PM

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

Solution

Dennis Koch•5/18/24, 2:23 PM

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');
}

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

jjo63OP•5/18/24, 5:32 PM

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