Can't paginate a query with a JOIN and select(*)

[PauBenetPrat]

• Laravel Version: 7.10 to 8.x
• PHP Version: 7.3
• Database Driver & Version: mysql

Description:

Can not paginate a query build with QueryBuilder when it has a join and we do select all fields with *.

Steps To Reproduce:

When using query builder to generate a query with a join if we select all fields with *, use a groupBy and paginate the query it crash with a Duplicate column name 'id':

Illuminate\Database\QueryException
SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id' (SQL: select count(*) as aggregate from (SELECT * FROM `order_contents`
INNER JOIN `orders` ON `GS_orders`.`id` = `order_contents`.`order_id`
INNER JOIN `menu_items` ON `menu_items`.`id` = `order_contents`.`item_id`) as `aggregate_table`)

I've realised that this was added on laravel 7.10 version:

class QueryBuilder {
  ...
    protected function runPaginationCountQuery($columns = ['*']) {
        if ($this->groups || $this->havings) {
            $clone = $this->cloneForPaginationCount();

            if (is_null($clone->columns) && ! empty($this->joins)) {
                $clone->select($this->from.'.*');
            }

            return $this->newQuery()
                ->from(new Expression('('.$clone->toSql().') as '.$this->grammar->wrap('aggregate_table')))
                ->mergeBindings($clone)
                ->setAggregate('count', $this->withoutSelectAliases($columns))
                ->get()->all();
        }
        ...
  }

I think that, if we are grouping, there is no need to keep selected columns when paginating, so we could always select $this->from.* when there is a join, not only when there a join and no columns are selected...

[driesvints]

Hey there,

Unfortunately we don't support this version anymore. Please check out our support policy on which versions we are currently supporting. Can you please try to upgrade to the latest version and see if your problem persists? We'll help you out and re-open this issue if so.

Thanks!

[PauBenetPrat]

Hi @driesvints it also happens with laravel 8.x.

I was refering that it happens since laravel 7.10. I tried to update to the latest version to see if it still crashes and it does.

Could you reopen it please?

[driesvints]

Hey @PauBenetPrat,

This seems like a support question to me and not a bug. Can you first please try one of the support channels below? If you can actually identify this as a bug, feel free to report back and I'll gladly help you out and re-open this issue.

• Laracasts Forums
• Laravel.io Forums
• StackOverflow
• Discord
• Larachat
• IRC

Thanks!

[PauBenetPrat]

Hi @driesvints from my point of view it's a bug, cause it was working on previous versions and it stopped working since laravel 7.10

I wrote a PR to fix it but @taylorotwell said it may not be a good idea to do this on a patch release.

https://github.com/laravel/framework/pull/36502/files

We've got 3 projects stuck on laravel 7.9 expecting for this to be solved and it doesn't seem to me to be a missconfiguration issue...

Thank you anyway :)

[driesvints]

@PauBenetPrat maybe try PR'ing to master instead to see if Taylor would accept it?

[driesvints]

Btw, this was the original PR: #32691 & this was the commit by @taylorotwell: 98a242e back then. Make sure to reference those in the PR.

[PauBenetPrat]

I wrote a PR but it was finally declined (#36561)
If anyone face the same issue, we had to override QueryBuilder as suggested here:

https://medium.com/@justin.park001/laravel-techniques-extending-the-query-builder-2f4aca7956a2

Although we did it slightly different, instead of Replacing the DataBaseServiceProvider on app/config.php, we override our mysql connection on AppServiceProvider register method:

namespace App\Providers;
class AppServiceProvider {
  public function register() {
    Connection::resolverFor('mysql', function ($connection, $database, $prefix, $config) {
      return new \App\Override\MySqlConnection($connection, $database, $prefix, $config);
    });
  }

namespace App\Override;

use App\Override\Builder as QueryBuilder;
use Illuminate\Database\MySqlConnection as IlluminateMySqlConnection;

class MySqlConnection extends IlluminateMySqlConnection
{
  public function query()
  {
    return new QueryBuilder($this, $this->getQueryGrammar(), $this->getPostProcessor());
  }
}

namespace App\Override;

use Illuminate\Database\Query\Builder as IlluminateQueryBuilder;
use Illuminate\Database\Query\Expression;

class Builder extends IlluminateQueryBuilder
{
  protected function runPaginationCountQuery($columns = ['*'])
  {
    if ($this->groups || $this->havings) {
      $clone = $this->cloneForPaginationCount();

      /*
       * THIS IS THE LINE ORIGINALLY OVERWRITTEN
       *  if (is_null($clone->columns) && ! empty($this->joins))
       */
      if (! empty($this->joins)) {
        $clone->select($this->from.'.*');
      }

      return $this->newQuery()
        ->from(new Expression('('.$clone->toSql().') as '.$this->grammar->wrap('aggregate_table')))
        ->mergeBindings($clone)
        ->setAggregate('count', $this->withoutSelectAliases($columns))
        ->get()->all();
    }

    $without = $this->unions ? ['orders', 'limit', 'offset'] : ['columns', 'orders', 'limit', 'offset'];

    return $this->cloneWithout($without)
      ->cloneWithoutBindings($this->unions ? ['order'] : ['select', 'order'])
      ->setAggregate('count', $this->withoutSelectAliases($columns))
      ->get()->all();
  }