Filament•2y ago

Column Not Found SQL ERROR

Hello there filament, Love the admin panel, tough I have a weird issue I can't figure out. When I sort the visible 'Venue names' I get an SQL error saying Column not found. I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'questions.venue_id' in 'where clause'

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'questions.venue_id' in 'where clause'

I believe the query it fails on is:

select `name` from `venues` where `questions`.`venue_id` = `venues`.`id`;

select `name` from `venues` where `questions`.`venue_id` = `venues`.`id`;

But if I run

SELECT VENUE_ID FROM QUESTIONS;

SELECT VENUE_ID FROM QUESTIONS;

On my database I do get all venue_id returned

14 Replies

Dennis Koch•2y ago

You are missing a join.

YasserOP•2y ago

Where can I find the Query with the join that gets fired ?

                TextColumn::make('question.venue.name')
                    ->label(__('venue.Venue'))
                    ->sortable()
                    ->searchable(),

                TextColumn::make('question.venue.name')
                    ->label(__('venue.Venue'))
                    ->sortable()
                    ->searchable(),

This is how I have my column set --- the Answer belongs to a single question

    public function question(): BelongsTo
    {
        return $this->belongsTo(Question::class);
    }

    public function question(): BelongsTo
    {
        return $this->belongsTo(Question::class);
    }

And a question belongsTo a single Venue

    public function venue(): BelongsTo
    {
        return $this->belongsTo(Venue::class);
    }

    public function venue(): BelongsTo
    {
        return $this->belongsTo(Venue::class);
    }

Tough a Question also has Many answers

    public function answers():HasMany{
        return $this->hasMany(Answer::class);
    }

    public function answers():HasMany{
        return $this->hasMany(Answer::class);
    }

--- Should I fix this by relating an answer to a venue with a foregin id as well ?

Dennis Koch•2y ago

Not sure if this is supported 3 levels deep

YasserOP•2y ago

Thanks for your input! I'll make the extra relationship then I guess 🙂

marty6236•11mo ago

Hi, I have a question, when migrating data into my database I get an error message SQLSTATE[HY000]: (errno: 150 "Foreign key constraint is incorrectly formed") (Connection: mysql, SQL: alter table cocntacts add constraint cocntacts_country_id_foreign foreign key (country_id) references countries (id) on delete cascade). What am I doing wrong?

Tobias Platen•11mo ago

This is more of a Laravel issue, but why don't you show us your two migrations? Then we can probably find the error.

marty6236•11mo ago

here are my migrations

2024_01_03_160314_cr...

2024_01_03_162039_cr...

2024_01_03_162054_cr...

2024_01_03_162023_cr...

marty6236•11mo ago

for those lines that are commented, it gives me an error

Tobias Platen•11mo ago

You have a sequencing issue. You can only establish a relationship between columns if both exist. The first migration that is executed attempts to establish relationships with a column in a table that doesn't even exist yet.

marty6236•11mo ago

`how can I set the migration order

Tobias Platen•11mo ago

Quote from Laravel Documentation:

Each migration filename contains a timestamp that allows Laravel to determine the order of the migrations:

Each migration filename contains a timestamp that allows Laravel to determine the order of the migrations:

The timestamp determines when each migration is executed. Change the timestamp in the filename to alter the order.

marty6236•11mo ago

Ok thanks for the advice

marty6236•10mo ago

Hi I have a problem with saving data to the database. SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '["1","2"]' for column crm.companies.contact_id at row 1.May I ask your advice please?

YasserOP•10mo ago

this is Laravel relationships and not filament related. Your screenshots suggest you have a hasOne->contact relation ship and you are assigning an array of contact_ids which suggest you are implementing an hasMany->contacts

Gaming

Programming

Column Not Found SQL ERROR