paginate method generate wrong sql when pass in second parameter "columns" more values

[andreipasat]

• Laravel Version: 5.3.*
• PHP Version: 7
• Database Driver & Version: MySql

Description:

Hello !
I try to get results form database and cunctruct pagination using query builder :
$profiles = DB::table('profiles')->select('profiles.*','profiles.id as profile_id');
$results = $profiles->paginate(8, ['id','name']);

In this case i get sql error like :

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' name) as aggregate from profiles ' at line 1 (SQL: select count(id, name) as aggregate from profiles )
It's clear that error apears because of : count(id, name)
I think should be generated count(id) as aggregate, name from profiles.
This is very disappointing becuase imagine that need to use having in sql, it's impossibile to use paginate for cases like that.

Steps To Reproduce:

[themsaid]

@andreipasat interested to know why you pass multiple columns to paginate().

[themsaid]

ping @andreipasat

[andreipasat]

@themsaid why then method accept multiple columns if not work ?
"This is very disappointing becuase imagine that need to use having in sql, it's impossibile to use paginate for cases like that."

[themsaid]

@andreipasat We have a few issues in pagination queries that I'm trying to solve :) That's why I'm asking for your use case.

I appreciate your help on that, what cases do you think the paginator needs to consider and how do you think we can solve that?

[andreipasat]

@themsaid For example i need use having in my query:
$distance = DB::raw('( 6371 * ACOS( COS( RADIANS('.$post['lat'].') ) * COS( RADIANS( lat ) ) * COS( RADIANS( lng ) - RADIANS('.$post['lng'].') ) + SIN( RADIANS('.$post['lat'].') ) * SIN( RADIANS( lat ) ) ) ) as distance');
$profiles = DB::table('profiles')->select('profiles.*','profiles.id as profile_id', $distance);
$dist = 20;
$profiles->having('distance', '<=', $dist);
$results = $profiles->paginate(8); // like that i get error that "distance" is not defined because paginate select just count(*)
I need use :
`$results = $profiles->paginate(8,['*',$distance]);'
for not have this error but i get another error because it's generating a sql like that :

select count(*,( 6371 * ACOS( COS( RADIANS ..... as distance) as aggregate and not
select count(*) as aggregate, ( 6371 * ACOS( COS( RADIANS ..... as distance how I think should be

[themsaid]

@andreipasat But the query should only return the count, that's why I wonder why you pass multiple columns.

[Roham0010]

@themsaid Hi,
I have this issue too, you asked "it should only return the count, that's why I wonder why you pass multiple columns"
the answer is that, imagine I have a product table and a user table I want to show number of each user products in my admin page of my users html table! Also I wanna show total products of each user I have. To do this I have to add a sub query to my select for get count of each user products like:
DB:raw('(select count(*) from products where products.user_id=users.id) as totalProducts')
and also I want to search base on this totalProducts on the results.
With basic laravel paginate if I do some search on totalProducts with having, final query to get count of result that paginate generate is like this:
select count(*) as aggregate from users , ... where clouse ... group by users.id having totalProducs > 10
It returns an error that says unknown column totalProducts , ....!!!
And we can't send this sub query to be defined in query when it's getting aggregate!
My point as @andreipasat said is that secound array parameter in paginate function shouldn't be inside count() it should be along side count() like:
select count(*) as aggregate, (select count(*) from products where .......) as totatProducts
It's what we want paginate do generate it this case, not this:
select count(*, (select count(*) from products where .......) as totatProducts) as aggregate
Thakns 👍

[themsaid]

But select count(*) as aggregate, ANOTHER_COLUMN that other column won't be used anywhere, we only read the aggregate count and that's it. Selecting other columns won't have any effect.

[Roham0010]

@themsaid
It will be used in having clause...
Look at this:
select cout(*),(select count(*) from products where products.user_id=users.id) as totalUserProducts from users where conditions group by users.id having totalUserProducts > 10 In this case I select users that have more than 10 products, Then I have a condition base on a sub query result so this sub query alias name should be used in pagination both when counting aggregate and when fetching data!

[andregaldino]

I have the same issue, My scope Function is :

public function scopeDistance($query, $lat, $lng, $radius = 2.5, $unit = "km")
    {
        $unit = ($unit === "km") ? 6378.10 : 3963.17;
        $lat = (float) $lat;
        $lng = (float) $lng;
        $radius = (double) $radius;
        return $query
        ->join('establishment_category','establishment_category.establishment_id','=','establishments.id')
        ->select(DB::raw("establishments.*,
            (
                $unit * ACOS(COS(RADIANS($lat))
                * COS(RADIANS(lat))
                * COS(RADIANS($lng) - RADIANS(lng))
                + SIN(RADIANS($lat)
            )
            * SIN(RADIANS(lat)))) AS distance")
            )
        ->orderBy('distance','asc')
        ->groupBy('establishments.id')
        ->having('distance','<=',$radius);
    } 

And i use like that :

$establishments = Establishment::Distance($lat,$lng)->paginate(10);

That's my error log :

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from establishments inner join establishment_category on establishment_category.establishment_id = establishments.id where establishments.deleted_at is null group by establishments.id having distance <= 2.5)

Without pagination works pretty good.
Ps: strict mode for MySQL in config/database.php is false

[andreipasat]

@andregaldino
Only solution that i found for me is to modify function compileAggregate from Grammar.php

`$sql = 'select ';

    $column = $aggregate['columns'][0];

    $addSelect = '';
    if (count($aggregate['columns']) > 1) {
        for ($i = 1; $i < count($aggregate['columns']); $i++) {
            $addSelect .= ',' .$aggregate['columns'][$i];
        }
    }

    $sql .= $aggregate['function'].'('.$column.') as aggregate' . $addSelect;

    return $sql;`

And before execute paginate if you have "having" need to add DB::unprepared("SET sql_mode = ''");.
Now works fine but the biggest minus is that need to change core function of laravel and will be removed after composer update.

[andregaldino]

Ok, Thanks for your solution @andreipasat , let's wait some update of laravel with solution like that

[joko-wandiro]

You can try using derived table. I'm using derived table to make Laravel Pagination keep works correctly using Model.setTable() method.

Source Code:

Model->setTable(DB::raw('Your Query'))

SQL:

select count(*) as aggregate from (
    SELECT *, COUNT(company.job_experience_personnelID) AS total_employee,
    SUBSTRING_INDEX(GROUP_CONCAT(REPLACE(CONCAT_WS(" ",personnel_firstName,personnel_lastName), ";", "") SEPARATOR ";"), ";", 10) AS personnel_name FROM (
        select personnel.firstName AS personnel_firstName, personnel.lastName AS personnel_lastName,
        job_experience.id AS job_experience_id, job_experience.personnelID AS job_experience_personnelID,
        `company`.`id`, `company`.`name`, `company`.`industryCategoryID`, `company`.`companyID`,
        `company`.`prefix`, `company`.`description`, `company`.`countryID`, `company`.`provinceID`, `company`.`city`,
        `company`.`created_at`, `company`.`updated_at`, industry_category.name AS industry_category_name, country.name AS country_name from `company`
        LEFT join `job_experience` on `job_experience`.`companyID` = `company`.`id`
        LEFT join `personnel` on `personnel`.`id` = `job_experience`.`personnelID`
        LEFT join `industry_category` on `industry_category`.`id` = `company`.`industryCategoryID`
        LEFT join `country` on `country`.`id` = `company`.`countryID`
        GROUP BY job_experience.personnelID, company.id
    ) AS company
    GROUP BY company.id 
) AS company