Custom text columns, calculated values, sum of related table values.

Hi, I have: Model: Booking

public function deals(): hasMany
{
    return $this->hasMany(Deal::class);
}

public function deals(): hasMany
{
    return $this->hasMany(Deal::class);
}

Model: Deal

public function booking(): belongsTo
{
    return $this->belongsTo(Booking::class);
}

public function booking(): belongsTo
{
    return $this->belongsTo(Booking::class);
}

My booking view shows this table (part of it). I have some questions, please read the comments.

Tables\Columns\ColumnGroup::make('Deals', [

    //Works as wanted, shows how many deals I have per booking.
    Tables\Columns\TextColumn::make('deals_count')
        ->counts('deals'),

    //Works as wanted, shows the price of each deal per booking
    Tables\Columns\TextColumn::make('deals.price')
        ->label('Each Deal Price')
        ->listWithLineBreaks()
        ->money('gbp', divideBy: 100),

    //How can I calculate the sum of all deals?
    Tables\Columns\TextColumn::make('???')
        ->label('All Deals Prices Sum')
        -???,

    //Works as wanted (different VAT values are stored in the DB)
    Tables\Columns\TextColumn::make('vat')
        ->label('VAT')
        ->suffix('%'),

    //How can I calculate the sum of all deals plus the value of the VAT?
    Tables\Columns\TextColumn::make('???')
        ->label('All Deals Prices Sum + VAT')
        ???,

])

Tables\Columns\ColumnGroup::make('Deals', [

    //Works as wanted, shows how many deals I have per booking.
    Tables\Columns\TextColumn::make('deals_count')
        ->counts('deals'),

    //Works as wanted, shows the price of each deal per booking
    Tables\Columns\TextColumn::make('deals.price')
        ->label('Each Deal Price')
        ->listWithLineBreaks()
        ->money('gbp', divideBy: 100),

    //How can I calculate the sum of all deals?
    Tables\Columns\TextColumn::make('???')
        ->label('All Deals Prices Sum')
        -???,

    //Works as wanted (different VAT values are stored in the DB)
    Tables\Columns\TextColumn::make('vat')
        ->label('VAT')
        ->suffix('%'),

    //How can I calculate the sum of all deals plus the value of the VAT?
    Tables\Columns\TextColumn::make('???')
        ->label('All Deals Prices Sum + VAT')
        ???,

])

Please, any advice is much appreciated, Thank you, Pablo

1 Reply

Pablo Torres•5mo ago

I ended with... works, if someone has any comment it will be much appreciated.

Tables\Columns\ColumnGroup::make('Deals', [
  Tables\Columns\TextColumn::make('deals_count')
      ->label('Count')
      ->wrapHeader()
      ->counts('deals'),
  Tables\Columns\TextColumn::make('deals.price')
      ->label('Price Each')
      ->wrapHeader()
      ->alignment(Alignment::End)
      ->listWithLineBreaks()
      ->money('gbp', divideBy: 100),
  Tables\Columns\TextColumn::make('Price Sum')
      ->label('Price Sum')
      ->state(function (Booking $record): int {
          return $record->deals->sum('price');
      })
      ->wrapHeader()
      ->alignment(Alignment::End)
      ->money('gbp', divideBy: 100),
  Tables\Columns\TextColumn::make('vat')
      ->label('VAT %')
      ->wrapHeader()
      ->alignment(Alignment::End)
      ->suffix('%'),
  Tables\Columns\TextColumn::make('VAT £')
      ->label('VAT £')
      ->state(function (Booking $record): int {
          $dealsPriceSum = $record->deals->sum('price');
          $vat = $record->vat;
          $totalSum = $dealsPriceSum * $vat / 100;
          return $totalSum;
      })
      ->wrapHeader()
      ->alignment(Alignment::End)
      ->money('gbp', divideBy: 100),
  Tables\Columns\TextColumn::make('Price Sum + VAT')
      ->label('Price Sum + VAT')
      ->state(function (Booking $record): int {
          $dealsPriceSum = $record->deals->sum('price');
          $vat = $record->vat;
          $totalSum = $dealsPriceSum + ($dealsPriceSum * $vat / 100);
          return $totalSum;
      })
      ->wrapHeader()
      ->alignment(Alignment::End)
      ->money('gbp', divideBy: 100),
])
  ->alignment(Alignment::Center)
  ->wrapHeader(),

Tables\Columns\ColumnGroup::make('Deals', [
  Tables\Columns\TextColumn::make('deals_count')
      ->label('Count')
      ->wrapHeader()
      ->counts('deals'),
  Tables\Columns\TextColumn::make('deals.price')
      ->label('Price Each')
      ->wrapHeader()
      ->alignment(Alignment::End)
      ->listWithLineBreaks()
      ->money('gbp', divideBy: 100),
  Tables\Columns\TextColumn::make('Price Sum')
      ->label('Price Sum')
      ->state(function (Booking $record): int {
          return $record->deals->sum('price');
      })
      ->wrapHeader()
      ->alignment(Alignment::End)
      ->money('gbp', divideBy: 100),
  Tables\Columns\TextColumn::make('vat')
      ->label('VAT %')
      ->wrapHeader()
      ->alignment(Alignment::End)
      ->suffix('%'),
  Tables\Columns\TextColumn::make('VAT £')
      ->label('VAT £')
      ->state(function (Booking $record): int {
          $dealsPriceSum = $record->deals->sum('price');
          $vat = $record->vat;
          $totalSum = $dealsPriceSum * $vat / 100;
          return $totalSum;
      })
      ->wrapHeader()
      ->alignment(Alignment::End)
      ->money('gbp', divideBy: 100),
  Tables\Columns\TextColumn::make('Price Sum + VAT')
      ->label('Price Sum + VAT')
      ->state(function (Booking $record): int {
          $dealsPriceSum = $record->deals->sum('price');
          $vat = $record->vat;
          $totalSum = $dealsPriceSum + ($dealsPriceSum * $vat / 100);
          return $totalSum;
      })
      ->wrapHeader()
      ->alignment(Alignment::End)
      ->money('gbp', divideBy: 100),
])
  ->alignment(Alignment::Center)
  ->wrapHeader(),

Gaming

Programming

Custom text columns, calculated values, sum of related table values.