QueryBuilder answering error if use groupBy

Question

QueryBuilder answering error if use groupBy

Dima Semenchuk Jun '16

Created Jun '16	Last Reply Jun '16	Replies 2	Views 422	Votes 0

Dima Semenchuk 2.1k

Jun '16

Phalcon 2.1.0b | PHP 5.5.34 | MySQL 5.6

I got a strange error

Phalcon\Paginator\Adapter\QueryBuilder::getPaginate(): Invalid arguments supplied for fast_join() i

With code

$queryBuilder = $this->modelsManager->createBuilder();
$queryBuilder->from(ChangeAction::class);
$queryBuilder->groupBy(ChangeAction::class . '.changeActionId');
$queryBuilder->join(ChangeField::class, ChangeField::class .'.changeActionId = ' . 
ChangeAction::class . '.changeActionId');
$queryBuilder->where(ChangeField::class . '.changeFieldId IS NOT NULL');

SELECT [Models\ChangeAction].* FROM [Models\ChangeAction] JOIN [Models\ChangeField] ON Models\ChangeField.changeActionId = Models\ChangeAction.changeActionId WHERE Models\ChangeField.changeFieldId IS NOT NULL GROUP BY Models\ChangeAction.changeActionId ORDER BY Models\ChangeAction.changeActionId DESC

Without groupBy it working properly

I'm using groupBy b/c disctinct method is not exists

Wojciech Ślawski · Answer 1 · 2016-06-27T15:14:43-07:00

First change your code to:

$queryBuilder = $this->modelsManager->createBuilder()
->from(['ca' => ChangeAction::class])
->groupBy('ca.changeActionId')
->join(ChangeField::class, 'cf.changeActionId = ca.changeActionId','cf')
->where('cf.changeFieldId IS NOT NULL');

Also if you have relations done then you don't need to type condition in join. Phalcon will do it. About error - i don't know why this error is raised. I think you need to post it on github.

Dima Semenchuk · Answer 2 · 2016-06-27T16:14:12-07:00

Thank you for a tip! I need this JOIN to exclude ChangeAction records which has no child records in ChangeField (ChangeAction hasMany ChangeField) Can you, please, give an advice how I can do this exclusion without joins?

Wojciech Ślawski · Answer 3 · 2016-06-27T16:40:33-07:00

I mean you still need joins. Just only if you have relation you can instead of ->join(ChangeField::class, 'cf.changeActionId = ca.changeActionId','cf') use just ->join(ChangeField::class. Everything is in docs https://docs.phalcon.io/pl/latest/reference/models.html#relationships-between-models

Dima Semenchuk · Answer 4 · 2016-07-01T08:08:51-07:00

Dima Semenchuk
2.1k

edited Jul '16
Jul '16

I forgot to say main thing - it is not mysql but AWS AuroraDB.. Probably I should add an issue to github