Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Query Builder Paginate gives SQLSTATE[42000] syntax error if you pass columns into paginate() #28890

Closed
awais-vteams opened this issue Jun 19, 2019 · 2 comments · Fixed by #28937
Closed

Query Builder Paginate gives SQLSTATE[42000] syntax error if you pass columns into paginate() #28890

awais-vteams opened this issue Jun 19, 2019 · 2 comments · Fixed by #28937
Labels
bug

Comments

@awais-vteams
Copy link
Contributor

  • Laravel Version: 5.8.22
  • PHP Version: 7.2.17
  • Database Driver & Version: MySql 8.0

Description:

Database Query Builder Paginate gives SQLSTATE[42000]: Syntax error if you pass columns into paginate()

Steps To Reproduce:

$results = DB::table('users')->paginate(20, ['id', 'name']);

Illuminate \ Database \ QueryException (42000)
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 users' at line 1 (SQL: select count(id, name) as aggregate from users)

Ref: #28889
@awais-vteams awais-vteams changed the title Query Builder Paginate gives syntax error if you pass columns into paginate() Query Builder Paginate gives SQLSTATE[42000] syntax error if you pass columns into paginate() Jun 19, 2019
@ap-in-git
Copy link

The right way of doing this would be .

$results = DB::table('users')->select("id,"name")->paginate(20);

@driesvints driesvints added the bug label Jun 24, 2019
@driesvints driesvints mentioned this issue Jun 24, 2019
Merged
@driesvints
Copy link
Member

Sent in a PR for this here: #28937

taylorotwell pushed a commit to illuminate/database that referenced this issue Jun 24, 2019
@driesvints
Fix columns parameter on paginate method
b7aae24 
This is currently broken and will throw a SQL exception:

    DB::table('posts')->paginate(5, ['title', 'content']);

At the moment when you attempt to pass specific columns on the paginate method on the base query builder it will fail when you provide more than one column. SQL queries can only handle a count for a specific column and not multiple ones. That's why it's unwanted to pass along the columns parameter from the paginate method to the getCountForPagination method. Since we just want to do a count of the current result set a simple count with the '*' sign is enough. At the moment the Eloquent builder already handles pagination in this way.

It's noted by Staudenmeier that the only difference is when you provide a specific column to the getCountForPagination method is that it'll only count non-NULLs. However, it's not feasible to allow this to be done in combination with the paginate method because the columns param on the paginate method soley exists to filter which columns will be retrieved in the paginated result set (which is currently broken thus). See laravel/framework#28844 (comment)

I've added an extra integration test which reproduces the problem at hand. I've updated the tests in the DatabaseQueryBuilderTest class to reflect the changes made to getCountForPagination call.

This has been a long outstanding issue so hopefully this will prevent more issues from popping up.

Fixes laravel/framework#28890
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

[5.8] Fix columns parameter on paginate method
3 participants
@driesvints @awais-vteams @ap-in-git