cakephp - cake - ¿Cómo limitar las asociaciones contenidas por registro/grupo?

query builder cakephp 3

Question

Tengo un modelo, artículos, que tiene muchos resúmenes. Quiero cargar los 10 últimos artículos, y para cada artículo, el resumen con el mayor número de puntos. Mi función se ve así:

public function getArticles($category, $viewName) {
            $subArticles = $this->Articles->findByCategory($category)->contain([
                    ''Abstracts'' => function ($q) {
                            return $q
                                    ->select([''body'', ''points'', ''article_id''])
                                    ->where([''Abstracts.approved'' => true])
                                    ->limit(10)
                                    ->order([''Abstracts.points'' => ''DESC'']);
                    }
            ])
            ->limit(10)
            ->order([''Articles.created'' => ''DESC'']) ;
            $this->set( $viewName . ''Articles'', $subArticles );
    }

El resultado que obtengo no es lo que intento sin embargo. Mirando a través del SQL, primero CakePHP está obteniendo los artículos.id de todo en la categoría (bien). Luego, CakePHP entra en la tabla de resúmenes, utilizando los 10 artículos que acaba de encontrar, y solicita los 10 resúmenes con los votos más altos (que pertenecen a esos artículos).

El problema es que quiero 1 resumen para cada artículo, no los 10 resúmenes que pertenecen a ningún artículo en esa categoría. ¿Cómo puedo arreglar esto? ¡Gracias!

EDITAR

ndm sugirió que esto era un duplicado de Usar límite () en el modelo contenido, así que intenté la solución allí. A saber, agregué esto a mi Modelo:

 $this->hasOne(''TopAbstract'', [
            ''className'' => ''Abstracts'',
            ''foreignKey'' => ''abstract_id'',
            ''strategy'' => ''select'',
            ''sort'' => [''TopAbstract.points'' => ''DESC''],
            ''conditions'' => function ($e, $query) {
            $query->limit(1);
            return $e;
    } ]);

Y luego trato de encontrar los Artículos por Categoría, con contener ([''TopAbstract'']), solo esto mata mi SQL. Muere una muerte horrible:

Error: SQLSTATE[HY000]: General error: 1 near ")": syntax error

¿Depurar ni siquiera muestra la consulta que lo mató, por lo que no estoy seguro de cómo depurar este?

EDITAR

Hablando un poco conmigo mismo, pero el error está definitivamente en la parte de ''condiciones'' de hasOne. Lo elimino y funciona bien. No se puede encontrar un ejemplo de cómo se supone que debe verse en las interwebs ... ¿alguien tiene alguna idea?

Answer 1

Lo que está buscando es una solución al problema de mayor n-por-grupo . No mencionó ningún RDBMS específico, pero no obstante vea también http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html

Así que demos una oportunidad, aquí hay tres opciones que se pueden aplicar a nivel de asociación, sin embargo, puede considerarlas como no "directas".

Para algo HasMany específico, ¡ HasMany hacia abajo!

Seleccionar estrategia - Usar una unión en una agrupación, subconsulta de valor máximo

$this->hasOne(''TopAbstracts'', [
    ''className'' => ''Abstracts'',
    ''strategy'' => ''select'',
    ''conditions'' => function (/Cake/Database/Expression/QueryExpression $exp, /Cake/ORM/Query $query) {
        $query->innerJoin(
            [
                ''AbstractsFilter'' => $query
                    ->connection()
                    ->newQuery()
                    ->select([''article_id'', ''points'' => $query->func()->max(''points'')])
                    ->from(''abstracts'')
                    ->group(''article_id'')
            ],
            [
                ''TopAbstracts.article_id = AbstractsFilter.article_id'',
                ''TopAbstracts.points = AbstractsFilter.points''
            ]
        );
        return [];
    }
]);

Esto seleccionará los resúmenes superiores a través de una consulta de combinación que se basa en los puntos máximos, se verá algo así como

SELECT
    TopAbstracts.id AS `TopAbstracts__id`, ...
FROM
    abstracts TopAbstracts
INNER JOIN (
        SELECT
            article_id, (MAX(points)) AS `points`
        FROM
            abstracts
        GROUP BY
            article_id
    )
    FilterAbstracts ON (
        TopAbstracts.article_id = FilterAbstracts.article_id
        AND
        TopAbstracts.points = FilterAbstracts.points
    )
WHERE
    TopAbstracts.article_id in (1,2,3,4,5,6,7,8, ...)

Seleccionar estrategia: usar el filtro de autocombinación izquierda

$this->hasOne(''TopAbstracts'', [
    ''className'' => ''Abstracts'',
    ''strategy'' => ''select'',
    ''conditions'' => function (/Cake/Database/Expression/QueryExpression $exp, /Cake/ORM/Query $query) {
        $query->leftJoin(
            [''AbstractsFilter'' => ''abstracts''],
            [
                ''TopAbstracts.article_id = AbstractsFilter.article_id'',
                ''TopAbstracts.points < AbstractsFilter.points''
            ]);
        return $exp->add([''AbstractsFilter.id IS NULL'']);
    }
]);

