Pagetables don't work with self join.

[asbator]

Pagetables don't work with self join. Below is query that works perfectly fine with Yaraja tables.

$cols = [
        Column::name('id')->title('Id')->sort()->searchable(),
        Column::name('nazwa')->title('Nazwa')->sort()->searchable(),
        Column::name('ulica_so')->title('Ulica So')->sort()->searchable(),
        Column::name('kod_so')->title('Kod So')->sort()->searchable(),
        Column::name('poczta_so')->title('Poczta So')->sort()->searchable(),
    ];

$query = Branch::leftJoin('branches as pow','branches.pow_id','=','pow.id')
                       ->leftJoin('branches as woj','branches.woj_id','=','woj.id')
                       ->select('branches.id',
                                'branches.nazwa',
                                'branches.ulica_so',
                                'branches.kod_so',
                                'branches.poczta_so',
                                'pow.nazwa as pow_nazwa',
                                'woj.nazwa as woj_nazwa');

$data = Pagetables::of($query)->columns($cols)->make(true);

This causes error:

Column 'id' in where clause is ambiguous

(SQL: select `branches`.`id`, `branches`.`nazwa`, `branches`.`ulica_so`, `branches`.`kod_so`, `branches`.`poczta_so`, `pow`.`nazwa` as `pow_nazwa`, `woj`.`nazwa` as `woj_nazwa` from `branches` left join `branches` as `pow` on `branches`.`pow_id` = `pow`.`id` left join `branches` as `woj` on `branches`.`woj_id` = `woj`.`id` where (`id` LIKE %% or `nazwa` LIKE %% or `ulica_so` LIKE %% or `kod_so` LIKE %% or `poczta_so` LIKE %%))

Problem is that column names are passed to WHERE part without table name.

I was trying to go around this problem trying to exclude id from searching but further problems turned out:

Column::name('id')->title('Id')->sort()->searchable(false);

ends with error
Unknown column '' in 'where clause'

(SQL: select `branches`.`id`, `branches`.`nazwa`, `branches`.`ulica_so`, `branches`.`kod_so`, `branches`.`poczta_so`, `pow`.`nazwa` as `pow_nazwa`, `woj`.`nazwa` as `woj_nazwa` from `branches` left join `branches` as `pow` on `branches`.`pow_id` = `pow`.`id` left join `branches` as `woj` on `branches`.`woj_id` = `woj`.`id` where (`` LIKE %% or `nazwa` LIKE %% or `ulica_so` LIKE %% or `kod_so` LIKE %% or `poczta_so` LIKE %%))

Column::name('id')->title('Id')->sort()->searchable(false)->raw(true);

ends with error
Savannabits\Pagetables\Pagetables::applySearch(): Argument #1 ($column) must be of type Savannabits\Pagetables\Column, null given,

I'm sorry for harrassing with issues. I'd love to help you but im currently working till late nights on deadline. Ill help with docs when ill finish.

[asbator]

I tried to add table names in the beginning of applySearch:

private function applySearch(Column $column, Builder &$q, $or = false)
    {
        // Apply Search to a depth of 3
        $searchParts = explode(".", $column->getSearchKey());
        $searchParts = $this->query->qualifyColumns($searchParts);

but it didnt end well, because now inserting joined colums doesnt work.

Column like so: Column::name('pow_nazwa')->title('pow_nazwa')->sort()->searchable(),
makes error: Unknown column 'branches.pow_nazwa' in 'where clause'

(SQL: select `branches`.`id`, `branches`.`nazwa`, `branches`.`ulica_so`, `branches`.`kod_so`, `branches`.`poczta_so`, `pow`.`nazwa` as `pow_nazwa`, `woj`.`nazwa` as `woj_nazwa` from `branches` left join `branches` as `pow` on `branches`.`pow_id` = `pow`.`id` left join `branches` as `woj` on `branches`.`woj_id` = `woj`.`id` where (`branches`.`id` LIKE %% or `branches`.`nazwa` LIKE %% or `branches`.`ulica_so` LIKE %% or `branches`.`kod_so` LIKE %% or `branches`.`poczta_so` LIKE %% or `branches`.`pow_nazwa` LIKE %% or `branches`.`woj_nazwa` LIKE %%))

If there is other way to get proper table name...

[asbator]

Experimenting with code and function calls i realized that im trying to do stuff the same way i do with Yajra DT.
Here i can get searching result (no selected data) that i need by simply adding relationship in columns instead of query.
So the right combination is:

        $cols = [
            Column::name('id')->title('ID')->sort()->searchable(),
            Column::name('nazwa')->title('Name')->sort()->searchable(),
            Column::name('woj.nazwa')->title('District')->sort()->searchable(),
            Column::name('pow.nazwa')->title('Province')->sort()->searchable(),
        ];

        $query = Branch::select('id','nazwa');
        $data = Pagetables::of($query)->columns($cols)->make(true);

For comparison in Yajra analogical would be such combination:

$columns = [
            Column::make("id")->className('all')->title('ID'),
            Column::make("nazwa")->className('all')->title('Name'),
            Column::make("pow_nazwa","pow.nazwa")->className('min-tablet-l')->title('District'),
            Column::make("woj_nazwa","woj.nazwa")->className('min-desktop')->title('Province'),
];
//Column::make 1st param identifies column in data qyery and can't have dot, 2nd param is used for counting query

$query = Branch::leftJoin('branches as pow','branches.pow_id','=','pow.id')
                       ->leftJoin('branches as woj','branches.woj_id','=','woj.id')
                       ->select('branches.id', branches.nazwa, 'pow.nazwa as pow_nazwa', 'woj.nazwa as woj_nazwa');

Maybe this will be helpful as examples in docs.

[coolsam726]

I know Pagetables is limited to only simple queries, I created the package to facilitate simple searching in CRUDs down to a relationship depth of 2 and did not consider complex queries like joins. Of course I can modify it to support such joins but it might take a bit of time given my current schedule.

A suggestion, maybe you could try and use Scout in conjunction with TNTSearch if you want to implement complex and fuzzy search on models. Checkout the Laravel TNTSearch Scout Driver. Here is a sample of how I use it in one of my projects:

public function search(Request $request): JsonResponse
    {
        $q = PurchaseOrder::query(); // Add your complex joins and other queries here
        if ($request->get("q")) {
            $q = PurchaseOrder::search($request->get("q"))->constrain($q); // Purchase order has to use the Searchable trait to support search.
        }
        $res = $q->paginate();
        return $this->api->success()->payload($res)->send();
    }

I have used it in my projects and I think it could be time to make it the official search driver for JIG. What do you think?