Phalcon: how to get distinct models?

Question

Using Phalcon Model, how can I get distinct rows when get rows using the find() method.

Answer 1

Using builder:

Basic implementation for later example:

    $queryBuilder = $this->getDI()->getModelsManager()
        ->createBuilder()
        ->addFrom('tableName', 't');

Distinct command:

    $queryBuilder->distinct('t.id');

Column thing works too, but not recommended:

    $queryBuilder->columns('DISTINCT(t.id) AS id')

Using strictly model:

   // we are waiting for it, but may still not be implemented
   TableModel::find(array('distinct' => 'id'))

For count:

   TableModel::count(array("distinct" => "id"));

And less recommended way according to previous answer:

   TableModel::find(array('columns' => 'distinct(id)'))

And link to imo best docs.

Also, there are some issues in Phalcon 2.0.2.

Answer 2

If you have declared some columns you can use:

 $result = Word::find(['columns'=>'distinct foo']);

Answer 3

Use the group keyword.

$result = TableModel::find(array("x_id = $x_id","group"=>"uid"));

You can add as many additional arguments to the first section with x_id such as

x_id = $x_id AND y_id = 100

The second portion is where you specify the field you want to group for TableModel.

Answer 4

You can define that in your model::initialize() method:

$this->hasManyToMany(
        'asset_id',
        'NameSpace\AssetsCategory',
        'asset_id',
        'category_id',
        'NameSpace\Category',
        'category_id',
        [
            'alias' => 'SimilarAssets',
            'params' => [
                'group' => 'NameSpace\Category.category_id' // remove duplicate data
            ]
        ]
    );

Answer 5

On some occasions you might wish to use it on model initialization, here is example:

/**
 * Class MyModel
 * @property \Phalcon\Mvc\Model\Resultset\Simple referenceAlias
 * @method int countReferenceAlias
 */
class MyModel extends \Phalcon\Mvc\Model
{
    public function initialize(): void
    {
        $this->setSource('my_model');

        $this->hasMany('id', 'ReferenceModel', 'reference_id', [
            'alias' => 'referenceAlias',
            'params' => ['distinct' => 'user_id']
        ]);
    }
}

So later on you can make calls this way:

print $myModel->countReferenceAlias();

or

foreach($myModel->referenceAlias() as $userReference){
    print $userReference->user->getName();
}

Answer 6

In phalcon 3.x it looks like to do a distinct with the Models Manager, the distinct method take a boolean as a parameter. So to do a distinct on a column you should do that:

 $queryBuilder = $this->getDI()->getModelsManager()
        ->createBuilder()
        ->addFrom('tableName', 't')
        ->columns('t.myColumn')
        ->distinct(true)
        ->getQuery()
        ->execute();