I have a backend list with quite complex filter scope with HAVING. For this reason, I had to turn off the showPageNumbers: false otherwise the query gives error trying to paginate: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'column_name' in 'having clause' (SQL: select count(*) as aggregate from users .... (the rest of the query)". My problem now is, I really need to know how many records are in the results of the query. I tried to manipulate the listRender() function in index.htm, but no success. Any idea? Do you have a fix for the Having issue? How can I get the query counts?

Thank you Sylwia

@Sylwia, What are you trying to achieve?

You can always just call ->count(); on the query to know exactly how many results there are.

Thank you for your reply.

"@Sylwia, What are you trying to achieve?" -> As above, I turned off 'showPageNumbers: false' (because of this issue: https://github.com/octobercms/october/issues/4768) and now I am trying to get the number of results of the query in a different way.

"You can always just call ->count(); on the query to know exactly how many results there are." -> Yes, but how I can get the call or queryBuilder in Controller say in index() function?

Last updated 4 years ago

Take a look at the docs for the listExtendQuery function. You could always set a variable you could use in the index function.

https://octobercms.com/docs/backend/lists#extend-model-query

I did already. It extends the lookup query. I don't want to extend it, the query works fine but only if the pagination is off (showPageNumbers: false). Is there a way to get in the index() in controller class a variable, ex; $this->vars['nr_results'] = <number of resutls of the lookup query>

Thanks for your help.

The controller class is just a php class. You can easily count the results in the query and save it to a variable in the controller. Let’s say $this->countRecords = $query->count().

Then just use the $this->countRecords variable in the index method.

Take a look at this file: /modules/backend/widgets/Lists.php. There should be a $records variable available so I think you can do the following:

$recordsCount = $this->vars[‘records’]->count();

Thanks again. Still no success in getting the number of results of the lookup query. The list widget has a variable $records and $this->vars[‘records’], but non of them is available in the Controller class or I am doing something wrong. Could you please paste a link to an example?

Last updated 4 years ago

I found another solution. I wasn't able to get the number of results of the lookup query when using the List behaviour. I would have to build the views without the behaviour.

After all I modified my scope so that HAVING don't go to the pagination query.

*** in the model scope function
public function scopeStatus($query, $filter){
   $queryClone = clone $query;
   $middleData = $queryClone->havingRaw()->get(); //complex query with group and having
   $modelRecordsIds = [];
   foreach($middleData as $oneRecord){
      array_push($modelRecordsIds, oneRecord->id);
   }   
   $query->whereIn('model.id',$modelRecordsIds);
   return $query;
}

Last updated 4 years ago