Problems with Search

[benhe84]

I'm Using Codeigniter 4.1.1 with Tablesigniter.

In my Model I use the Builder to filter Data before. For example

public function frontTable(): BaseBuilder
    {
        $builder = $this->db->table($this->table);
        $builder->where('status', 1);
        return $builder;
    }

When the Table is loaded filtering works and only entries where status =1 are shown. If I use the search, all entries are shown. Please help me.

[benhe84]

I solved the Problem. Surround the foreach ($this->searchLike as $key => $field) in the function extraConfig with $bui->groupStart(); and $bui->groupEnd(); so that it looks like:

if(!empty($_POST["search"]["value"])){ $bui->groupStart(); foreach ($this->searchLike as $key => $field) { if($key == 0){ $bui->like($field,$_POST["search"]["value"]); }else{ $bui->orLike($field,$_POST["search"]["value"]); } } $bui->groupEnd(); } Then only Entries from the Builder are shown.

[monkenWu]

Hi there !

Can you provide part of your configuration inside your Controller relating to the TableIgniter? This really helps me debug.

According to your question, I tried to revert the possible situation. Take the sample codes as an example:

controller

public function fullTable(){
    $model = new HomeModel();
    $table = new TablesIgniter();
    $table->setTable($model->noticeTable())
          ->setDefaultOrder("id","DESC")
          ->setSearch(["title","date"])
          ->setOrder([null,null,"date"])
          ->setOutput([$model->button(),"title","date"]);
    return $table->getDatatable();
}

Model

public function noticeTable(){
    $builder = $this->db->table("news")
                    ->where('date','2020-01-04');
    return $builder;
}

According to the codes above, the builder inside noticeTable will only illustrate the date content as 2020-01-04. But we will encounter a question here, if we input any string in the search funcition key of the Datatables, the where query set inside the noticeTable will become invalid.

The cause of this question is the configuration of the Controller have confilct with the builder. Lets take a look at the 6th line of the sample Controller: ->setSearch(["title", "date"]).

In the TablesIgniter, the builder you transmit will become the foundation of all SQL Query. If you have already defined a rule that can filter some field with Where statement inside the builder, then the field should not be seen in the configuration of setSearch() of the TablesIgniter.

If we revise the ->setSearch(["title","date"]) inside the sample program into ->serSearch(["title"]), you will find out that everything works normally.

[benhe84]

But we will encounter a question here, if we input any string in the search funcition key of the Datatables, the where query set inside the noticeTable will become invalid.

That was the Problem! This Problem can be Prevented by changing the TablesIgniter.php

TablesIngiter.php

Edit the function extraConfig Surround the foreach ($this->searchLike as $key => $field) in the with $bui->groupStart(); and $bui->groupEnd(); so that it looks like:

if(!empty($_POST["search"]["value"])){
   $bui->groupStart();
   foreach ($this->searchLike as $key => $field) {
      if($key == 0){
         $bui->like($field,$_POST["search"]["value"]);
      }
      else{
        $bui->orLike($field,$_POST["search"]["value"]); 
      }
   }    
   $bui->groupEnd();
   }

If you surround the forech with a groupStart() and a groupEnd() the query inside the notice Table keeps valid.

[monkenWu]

OK,I get it! I will fix this problem in the next version. Thank you for your feedback!

[monkenWu]

Hi, you can update to the v1.2.0 version, which fixes the above problem.