Crud: $this->crud->addClause produces empty result when variable is passed

[adrenaxus]

Bug report

What I did:

Fresh Backpack 3.6 Installation on PhP 7.2.22.

Added new route in routes/backpack/custom.php:
Route::get('users/town_id/{town_id}', 'UserCrudController@index');

My URL looks like this:
_https://example.com/admin/users/town_id/20_

Added fake users to database, this query produces >300 results (_just to emphasize that there are many users with town_id 20 in DB_):

SELECT * FROMusersWHERE town_id = 20

Now I added the following code to app/Http/Controllers/Admin/UserCrudController.php:

$town_id = \Route::current()->parameter('town_id'); //fetch town_id from url
$this->crud->addClause('where','town_id', $town_id); //get only users with matching town_id

What I expected to happen:

I expect the table to show only users with a matching town_id of 30. In the same fashion as this has always worked perfectly for me:

$user_id = backpack_user()->id;
$this->crud->addClause('where','user_id',$user_id);

What happened:

I get an empty result.

What I've already tried to fix it:

If I insert a static value instead of the $town_id variable here it works:

$this->crud->addClause('where','town_id', 20); //this works as expected, table only shows users with town_id of 20

if I add a dd between my code like this

$town_id = \Route::current()->parameter('town_id'); //get the town_id from url
dd($town_id); //check if town_id is set
$this->crud->addClause('where','town_id', $town_id); //get only users with matching town_id

It clearly shows that $town_id is indeed set and contains 20.

I have tried casting it to (int) or (string) but it doesnt change. I tried finding the problem for several days without success. I don't understand how such a behaviour is even possible. EIther I completely misunderstood something about the functionality of addClause or it is indeed some kind of weird bug...

I already opened an Issue (https://github.com/Laravel-Backpack/CRUD/issues/2022) which was closed, but after trying with a fresh Installation and only adding 3 lines of code I have the same problem now.

I want to emphasize again that if I want to show only models that belong to the currently logged in user, that works without any problems:

$user_id = backpack_user()->id;
$this->crud->addClause('where','user_id',$user_id);

Maybe I have to overwrite the index() function to accept an $id as parameter like this: index($id)...

Please give some kind of advice on how to fix this problem, as if I cant fix it backpack gets useless for me and our clients I really need to be able to add a simple Clause to my model and have been trying to achieve it for weeks now!

Backpack, Laravel, PHP, DB version:

Backpack 3.6, Laravel 5.8, Mysql 5.7.27

[tabacitu]

@adrenaxus , that's very odd indeed. What if you do this instead of addClause:

$this->crud->query = $this->crud->query->where('select', '=', $category_id);

Does this work for you?

[adrenaxus]

Thanks for your answer! Same problem with

$this->crud->query = $this->crud->query->where('page_id', '=', $category_id);

produces an empty result, but

$this->crud->query = $this->crud->query->where('select', '=', 30);

works as expected. Seems like as soon as I use a variable it doesnt work anymore....
I want to add that if I take the generated query with bindings using

dd($this->crud->query->toSql());
and
dd($this->crud->query->getBindings());

and manually execute the query it produces the correct result.

In my opinion the problem is in addClause function:

 public function addClause($function)
    {
        return call_user_func_array([$this->query, $function], array_slice(func_get_args(), 1));
    }

something with array_slice(func_get_args(), 1) destroys the passed variable? But why does this work then: $this->crud->addClause('where','user_id',backpack_user()->id);... I don't know...

I even tried to make my own helper function get_parameter which returns the URL parameter, so I can maybe use $this->crud->addClause('where','user_id',get_parameter()->id); but still no luck... same problem again.

[tabacitu]

That's exactly what I was trying to debug. Since using $this->query = xxx instead of addClause() has the same problem, it means the problem is most likely NOT with the addClause() method. We didn't use addClause, and the problem persisted.

[tabacitu]

I got it! I FINALLY fucking got it.

Pfiew. I can't imagine how much frustration this must have caused you 😄

Your problem is the AJAX datatables URL. It doesn't get the parameter, so it doesn't apply it. Basically:

• the /town_id/x route points to Controller::index()
• CrudController::index() just loads the view;
• the view tells DataTables to load the information from the url($crud->route.'/search').'?'.Request::getQueryString() route; which will NOT include your town_id parameter; because it uses $crud->route(), which is specified in your setup(); it does NOT include /town_id/x;

SOLUTION A

One way to get around this:

• instead of having a route for it, use a GET parameter: example.com/admin/users/?town_id=20
• you'll be able to get that parameter and apply things to search() from your setup() method:

        if (\Route::getCurrentRoute()->getActionMethod() == 'search' && \Request::has('town_id')) {
            $this->crud->addClause('where', 'select', \Request::get('town_id'));
        }

SOLUTION B

Overwrite the datatables_logic.blade.php file that Backpack provides, and replace $crud->route with Route::getCurrentRoute() or something of that nature - use Laravel's way of getting the current URL, instead of $this->crud. This will make your AJAX calls to the current URL (not the URL you defined for the CRUD with $this->crud->setRoute()).

---

I would recommend option 1, to be honest.

Boy, this was nasty one :-)) Let me know if this didn't fix it for you, and I'll reopen it.

Cheers!

[adrenaxus]

Thank you very much for providing this solution, can't thank you enough! I can get it to work now without problems using a GET parameter.

Cheers again and have good day!