Search (smart?) issue with primary key ids and LIKE operator

[alekth85]

Hello,

So I have a problem, not sure if it's my lack of understanding how this works, or is it a problem with the lib itself. The problem is with search. Search works, but it's all queried (seemingly) with operator LIKE and not = I have custom search fields on a page, and jquery listens to on change event for those input boxes or dropdowns, and then calls the datatable search logic (code below). Searching for titles work fine, but searching for primary key ids does not, because it pretty much does this always:

SELECT * FROM table WHERE id LIKE '%2%' which returns the correct result, plus everything else that has a "2" in the id field, which is completely wrong. It's ID field, the operator has to be =

Specifically this issue is for country list dropdown. Values are primary key ids. That's what we send to laravel.

1. I make server side requests with JS to a controller that then does a query call on a Partner model, and finishes with a ->get();
2. After that, I have some other data that I collect from other sources, and add that to the collection, with ->map();
3. And then I call DataTables::collection($result), editing some columns and finally, i return JSON to the JS.

Datatable init:

function getList() {
        datatable = $('#partner_list').DataTable({
            fixedHeader: {headerOffset: 122},
            "processing": true,
            "serverSide": true,
            "searching": true,
            "dom": 'lrt',
            "iDisplayLength": -1,
            "serverMethod": "post",
            "ajax": {
                "url": "/v2/api/partners/list",
                "headers": {
                    "Authorization": "Bearer " + readCookie("access_v2_token")
                },
            },
            "columns": [
                { data: 'Country_Title', "name": "Country_ID" },
                { data: 'City_Title' },
                { data: 'Partner_Title' },
                { data: 'Partner_Type'},
                { data: 'Partner_Branch_Currency' },
                { data: 'HQ_Phone' },
                { data: 'HQ_Email' },
                { data: 'Is_Active' },
            ],
            drawCallback : function() {
                // pageInfo(this.api().page.info());
            },
            rowCallback: function (row, data) {
                // Add id
                $(row).attr("data-id", data['Partner_ID']);

                if (data['Is_Active'] == 0) {
                    console.log("Adding grey");
                    $(row).addClass("d-red");
                }
            }
        });
    }

Search in JS:

 $(document).on('change', '#country_list', function () {
        datatable.column('Country_ID:name').search($(this).val()).draw();
    });

Controller:

public function listPartners(Request $request)
    {
        $queryPartners = Partner::query()
            ->leftJoin("Partner_Branch", function ($j) {
                $j->on("Partner_Branch.Partner_ID", "=", "Partner.Partner_ID")
                    ->where("Partner_Branch.Type", "=", "HQ");
            })
            ->leftJoin("Partner_Branch AS pb2", "pb2.Partner_ID", "=", "Partner.Partner_ID")
            ->leftJoin("Country", "Country.Country_ID", "=", "Partner_Branch.Country_ID")
            ->leftJoin("City", "City.City_ID", "=", "Partner_Branch.City_ID")
            ->whereNull("Partner.Is_Deleted")
            ->with("types")
            ->where("Partner.Is_Active", "=", 1)
            ->orderBy("Country.Country_Title")
            ->groupBy("Partner.Partner_ID")
            ->select(
                "Partner.*",
                "Country.Country_ID",
                "Country.Country_Title",
                "City.City_Title",
                "Partner_Branch.Currency AS Partner_Branch_Currency",
                "Partner_Branch.Partner_Branch_ID AS pbcid2",
                "pb2.Partner_Branch_ID AS pbcid"
            )
            ->get();


        $queryPartners->map( function($row) {
            $row['Contact_Messenger_Service'] = "";
            $row['Contact_Phone_Country'] = "";
            $row['Contact_Messenger'] = "";
            $row['Contact_Phone'] = "";
            $row['HQ_Phone'] = "";
            $row['HQ_Email'] = "";

            $data = $this->getContacts($row['pbcid']);

            $data->each(function ($col) use($row) {
                if (strtolower($col->Communication_Type) == 'phone') {
                    $row['Contact_Phone'] = $col->Contact_Entry;
                    $row['HQ_Phone'] = $col->Contact_Entry;
                    $row['Contact_Phone_Country'] = $col->Phone_Country;
                }

                if (strtolower($col->Communication_Type) == 'e-mail') {
                    $row['Contact_Email'] = $col->Contact_Entry;
                    $row['HQ_Email'] = $col->Contact_Entry;
                }

                if (strtolower($col->Communication_Type) == 'messenger') {
                    $row['Contact_Messenger'] = $col->Contact_Entry ?? "";
                    $row['Contact_Messenger_Service'] = $col->Messenger_Type ?? 'N/A';
                }
            });
        });

        $data = DataTables::collection($queryPartners)
            ->editColumn('Partner_Title', function($row) {
                return '<a href="/'. $row['Partner_ID'] .'">' . $row['Partner_Title'] . '</a>';
            })
            ->editColumn('Partner_Type', function ($row) {
                return $row['types']['Title'] ?? "";
            })
            ->editColumn('Is_Active', function ($row) {
                if ($row['Is_Active'] == 1) {
                    $ret = "Active";
                } else {
                    $ret = "Inactive";
                }

                return $ret;
            })
            #->filter(function ($query) use ($request) {
            #    $columns = $request->get("columns");
            #    foreach ($columns as $key => $val) {
            #        if ($val['data'] == "Country_Title") {
            #            $query->where('Country.Country_ID', '=', $val['search']['value']);
            #        }
            #    }
            #})
            ->rawColumns(["Partner_Title"])
            ->toJson();

        return $data;
    }

