Filter and search configuration of relationship tables

[ali-d-coded]

How can I configure the filter and search of a table having so many relations? I'm getting an error SQL saying value ambiguous. Nothing works, search, sort, nothing!!!
Please help me, guys. Anybody, please explain how it's done. It'll be so much helpful If I get an example of how it's done.

[dansysanalyst]

Hi @ali-d-coded,

Could you please provide us with some more details, so we can try to assist you better?

• What are your tables and relationships?
• What are you trying to accomplish?
• What is the result you get?

😊

[ali-d-coded]

` <?php

namespace App\Http\Livewire;

use App\Models\DataCardOrder;
use Illuminate\Support\Carbon;
use Illuminate\Database\QueryException;
use Illuminate\Database\Eloquent\Builder;
use PowerComponents\LivewirePowerGrid\Button;
use PowerComponents\LivewirePowerGrid\Column;
use PowerComponents\LivewirePowerGrid\PowerGrid;
use PowerComponents\LivewirePowerGrid\PowerGridEloquent;
use PowerComponents\LivewirePowerGrid\PowerGridComponent;
use PowerComponents\LivewirePowerGrid\Traits\ActionButton;
use PowerComponents\LivewirePowerGrid\Rules\Rule;

final class DataCardsReport extends PowerGridComponent
{
use ActionButton;

//Messages informing success/error data is updated.
public bool $showUpdateMessages = true;

/*
|--------------------------------------------------------------------------
|  Features Setup
|--------------------------------------------------------------------------
| Setup Table's general features
|
*/
public function setUp(): void
{
    $this->showCheckBox()
        ->showPerPage()
        ->showSearchInput()
        ->showExportOption('download', ['excel', 'csv']);
}

/*
|--------------------------------------------------------------------------
|  Datasource
|--------------------------------------------------------------------------
| Provides data to your Table using a Model or Collection
|
*/

/**
* PowerGrid datasource.
*
* @return  \Illuminate\Database\Eloquent\Builder<\App\Models\User>|null
*/
public function datasource(): ?Builder
{
    return DataCardOrder::query()->join('users', 'users.id', '=', 'data_card_orders.user_id')
     ->join('data_card_order_plans', 'data_card_order_plans.order_id', '=', 'data_card_orders.id')
    ->join('data_card_order_cards', 'data_card_order_cards.order_plan_id', '=', 'data_card_order_plans.id')
    ->join('data_card_plans', 'data_card_plans.id', '=', 'data_card_order_plans.plan_id')
    ->select('data_card_orders.*',
    // 'data_card_orders.created_at as date',
     'users.name as user_name',
    //  'data_card_order_plans.quantity as qty',
     'data_card_plans.code as code',
     'data_card_plans.wsp as wsp',
     'data_card_plans.srp as srp')->distinct();
}

/*
|--------------------------------------------------------------------------
|  Relationship Search
|--------------------------------------------------------------------------
| Configure here relationships to be used by the Search and Table Filters.
|
*/

/**
 * Relationship search.
 *
 * @return array<string, array<int, string>>
 */
public function relationSearch(): array
{
    return []; // I want to know how to configure this part?? I could,nt find it in the documentation
}

/*
|--------------------------------------------------------------------------
|  Add Column
|--------------------------------------------------------------------------
| Make Datasource fields available to be used as columns.
| You can pass a closure to transform/modify the data.
|
*/
public function addColumns(): ?PowerGridEloquent
{
    // dd($this->datasource());

    return PowerGrid::eloquent()
        ->addColumn('id')
        ->addColumn('order_id')
        ->addColumn('user_name')
        ->addColumn('total_amount')
        // ->addColumn('qty')
        ->addColumn('code')
        ->addColumn('wsp')
        ->addColumn('srp')
        ->addColumn('date', function(DataCardOrder $model) {
            return Carbon::parse($model->created_at)->format('d/m/Y H:i:s');
        })
        ->addColumn('updated_at_formatted', function(DataCardOrder $model) {
            return Carbon::parse($model->updated_at)->format('d/m/Y H:i:s');
        });
}

/*
|--------------------------------------------------------------------------
|  Include Columns
|--------------------------------------------------------------------------
| Include the columns added columns, making them visible on the Table.
| Each column can be configured with properties, filters, actions...
|
*/

 /**
 * PowerGrid Columns.
 *
 * @return array<int, Column>
 */
public function columns(): array
{
    return [
        Column::add()
            ->title('ID')
            ->field('id')
            ->makeInputRange(),

        Column::add()
            ->title('Order Id')
            ->field('order_id')
            ->sortable()
            ->searchable()
            ->makeInputText(),

        Column::add()
            ->title('Retailer')
            ->field('user_name'),
            // ->makeInputRange(),

    

        Column::add()
            ->title('Plan Code')
            ->field('code')
            ->makeInputRange(),

        Column::add()
            ->title('Purchase price')
            ->field('wsp')
            ->makeInputRange(),

        Column::add()
            ->title('Sales Price')
            ->field('srp')
            ->makeInputRange(),

        Column::add()
            ->title('Total Amount')
            ->field('total_amount')
            ->sortable()
            ->searchable(),

        Column::add()
            ->title('Date')
            ->field('date', 'created_at')
            ->searchable()
            ->sortable()
            // ->makeInputDatePicker('created_at'),

      
    ];
}

}
`

This is my powergrid class, I'm combining multiple database tables to form this datatable, so the search and sort or any filter option are'nt working!!!, whenever I try to do any sort or searching I get this error

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'order_id' in where clause is ambiguous (SQL: select count(*) as aggregate from data_card_orders inner join users on users.id = data_card_orders.user_id inner join data_card_order_plans on data_card_order_plans.order_id = data_card_orders.id inner join data_card_order_cards on data_card_order_cards.order_plan_id = data_card_order_plans.id inner join data_card_plans on data_card_plans.id =

how can I solve this?? please help me, I'm stuck.

[dansysanalyst]

Material on relationSearch:

📚 Documentation: https://livewire-powergrid.com/table/column-filters.html#filter-by-relationship

📖 Example: https://github.com/Power-Components/powergrid-demo/blob/main/app/Http/Livewire/DishesTable.php

[BenOussama180]

u knoow what, let me create a repo for this specific case and i'll put the link here give me 10min

[luanfreitasdev]

I believe this PR can also help #864

[BenOussama180]

surprisignly , it works fine in the demo project xDD
repo link : https://github.com/BenOussama180/poweregrid-test-case

i got no idea why its not working on my project, mybe i should update poowergrid tolatest version i am using 3.5.13

[luanfreitasdev]

Hello @BenOussama180 . I downloaded your project and it's really working

[BenOussama180]

yea i still cant figure out why its not working on my project , ill try updating powergrid maybe it'll fix