How to convert this SQL to PHQL?

[ftugit]

I need to retrieve a certain number of names from another table. If I use JOIN, I get all the names, but I want to limit their number. Here’s an example of regular SQL: https://dbfiddle.uk/T3cexpeH.
I have a Collection table and a CollectionNames table. I need to use the id from Collection to get a certain number of names from CollectionNames using collection_id for their linkage.

This code works:

$sql = 'SELECT f.id,
    (SELECT group_concat(x.name)
            FROM (SELECT b.name
                            FROM collection_names AS b
                            WHERE b.collection_id = f.id
                            ORDER BY b.name
                            LIMIT 2) AS x) names
    FROM collection AS f';
$result = $this->db->fetchAll($sql, \Phalcon\Db\Enum::FETCH_ASSOC);

But this one does not:

$query = $this->modelsManager->executeQuery('
    SELECT f.id,
   (SELECT group_concat(x.name)
           FROM (SELECT b.name
                        FROM App\Models\CollectionNames AS b
                        WHERE b.collection_id = f.id
                        ORDER BY b.name
                        LIMIT 1) AS x) names
  FROM App\Models\Collection AS f');

[niden]

GROUP_CONCAT is not supported by PHQL. You can however register a new function that would do what you want.

Have a look at this

https://docs.phalcon.io/latest/db-phql/?h=match#custom-dialect

[ftugit]

I am already using this function. Here it is implemented:

$db = new Phalcon\Db\Adapter\Pdo\Sqlite([
    'dbname' => STORAGE . '/database/sqlite.db',
    'dialectClass'  => (new Phalcon\Db\Dialect\Sqlite)->registerCustomFunction('GROUPCONCAT', function ($dialect, $expression) {
        $arguments = $expression['arguments'];

        if(empty($arguments[1]) !== true) 
            return sprintf(
                'GROUP_CONCAT(%s, %s)',
                $dialect->getSqlExpression($arguments[0]),
                $dialect->getSqlExpression($arguments[1])
            );
        else 
            return sprintf('GROUP_CONCAT(%s)', $dialect->getSqlExpression($arguments[0]));
    })
]);

Through JOIN, it works and collects names with a separator. However, it collects all names without limitation, and what if I have 10,000 of them?
I just didn't ask for help to make it work; I asked for help with the SQL itself.

Here is the original implementation for retrieving names:

$builder = $this->modelsManager->createBuilder()
            ->columns([implode(',', array_map(function($item) {
                return 'c.' . $item;
            }, $fields)), 'GROUPCONCAT(cn.name, :separator:) AS names'])
            ->from(['c' => $model::class])
            ->leftJoin('App\Models\CollectionNames', $model->softDeleteFetch('c.id = cn.collection_id', false, 'cn'), 'cn')
            ->where($model->softDeleteFetch('c.id = :id: AND c.account_id = :uid:', $trashed === 'only' ?: ($trashed === 'with' ? null : false), 'c'), [
                'id' => $id,
                'uid' => $this->account->id
            ])
            ->groupBy('c.id')
            ->limit(1);

It works, but as I mentioned, I am getting all rows of names, and I need to limit them. I found SQL on the internet from a post with a question, but PHQL just doesn’t want to work with it. If I write pure SQL to PDO, then for me, the whole point of using PHQL is lost.

[ftugit]

In the end, I wrote an analog of GROUP_CONCAT. A questionable implementation of the desired SQL.

registerCustomFunction('JOINSELECT', function ($dialect, $expression) {
            $arguments = $expression['arguments'];

            $table = (new (trim($arguments[1]['value'], '\'')))->getSource();
            $field = $arguments[0]['value'];
            $where = trim($arguments[2]['value'], '\'');
            $id = $dialect->getSqlExpression($arguments[3]);
            $comparator = trim($arguments[4]['value'], '\'');
            $limit = empty($arguments[5]) !== true ? $arguments[5]['value'] : 5;

            // JOINSELECT("name", "App\Models\CollectionNames", "collection_id", "=", id, "1")
            return sprintf('(SELECT group_concat(x.%s, "|||") FROM (SELECT b.%s FROM %s AS b WHERE %s %s %s ORDER BY b.id LIMIT %s) AS x)', $field, $field, $table, $where, $comparator, $id, $limit);
 })