F
Filament•3mo ago
Schilly

Import Action - "unknown column '' in `where clause` "

Hello - I am trying to get this importer to behave... I am using it to import records into a Relationship Pivot Table. The relationship is a belongsToMany relationship - and knowing that it will not play nice on that end, I have made up a Pivot Model to interact with. Here is the pivot model: 
<?php

namespace App\Models\S3Operations;

use App\Models\S3Operations\Operation;
use App\Models\S5Logistics\Warehouse;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\Pivot;

class OperationWarehouse extends Pivot
{

    protected $fillable =
    [
        'operation_id',
        'warehouse_id',
        'fuel',
        'aircraft',
        'equipment'
    ];

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

    public function warehouse(): BelongsTo
    {
        return $this->belongsTo(Warehouse::class);
    }
}
<?php

namespace App\Models\S3Operations;

use App\Models\S3Operations\Operation;
use App\Models\S5Logistics\Warehouse;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\Pivot;

class OperationWarehouse extends Pivot
{

    protected $fillable =
    [
        'operation_id',
        'warehouse_id',
        'fuel',
        'aircraft',
        'equipment'
    ];

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

    public function warehouse(): BelongsTo
    {
        return $this->belongsTo(Warehouse::class);
    }
}
To go along with the pivot model - I ahve made up the Importer, as follows: 
<?php

namespace App\Filament\Imports;

use App\Models\S3Operations\OperationWarehouse;
use Filament\Actions\Imports\ImportColumn;
use Filament\Actions\Imports\Importer;
use Filament\Actions\Imports\Models\Import;

class OperationWarehouseImporter extends Importer
{
    protected static ?string $model = OperationWarehouse::class;

    public static function getColumns(): array
    {
        return [
            ImportColumn::make('operation_id')
                ->integer()
                ->requiredMapping(),
            ImportColumn::make('warehouse_id')
                ->integer()
                ->requiredMapping(),
            ImportColumn::make('fuel')
                ->numeric(decimalPlaces: 2)
                ->requiredMapping(),
            ImportColumn::make('aircraft')
                ->requiredMapping(),
            ImportColumn::make('equipment')
                ->requiredMapping(),
        ];
    }

    public function resolveRecord(): ?OperationWarehouse
    {
        $warehouseID = $this->data['warehouse_id'];

        return OperationWarehouse::firstOrNew(["warehouse_id" => $warehouseID]);

        // return new OperationWarehouse();
    }

    public static function getCompletedNotificationBody(Import $import): string
    {
        $body = 'Your equipment import has completed and ' . number_format($import->successful_rows) . ' ' . str('row')->plural($import->successful_rows) . ' imported.';

        if ($failedRowsCount = $import->getFailedRowsCount()) {
            $body .= ' ' . number_format($failedRowsCount) . ' ' . str('row')->plural($failedRowsCount) . ' failed to import.';
        }

        return $body;
    }
}
<?php

namespace App\Filament\Imports;

use App\Models\S3Operations\OperationWarehouse;
use Filament\Actions\Imports\ImportColumn;
use Filament\Actions\Imports\Importer;
use Filament\Actions\Imports\Models\Import;

class OperationWarehouseImporter extends Importer
{
    protected static ?string $model = OperationWarehouse::class;

    public static function getColumns(): array
    {
        return [
            ImportColumn::make('operation_id')
                ->integer()
                ->requiredMapping(),
            ImportColumn::make('warehouse_id')
                ->integer()
                ->requiredMapping(),
            ImportColumn::make('fuel')
                ->numeric(decimalPlaces: 2)
                ->requiredMapping(),
            ImportColumn::make('aircraft')
                ->requiredMapping(),
            ImportColumn::make('equipment')
                ->requiredMapping(),
        ];
    }

    public function resolveRecord(): ?OperationWarehouse
    {
        $warehouseID = $this->data['warehouse_id'];

        return OperationWarehouse::firstOrNew(["warehouse_id" => $warehouseID]);

        // return new OperationWarehouse();
    }

