justjigga Posts - Answer Overflow

justjigga

•Created by justjigga on 7/22/2024 in #❓┊help

How can I use summarize to add the sum of a calculated column in a table footer?

I am using getStateUsing to calculate the number of hours a client uses for a specific period of time. I would like to add the sum of this value to the table footer.

Tables\Columns\TextColumn::make('hours_used')
    ->getStateUsing(function (Client $record) {
       return $record->timelogs()
                      ->select(
                            DB::raw('DAY(timelogs.created_at), (CEIL(( SUM(timelogs.total_seconds) /3600) / 0.25) * 0.25) as billable' )
                            )
                            ->whereBetween('timelogs.created_at', [$this->startDate, $this->endDate])
                            ->groupBy(DB::raw('client_id, ticket_id, DAY(timelogs.created_at)'))->get()->sum('billable');
                    })
    ->summarize(Sum::make()->using(fn (Builder $query, Client $record): float => $query->sum(
       $record->timelogs()->select(
                            DB::raw('DAY(timelogs.created_at), (CEIL(( SUM(timelogs.total_seconds) /3600) / 0.25) * 0.25) as billable' )
                        )
                        ->whereBetween('timelogs.created_at', [$this->startDate, $this->endDate])
                        ->groupBy(DB::raw('client_id, ticket_id, DAY(timelogs.created_at)'))->get()->sum('billable')
                    ) )),

Tables\Columns\TextColumn::make('hours_used')
    ->getStateUsing(function (Client $record) {
       return $record->timelogs()
                      ->select(
                            DB::raw('DAY(timelogs.created_at), (CEIL(( SUM(timelogs.total_seconds) /3600) / 0.25) * 0.25) as billable' )
                            )
                            ->whereBetween('timelogs.created_at', [$this->startDate, $this->endDate])
                            ->groupBy(DB::raw('client_id, ticket_id, DAY(timelogs.created_at)'))->get()->sum('billable');
                    })
    ->summarize(Sum::make()->using(fn (Builder $query, Client $record): float => $query->sum(
       $record->timelogs()->select(
                            DB::raw('DAY(timelogs.created_at), (CEIL(( SUM(timelogs.total_seconds) /3600) / 0.25) * 0.25) as billable' )
                        )
                        ->whereBetween('timelogs.created_at', [$this->startDate, $this->endDate])
                        ->groupBy(DB::raw('client_id, ticket_id, DAY(timelogs.created_at)'))->get()->sum('billable')
                    ) )),

I think I'm missing something. The error I get is

SQLSTATE[42S22]: Column not found: 1054 Unknown column '3.25' in 'field list'
SELECT sum(`3`.`25`) AS aggregate FROM (SELECT * FROM `clients` WHERE (EXISTS (SELECT * FROM `timelogs` INNER JOIN `tickets` ON `tickets`.`id` = `timelogs`.`ticket_id` WHERE `clients`.`id` = `tickets`.`client_id` AND `timelogs`.`created_at` BETWEEN 2024-07-01 AND 2024-07-31 23:59:59 AND `timelogs`.`deleted_at` IS NULL AND `tickets`.`deleted_at` IS NULL)) AND `clients`.`deleted_at` IS NULL ORDER BY `name` ASC) AS `clients`

SQLSTATE[42S22]: Column not found: 1054 Unknown column '3.25' in 'field list'
SELECT sum(`3`.`25`) AS aggregate FROM (SELECT * FROM `clients` WHERE (EXISTS (SELECT * FROM `timelogs` INNER JOIN `tickets` ON `tickets`.`id` = `timelogs`.`ticket_id` WHERE `clients`.`id` = `tickets`.`client_id` AND `timelogs`.`created_at` BETWEEN 2024-07-01 AND 2024-07-31 23:59:59 AND `timelogs`.`deleted_at` IS NULL AND `tickets`.`deleted_at` IS NULL)) AND `clients`.`deleted_at` IS NULL ORDER BY `name` ASC) AS `clients`

2 replies

FFilament

•Created by justjigga on 7/10/2024 in #❓┊help

Use ImportAction to import records and related records

I am using ImportAction to import a list of registrants and addresses. Each registrant can have a physical address and mailing address. The csv file looks something like: firstname,lastname,phys_address,phys_city,phys_state,mail_address_mail_city,mail_state,yob. My tables look like: firstname,lastname,phys_address_id,mail_address_id,yob The documentation shows how to use relationships for lookups of existing relationships but I'm not certain how to create new ones. During the import process, I would like to create new addresses if the registrant does not exist, or update the addresses if the registrant does exist. I know I can do this without using ImportAction but I like the ability to map columns during the import process. Thanks!

3 replies

Gaming

Programming