Filament•14mo ago

Create select with pivot relationship

I need to insert a Select with single selta in my project. The main model of the project is "Game", The select takes data from the Franchise_pivot table in which it has: -franchise_id -gioco_id And where it parses franchise_id (to return the name) in Franchise which is structured: -id -name How can I implement this select? Currently I have done:

                                     Select::make('Franchise')
                                        ->label('Franchise:')
                                        ->relationship('franchise')
                                        ->searchable(),

                                     Select::make('Franchise')
                                        ->label('Franchise:')
                                        ->relationship('franchise')
                                        ->searchable(),

Model "Game"

    public function franchise(): belongsTo
    {
        return $this->belongsTo(Franchise_pivot::class, 'id', 'gioco_id');
    }

    public function franchise(): belongsTo
    {
        return $this->belongsTo(Franchise_pivot::class, 'id', 'gioco_id');
    }

But the result is that I have the game id in the select, and not the franchise name

13 Replies

AskancyOP•14mo ago

bump

awcodes•14mo ago

->relationship('franchise', 'name')

AskancyOP•14mo ago

Even with 'name' it keeps returning the id. Also 4 is the id of the game I am editing, it should show me id 7 to be correct...

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'franchise_pivot.name' in 'field list'
SELECT
  `franchise_pivot`.`name`,
  `franchise_pivot`.`id`
FROM
  `franchise_pivot`
WHERE
  (`name` LIKE % assasas %)
ORDER BY
  `franchise_pivot`.`name` ASC
limit
  50

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'franchise_pivot.name' in 'field list'
SELECT
  `franchise_pivot`.`name`,
  `franchise_pivot`.`id`
FROM
  `franchise_pivot`
WHERE
  (`name` LIKE % assasas %)
ORDER BY
  `franchise_pivot`.`name` ASC
limit
  50

the name column should take it from Franchise not Franchise_pivot

awcodes•14mo ago

if you do Game::first()->franchise in tinker, does it give you the franchise model?

AskancyOP•14mo ago

no I get null

awcodes•14mo ago

first make sure that Game::first() gives you a Game that should have franchises, if you still get null then something is off in your relationship. or Game::where('id', some id that has franchises)->get()

AskancyOP•14mo ago

Nothing, I always get null. the franchise field you read in the Game leave it alone, first not using a pivot table, I generated it so I could convert my panel to Filamentphp

AskancyOP•14mo ago

Now in the edit page the select remains empty, but if I search for a Franchise and save, it correctly saves the query in the database:

Select::make('Franchise')
    ->label('Franchise:')
    ->relationship('franchises', 'name')
    ->searchable(),

Select::make('Franchise')
    ->label('Franchise:')
    ->relationship('franchises', 'name')
    ->searchable(),

public function franchises(): belongsToMany
{
    return $this->belongsToMany(Franchise::class, 'franchise_pivot', 'gioco_id', 'franchise_id')
    ->select('franchise.id AS franchise_id');    
}

public function franchises(): belongsToMany
{
    return $this->belongsToMany(Franchise::class, 'franchise_pivot', 'gioco_id', 'franchise_id')
    ->select('franchise.id AS franchise_id');    
}

However, how come it saves correctly in the table but the select when viewing the edit page remains empty? ->select('franchise.id AS franchise_id') I had to add it because I was getting the error: >SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous Bump

krekas•14mo ago

your relation looks weird

AskancyOP•14mo ago

I agree, But I don't understand why if I try a relationship like:

    public function franchises(): belongsToMany
    {
        return $this->belongsToMany(Franchise::class, 'franchise_pivot', 'gioco_id', 'franchise_id');
    }

    public function franchises(): belongsToMany
    {
        return $this->belongsToMany(Franchise::class, 'franchise_pivot', 'gioco_id', 'franchise_id');
    }

I get the error: >SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous

SELECT
  DISTINCT `franchise`.*
FROM
  `franchise`
  LEFT JOIN `franchise_pivot` ON `franchise`.`id` = `franchise_pivot`.`franchise_id`
WHERE
  `id` = 7
limit
  1

SELECT
  DISTINCT `franchise`.*
FROM
  `franchise`
  LEFT JOIN `franchise_pivot` ON `franchise`.`id` = `franchise_pivot`.`franchise_id`
WHERE
  `id` = 7
limit
  1

looking at the sql query, it looks correct

krekas•14mo ago

column names are in english but table name isn't so use english naming for everything first then check the docs for relationships and how tables should be named belongs to many shouldn't be named like that when you will use laravel naming i think all problems will be solved automatically

AskancyOP•14mo ago

I'm converting an existing project from 2008, which has evolved over the years, so I can't use all the Laravel conventions... I don't think one is obliged to follow these conventions.... it's recommend but not obliged...

krekas•14mo ago

but it solves a lot of problems maybe you could use enligsh naming and migrate data to new tables? otherwise you should check laravel docs for relationship i'm 99% sure it's a problem with your relations

Gaming

Programming

Create select with pivot relationship