How to set columns array on raw sql query with builder ?

[adishmuhammed]

Please point me the problem with the following code. I can't find any documentation on how to setup columns array on raw sql query with builder. here is what i am getting now :
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'event_registrations.unit' in 'field list'

here is my code

namespace App\Http\Livewire;

use Rappasoft\LaravelLivewireTables\DataTableComponent;
use Rappasoft\LaravelLivewireTables\Views\Column;
use App\Models\EventRegistration;

class DonateaContact extends DataTableComponent
{
    public function builder(): \Illuminate\Database\Eloquent\Builder
    {
        return EventRegistration::join('districts', 'event_registrations.district_id', '=', 'districts.id')
            ->join('zones', 'event_registrations.zone_id', '=', 'zones.id')
            ->join('units', 'event_registrations.unit_id', '=', 'units.id')
            ->join('events', 'event_registrations.event_id', '=', 'events.id')
            ->select(
                'units.name as unit',
                'zones.name as zone',
                'districts.name as district',
                'events.name as event',
                \DB::raw('(select count(*) from event_registrations as er where er.unit_id = units.id and er.donated_contact = "Y" and er.event_id = event_registrations.event_id) as numberOfDonors'),
                \DB::raw('(select count(*) from event_registrations as er join users as u on u.id = er.added_by where er.unit_id = u.unit_id and er.donated_contact = "Y" and er.event_id = event_registrations.event_id) as numberOfDonorsFormUnit')
            );
    }

    public function configure(): void
    {
        $this->setPrimaryKey('id');
    }

    public function columns(): array
{
    $columns = [
        Column::make("Unit", "unit"),
        Column::make("Zone", "zone"),
        Column::make("District", "district"),
        Column::make("Event", "event"),
        Column::make("Number Of Donated to Unit", "numberOfDonors"),
        Column::make("Number Of Donated form Unit", "numberOfDonorsFormUnit"),
    ];

    \Log::info('Columns:', $columns); // Log the columns array for debugging

    return $columns;
}
}

[lrljoe]

You should not need to do any joins in builder.
You should not need to add any counts using DB RAW

Can you share the relevant relationships for your model
EventRegistration

And the related field(s) that you want to share please