Esto usará una autocombinación que se filtra según las filas que no tienen a.points < b.points , se verá algo así como

SELECT
    TopAbstracts.id AS `TopAbstracts__id`, ...
FROM 
    abstracts TopAbstracts
LEFT JOIN
    abstracts FilterAbstracts ON (
        TopAbstracts.article_id = FilterAbstracts.article_id
        AND
        TopAbstracts.points < FilterAbstracts.points
    )
WHERE
    (FilterAbstracts.id IS NULL AND TopAbstracts.article_id in (1,2,3,4,5,6,7,8, ...))

Unirse a la estrategia: utilizar una subconsulta para la condición de unión

$this->hasOne(''TopAbstracts'', [
    ''className'' => ''Abstracts'',
    ''foreignKey'' => false,
    ''conditions'' => function (/Cake/Database/Expression/QueryExpression $exp, /Cake/ORM/Query $query) {
        $subquery = $query
            ->connection()
            ->newQuery()
            ->select([''SubTopAbstracts.id''])
            ->from([''SubTopAbstracts'' => ''abstracts''])
            ->where([''Articles.id = SubTopAbstracts.article_id''])
            ->order([''SubTopAbstracts.points'' => ''DESC''])
            ->limit(1);

        return $exp->add([''TopAbstracts.id'' => $subquery]);
    }
]);

Esto utilizará una subconsulta correlacionada que usa una selección bastante específica con un orden y una limitación simples para elegir el comentario principal. Tenga en cuenta que la opción foreignKey se establece en false para evitar que una condición adicional Articles.id = TopAbstracts.article_id se compile en las condiciones de unión.

La consulta se verá algo así como

SELECT
    Articles.id AS `Articles__id`, ... ,
    TopAbstracts.id AS `TopAbstracts__id`, ...
FROM
    articles Articles
LEFT JOIN
    abstracts TopAbstracts ON (
        TopAbstracts.id = (
            SELECT
                SubTopAbstracts.id
            FROM
                abstracts SubTopAbstracts
            WHERE
                Articles.id = SubTopAbstracts.article_id
            ORDER BY
                SubTopAbstracts.points DESC
            LIMIT
                1
        )
    )

Todas estas 3 opciones consultarán e inyectarán los registros sin ningún hacker, simplemente no es muy "directo".

Un enfoque manual

En aras de la exhaustividad, por supuesto, siempre es posible cargar manualmente los registros asociados y formatear los resultados de forma adecuada, por ejemplo, utilizando formateadores de resultados, consulte por ejemplo, la entidad CakePHP contiene sin clave externa.

Seleccionar estrategia y orden invertido

Solo como referencia, una de las soluciones raras con la que tropecé inicialmente. ¡Este realmente no debería ser usado!

Esto seleccionará todos los resúmenes asociados, y luego el ORM iterará sobre ellos y para cada artículo elegirá el primero con un valor de article_id coincidente. Entonces, en teoría, cuando se ordena el trazado de points , el ORM debe elegir el que tiene más puntos.

Si bien esperaba que esto funcionara de la caja, parece que el ORM itera sobre los resultados en orden inverso, lo que hará que se elijan las filas incorrectas. Para que esto funcione, la consulta debe usar el orden opuesto que normalmente debería usarse, es decir, ASC lugar de DESC .

$this->hasOne(''TopAbstracts'', [
    ''className'' => ''Abstracts'',
    ''foreignKey'' => ''abstract_id'',
    ''strategy'' => ''select'',
    ''conditions'' => function (/Cake/Database/Expression/QueryExpression $exp, /Cake/ORM/Query $query) {
        $query->order([''TopAbstracts.points'' => ''ASC'']);
        return [];
    }
]);

Además, la función debe devolver una matriz vacía en lugar de la expresión como se muestra en la respuesta vinculada, ya que esto provocará que se compile SQL no válido. Ambos comportamientos, la iteración de orden inverso y el SQL no válido pueden ser errores.

Si bien esto funcionará, siempre seleccionará todos los resúmenes asociados, no solo los más importantes, que podrían considerarse bastante ineficientes, y se vería algo así como

SELECT
    Articles.id AS `Articles__id`, ...
FROM
    articles Articles

SELECT
    TopAbstracts.id AS `TopAbstracts__id`, ...
FROM
    abstracts TopAbstracts
WHERE
    TopAbstracts.article_id in (1,2,3,4,5,6,7,8, ...)
ORDER BY
    TopAbstracts.points ASC

HasMany asociaciones

Le di una HasMany asociaciones HasMany , pero ahora estoy demasiado ocupado como para seguir adelante con esto ... simplemente ROW_NUMBER() una asociación personalizada específica de MySQL para fines de prueba, basada en la emulación ROW_NUMBER() similar a MySQL seleccione los mejores registros X para cada individuo en la mesa

Si alguien está interesado, consulte https://gist.github.com/ndm2/039da4009df1c5bf1c262583603f8298