Concatenating two fields into one column using format()

[dmsatparam]

Hi!

I have been trying to concatenate two fields into one column using the format() function for v2 of the datatable. However, I keep encountering an SQL error.

Code Block:

Column::make('Name')
    ->format(
        fn($value, $row, Column $column) => $row->first_name . ' ' . $row->last_name
    ),

Error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'leads.name' in 'field list'

[lrljoe]

Use a label method on the column if the "name" field doesn't exist.

[lrljoe]

I'm assuming that the "Customer" is your primary model?

In which case, do you get anything when you use:

Column::make("Customer")
            ->label(
                fn($row) => $row->first_name . ' ' . $row->last_name
            ),

You can definitely specify the following in your model and call it in a label.

    public function getFullNameAttribute()
    {
        return $this->first_name . ' ' . $this->last_name;
    }

However, if the first_name/last_name are not against your main model, then you'll need to stick the relationship in there too.

If you're not getting any values back by default, then it can be worthwhile adding the fields as additional selects in your configure()

            ->setAdditionalSelects(['users.first_name as first_name'])

[dmsatparam]

That is correct, Customer (Lead) is my primary model. I checked the contents of the $row variable and I came across 6 attribute. However, it does not contain 'first_name' nor 'last_name'. The attribute only has those six fields called in my columns function. Sharing with you the whole codebase of my TableComponent.

<?php

namespace App\Http\Livewire\Leads;

use Illuminate\Database\Eloquent\Builder;
use Rappasoft\LaravelLivewireTables\Views\Filters\SelectFilter;
use Rappasoft\LaravelLivewireTables\Views\Filters\DateFilter;
use Rappasoft\LaravelLivewireTables\DataTableComponent;
use Rappasoft\LaravelLivewireTables\Views\Column;
use App\Models\Lead;
use App\Models\LeadCategory;
use App\Models\LeadSource;

class Table extends DataTableComponent
{

    public array $categoryFilterOptions = [];
    public array $leadSourceFilterOptions = [];

    public function builder(): Builder
    {
        return Lead::query();
    }   
    public function configure(): void
    {
        $this->setPrimaryKey('id')
        ->setFilterLayout('slide-down')
        ->setQueryStringDisabled()
        ->setDefaultSort('created_at', 'desc');

        if(empty($this->categoryFilterOptions))
        {
            $this->categoryFilterOptions = LeadCategory::query()->get()->keyBy('description')->map(fn($category) => $category->description)->toArray();
        }

        if(empty($this->leadSourceFilterOptions))
        {
            $this->leadSourceFilterOptions = LeadSource::query()->get()->keyBy('description')->map(fn($leadSource) => $leadSource->description)->toArray();
        }

    }

    public function columns(): array
    {
        return [
            Column::make("Entry Date", "created_at")
                ->format(
                    fn($value, $row, Column $column) => date('m/d/Y', strtotime($row->created_at))
                )
                ->sortable(),

            Column::make("Lead No.", "id")
                ->searchable(),
            Column::make("Customer")
                ->label(
                fn($row) => dd($row->first_name)
            ),
            Column::make("Contact Person", "contact_person")
                ->searchable(),
            Column::make("Category", "category.description")
                ->searchable(),
            Column::make("Lead Source", "source.description")
                ->searchable(),
            
            Column::make("Assignee", "assigned_sales.name")
                ->searchable(),

        ];
    }

    public function filters(): array
    {
        return [
            DateFilter::make('Entry Date'),

            SelectFilter::make('Category')
            ->options(
                array_merge(
                    [
                        '' => 'All',
                    ],
                    $this->categoryFilterOptions
                )
            )
            ->filter(function(Builder $builder, string $value) {
                $builder->where('lead_categories.description', $value);
                
            }),
            SelectFilter::make('Assignee')
            ->options(
                array_merge(
                    [
                        '' => 'All',
                    ],
                )
            )
            ->filter(function(Builder $builder, string $value) {
                $builder->where('assignee', $value);
                
            }),
            SelectFilter::make('Source')
            ->options(
                array_merge(
                    [
                        '' => 'All',
                    ],
                    $this->leadSourceFilterOptions
                )
            )
            ->filter(function(Builder $builder, string $value) {
                $builder->where('lead_sources.description', $value);
                
            }),

        ];
    }


}

[dmsatparam]

Hi! Just wanted to say I have resolved this one through using the 'hideIf' helper. Not sure if it's efficient but I really needed the searchable functionalities.

 Column::make("First Name", "first_name")
                ->hideIf(true)
                ->searchable(),
            Column::make("Last Name", "last_name")
                ->hideIf(true)
                ->searchable(),
            Column::make('Customer')
            ->label(
                fn($row, Column $column) => $row->first_name . ' ' . $row->last_name
            )

Thanks for helping me!

[lrljoe]

Use the setAdditionalSelects to avoid having to do that! It adds the fields to the query.

[lrljoe]

In your configure method, use setAdditionalSelects, rather than creating a column and hiding it. Note that you may need to add the table name if its a non-unique name
e.g.

        $this->setPrimaryKey('id')
        ->setFilterLayout('slide-down')
        ->setQueryStringDisabled()
        ->setAdditionalSelects(['first_name','last_name']);
        ->setDefaultSort('created_at', 'desc');

Then you can just reference the field as you've done in your Customer column

  Column::make('Customer')
  ->label(
      fn($row, Column $column) => $row->first_name . ' ' . $row->last_name
  )

If you regularly need those two combined in that style, it may be worth using some of the more advanced MySQL features to create a dynamic field.

If this has resolved your issue, then please do mark the discussion as resolved/answered, as it does really help identify the outstanding discussions/issues :)