[2.0] Struggling to get a relationship count to work.

[xitude]

Would like to be able to sort by recipients_count im using the following code;

<?php

namespace App\Http\Livewire\Campaign;

use Illuminate\Database\Eloquent\Builder;
use Rappasoft\LaravelLivewireTables\DataTableComponent;
use Rappasoft\LaravelLivewireTables\Views\Column;
use App\Models\Campaign\Campaign;
use Rappasoft\LaravelLivewireTables\Views\Columns\LinkColumn;

class CampaignTable extends DataTableComponent
{
    protected $model = Campaign::class;

    public function configure(): void
    {
        $this->setPrimaryKey('id');
        $this->setDefaultSort('id', 'desc');
        $this->setRefreshTime(10000);
        $this->setRefreshVisible();
        $this->setEmptyMessage('No Campaigns Found');
    }

    public function columns(): array
    {
        return [
            LinkColumn::make('Name', 'name')
                ->title(fn($row) => $row->name)
                ->location(fn($row) => route('campaigns.show', $row->uuid))
                ->attributes(function($row) {
                    return [
                        'class' => 'rounded-full',
                        'alt' => $row->name . ' Avatar',
                    ];
                }),
            Column::make('Recipients', 'recipients_count')
                ->format(function($value, $row, Column $column) {
                    return $row->recipients_count;
                })
                ->sortable()
                ->setSortingPillTitle('Recipients'),
            Column::make('Creator', 'creator_id')
                ->format(function($value, $row, Column $column) {
                    return $row->creator->first_name . ' ' . $row->creator->last_name;
                })->eagerLoadRelations(),
            Column::make("Created at", "created_at")
                ->sortable()
        ];
    }

    public function builder(): Builder
    {
        return Campaign::with(['creator', 'recipients'])
            ->where('merchant_id', auth()->user()->current_merchant_id)
            ->where('archived_at', '!=', null)
            ->withCount('recipients');
    }
}

Getting the following error;

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'campaigns.recipients_count' in 'field list' (SQL: select `campaigns`.*, (select count(*) from `recipients` inner join `campaign_recipients` on `recipients`.`id` = `campaign_recipients`.`recipient_id` where `campaigns`.`id` = `campaign_recipients`.`campaign_id` and `recipients`.`deleted_at` is null) as `recipients_count`, `campaigns`.`recipients_count` as `recipients_count`, `campaigns`.`creator_id` as `creator_id`, `campaigns`.`created_at` as `created_at` from `campaigns` where `merchant_id` = 1 and `archived_at` is not null and `campaigns`.`deleted_at` is null order by `id` desc limit 10 offset 0)

Just a little confused about it as this normally would get me back the count, even if I chained ->get() on the builder function. Clearly missing something. (would be good to get a doc section for counting relations)

[dhruva81]

I am also facing the same issue.

[dhruva81]

Please try this

   Column::make('Recipients')
          ->label(function ($row) {
               return $row->recipients_count;
          }),

[xitude]

Sorry for the delay in trying this, this resolved my issue.

[dragonplunker]

how to get it work with sorting?

[DanielGSoftware]

After struggling with this for a couple of hours, I found a hacky solution to make it work. Let me explain:

Let's say you have a Post model that has comments, and you'd like to show the amount of comments in the table.
Assuming you added a withCount('comments') in your query, you'd do Column::make('Comments', 'comment_count').

The query will look like this:

select
  `posts`.*,
  (
    select
      count(*)
    from
      `comments`
    where
      `post`.`id` = `comment`.`post_id`
  ) as `comments_count`,
  `posts`.`comments_count` as `comments_count` #this part errors
from
  `posts`

The first part is withCount('comments').
The second part that errors is added by the package itself when you added comment_count as the second argument for Column::make(), which makes sense because we don't have a comments_count on posts.

So what we need to do is to alter this part to make the query not error, and that we add a custom sortable() that sorts on comments_count.

Column::make('Comments', 'id')  // <--- 'id' (or anything else) instead of 'comments_count'
     ->sortable(fn (Builder $builder, string $direction) => $builder->orderBy('comments_count', $direction))

This works, and you can now sort on the comments count.

[shiv122]

why cant just make table name option when accessing columns . give user access to specify the table name in case of conflict .

[lrljoe]

Set the tableName in the component by using:

public string $tableName = 'WhateverYouLike';

[shiv122]

is was talking about when u define columm name the query creates something like this table.columnName

[lrljoe]

Please can you give me an example of the issue, perhaps a screenshot of the columns, and a paste of the query.

[shiv122]

here's an example there are no table named order but i have a relationship order it should pull from collection instead of query like yazra datatable

[lrljoe]

The reason that it doesn't do that by default is for the builderless approach where it calculates which columns it should retrieve.

If you use setAdditionalSelects in your configure() method, and add the column in there, then it should work as you'd expect, but if not then please create a new issue for this, and I'll take a closer look at what is going on and give you a solution
Just spotted the age of this original issue that you've added this onto!