List with join and grouping gives error in count query because of selecting everything

[adriannuta]

Here's the situation: 2 tables, one has many from the another (lets' say posts and comments).

In the posts list you have filters that are applied on the comment, so you're going to use whereHas clauses. The problem with whereHas (which creates a WHERE EXISTS) is that it gets slow when you have millions of entries, especially when the filtered result set in the comments is large (thing filtering by comment type). The alternative to EXISTS is to JOIN instead.

Since the JOIN will generate you more rows (duplicates) in the posts result set, you will want to group by the posts primary key. A problem in Backpack arise in the inner query that calculates the count:

$minimumColumns = ($crudQuery->groups || $crudQuery->havings) ? '*' : $modelTable.'.'.$this->model->getKeyName();

If posts and comments have at least a column with the same name - and usually that's the primary key name (id), you will get error Column already exists: 1060 Duplicate column name XXX.

A simple fix is to change '*' to $modelTable.'.*' to select everything only from the main table.

[karandatwani92]

@tabacitu @pxpm need your attention here. The request is related to CORE. -- Thanks

[pxpm]

Thanks for the report @adriannuta and sorry it took me a long time to get back here. It slipped me somehow. 🙏

I've submitted https://github.com/Laravel-Backpack/CRUD/pull/5320 with your suggested fix, and merged it already, it will be tagged later today as 6.2.1

Thanks again for the heads up 🙏

Cheers