Turning off smart search in the datatables.php config file does not do anything.

[yajra]

You can use filterColumn to make it an exact search or what you see fit.

[yajra]

Also, I suggest you use a query if possible. The collection is not full-featured yet, some features may not work.

[alekth85]

Hello. Yes, that's what I used (filterColumn), and I re-arranged the code so it does

1. Query
2. Datatables::eloquent (editing columns, and filtering), instead of Datatables::collection, returns toArray
3. foreach over the resultset, adding more data
4. Convert to collection, and call toJson method and return

That's the combination that worked for me. Here's the code:

$queryPartners = Partner::query()
            ->leftJoin("Partner_Branch", function ($j) {
                $j->on("Partner_Branch.Partner_ID", "=", "Partner.Partner_ID")
                    ->where("Partner_Branch.Type", "=", "HQ");
            })
            ->leftJoin("Partner_Branch AS pb2", "pb2.Partner_ID", "=", "Partner.Partner_ID")
            ->leftJoin("Country", "Country.Country_ID", "=", "Partner_Branch.Country_ID")
            ->leftJoin("City", "City.City_ID", "=", "Partner_Branch.City_ID")
            ->whereNull("Partner.Is_Deleted")
            ->with("types")
            ->orderBy("Country.Country_Title")
            ->groupBy("Partner.Partner_ID")
            ->select(
                "Partner.*",
                "Country.Country_ID",
                "Country.Country_Title",
                "City.City_Title",
                "Partner_Branch.Currency AS Partner_Branch_Currency",
                "Partner_Branch.Partner_Branch_ID AS pbcid2",
                "pb2.Partner_Branch_ID AS pbcid"
            );

        $data = DataTables::eloquent($queryPartners)
            ->editColumn('Partner_Title', function($row) {
                return '<a href="/'. $row['Partner_ID'] .'">' . $row['Partner_Title'] . '</a>';
            })
            ->editColumn('Partner_Type', function ($row) {
                return $row['types']['Title'] ?? "";
            })
            ->editColumn('Is_Active', function ($row) {
                if ($row['Is_Active'] == 1) {
                    $ret = "Active";
                } else {
                    $ret = "Inactive";
                }

                return $ret;
            })
            ->filterColumn("Partner_Title", function ($query, $keyword) {
                $query->where('Partner.Partner_Title', 'LIKE', '%' . $keyword . '%');
            })
            ->filterColumn("City.City_Title", function ($query, $keyword) {
                $query->where('City.City_Title', 'LIKE', '%' . $keyword . '%');
            })
            ->filter(function ($query) use ($request) {
                $columns = $request->get("columns");
                foreach ($columns as $key => $val) { 
                    if ($val['data'] == "Is_Active") {
                        if ($val['search']['value'] == "") {
                            $query->where('Partner.Is_Active', '=', 1);
                        }
                    }
                }
            })
            ->rawColumns(["Partner_Title"])->smart(false)
            ->toArray();

        foreach ($data['data'] as $key => $val) {

            $data['data'][$key]['Contact_Messenger_Service'] = "";
            $data['data'][$key]['Contact_Phone_Country'] = "";
            $data['data'][$key]['Contact_Messenger'] = "";
            $data['data'][$key]['Contact_Phone'] = "";
            $data['data'][$key]['HQ_Phone'] = "";
            $data['data'][$key]['HQ_Email'] = "";

            $cData = $this->getContacts($val['pbcid']);

            $cData->each(function ($col) use ( &$data, $key ) {
                if (strtolower($col->Communication_Type) == 'phone') {
                    $data['data'][$key]['Contact_Phone'] = $col->Contact_Entry;
                    $data['data'][$key]['HQ_Phone'] = $col->Contact_Entry;
                    $data['data'][$key]['Contact_Phone_Country'] = $col->Phone_Country;
                }

                if (strtolower($col->Communication_Type) == 'e-mail') {
                    $data['data'][$key]['Contact_Email'] = $col->Contact_Entry;
                    $data['data'][$key]['HQ_Email'] = $col->Contact_Entry;
                }

                if (strtolower($col->Communication_Type) == 'messenger') {
                    $data['data'][$key]['Contact_Messenger'] = $col->Contact_Entry ?? "";
                    $data['data'][$key]['Contact_Messenger_Service'] = $col->Messenger_Type ?? 'N/A';
                }
            });
        }

        return collect($data)->toJson();

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity.