We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

QueryBuilder answering error if use groupBy

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

edited Jun '16

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.

edited Jun '16

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?

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

edited Jul '16

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