    public static function getCompletedNotificationBody(Import $import): string
    {
        $body = 'Your equipment import has completed and ' . number_format($import->successful_rows) . ' ' . str('row')->plural($import->successful_rows) . ' imported.';

        if ($failedRowsCount = $import->getFailedRowsCount()) {
            $body .= ' ' . number_format($failedRowsCount) . ' ' . str('row')->plural($failedRowsCount) . ' failed to import.';
        }

        return $body;
    }
}
The problem I am having is - the importer is throwing an error as follows: 
[2024-11-01 15:33:37] local.ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause' (Connection: mysql, SQL: update `operation_warehouse` set `fuel` = 750, `aircraft` = [{"airframe_id":"4","qty":"22"},{"airframe_id":"8","qty":"5"}], `equipment` = [{"equipment_id":"177","qty":"150"},{"equipment_id":"6","qty":"150"},{"equipment_id":"30","qty":"150"}], `operation_warehouse`.`updated_at` = 2024-11-01 15:33:37 where `` = 1 and `` = 1) {"userId":205358980568973312,"exception":"[object] (Illuminate\\Database\\QueryException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause' (Connection: mysql, SQL: update `operation_warehouse` set `fuel` = 750, `aircraft` = [{\"airframe_id\":\"4\",\"qty\":\"22\"},{\"airframe_id\":\"8\",\"qty\":\"5\"}], `equipment` = [{\"equipment_id\":\"177\",\"qty\":\"150\"},{\"equipment_id\":\"6\",\"qty\":\"150\"},{\"equipment_id\":\"30\",\"qty\":\"150\"}], `operation_warehouse`.`updated_at` = 2024-11-01 15:33:37 where `` = 1 and `` = 1) at C:\\Users\\patri\\Herd\\hvy\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php:825)
[2024-11-01 15:33:37] local.ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause' (Connection: mysql, SQL: update `operation_warehouse` set `fuel` = 750, `aircraft` = [{"airframe_id":"4","qty":"22"},{"airframe_id":"8","qty":"5"}], `equipment` = [{"equipment_id":"177","qty":"150"},{"equipment_id":"6","qty":"150"},{"equipment_id":"30","qty":"150"}], `operation_warehouse`.`updated_at` = 2024-11-01 15:33:37 where `` = 1 and `` = 1) {"userId":205358980568973312,"exception":"[object] (Illuminate\\Database\\QueryException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause' (Connection: mysql, SQL: update `operation_warehouse` set `fuel` = 750, `aircraft` = [{\"airframe_id\":\"4\",\"qty\":\"22\"},{\"airframe_id\":\"8\",\"qty\":\"5\"}], `equipment` = [{\"equipment_id\":\"177\",\"qty\":\"150\"},{\"equipment_id\":\"6\",\"qty\":\"150\"},{\"equipment_id\":\"30\",\"qty\":\"150\"}], `operation_warehouse`.`updated_at` = 2024-11-01 15:33:37 where `` = 1 and `` = 1) at C:\\Users\\patri\\Herd\\hvy\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php:825)
But the "warehouse_id" is 100% being set in the firstOrNew method... Thoughts?
3 Replies
Schilly
SchillyOP•3mo ago
Additionally - I have checked to ensure the warehouse_id is being set via dd($this->date['warehouse_id']); and this returns as expected an integer of the warehouse as listed in the imported CSV file. Also - I have tested to see if it works to straight up create "new" entries into the pivot table - this works 100% as expected without error. The only part that does not seem to work - is for the updating existing rows based on what is in the CSV Get the same error message when I try to do "update only" as directed in the docs as well
Schilly
SchillyOP•3mo ago
This was the code I used for the "update only" attempt: 
return OperationWarehouse::query()
            ->where('warehouse_id', $this->data['warehouse_id'])
            ->first();
return OperationWarehouse::query()
            ->where('warehouse_id', $this->data['warehouse_id'])
            ->first();
- Still throws the same error of cannot find the column ''. Here is afull stack trace error ouput - not sure if it will help or not (attached)

message.txt

Schilly
SchillyOP•3mo ago
Managed to get it to work - had to add a id column to the pivot table that increments. Not sure if this is intended behavior or not but seems to work fine now.

Did you find this page helpful?