framework: paginate method generate wrong sql when pass in second parameter "columns" more values
- 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:
About this issue
- Original URL
- State: closed
- Created 8 years ago
- Comments: 15 (7 by maintainers)
@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 > 10In 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!@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 > 10It returns an error that says unknown columntotalProducts, …!!! 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 totatProductsIt’s what we want paginate do generate it this case, not this:select count(*, (select count(*) from products where .......) as totatProducts) as aggregateThakns 👍Ok, Thanks for your solution @andreipasat , let’s wait some update of laravel with solution like that