F
Filament•2mo 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•2mo 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•2mo 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•2mo 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.
Want results from more Discord servers?
Add your server