Digamos que tengo las siguientes tablas que modelan las etiquetas adjuntas a los artículos:
articles (article_id, title, created_at, content) tags (tag_id, tagname) articles_tags (article_fk, tag_fk) ¿Cuál es la forma idiomática de recuperar los n artículos más nuevos con todos sus nombres de etiqueta adjuntos? Este parece ser un problema estándar, pero soy nuevo en SQL y no veo cómo resolver este problema de manera elegante.
Desde la perspectiva de la aplicación, me gustaría escribir una función que devuelva una lista de registros de la forma [title, content, [tags]] , es decir, todas las etiquetas adjuntas a un artículo estarían contenidas en una lista de longitud variable. Las relaciones SQL no son tan flexibles; hasta ahora, solo puedo pensar en una consulta para unir las tablas que devuelve una nueva fila para cada combinación de artículo/etiqueta, que luego necesito condensar mediante programación en el formulario anterior.
Alternativamente, puedo pensar en una solución donde emito dos consultas: Primero, para los artículos; segundo, una inner join en la tabla de enlaces y la tabla de etiquetas. Luego, en la aplicación, ¿puedo filtrar el conjunto de resultados para cada article_id para obtener todas las etiquetas de un artículo determinado? Esta última parece ser una solución bastante detallada e ineficiente.
¿Me estoy perdiendo de algo? ¿Hay una forma canónica de formular una sola consulta? ¿O una sola consulta más un procesamiento posterior menor?
Además de la simple pregunta de SQL, ¿cómo se vería una consulta correspondiente en Opaleye DSL? Es decir, si se puede traducir en absoluto?
Por lo general, usaría una consulta de limitación de filas que selecciona los artículos y los ordena por fecha descendente, y una subconsulta conjunta o correlacionada con una función de agregación para generar la lista de etiquetas.
La siguiente consulta le brinda los 10 artículos más recientes, junto con el nombre de sus etiquetas relacionadas en una matriz:
select a.*, ( select array_agg(t.tagname) from article_tags art inner join tags t on t.tag_id = art.tag_fk where art.article_fk = a.article_id ) tags from articles order by a.created_at desc limit 10Ha convertido la mayor parte de la respuesta de GMB con éxito a Opaleye en su respuesta a su pregunta posterior . Aquí hay una versión completamente funcional en Opaleye.
En el futuro, puede hacer preguntas de este tipo en el rastreador de problemas de Opaleye . Probablemente obtendrá una respuesta más rápida allí.
{-# LANGUAGE Arrows #-} {-# LANGUAGE FlexibleInstances #-} {-# LANGUAGE MultiParamTypeClasses #-} {-# LANGUAGE TemplateHaskell #-} import Control.Arrow import qualified Opaleye as OE import qualified Data.Profunctor as P import Data.Profunctor.Product.TH (makeAdaptorAndInstance') type F field = OE.Field field data TaggedArticle abc = TaggedArticle { articleFk :: a, tagFk :: b, createdAt :: c} type TaggedArticleR = TaggedArticle (F OE.SqlInt8) (F OE.SqlInt8) (F OE.SqlDate) data Tag ab = Tag { tagKey :: a, tagName :: b } type TagR = Tag (F OE.SqlInt8) (F OE.SqlText) $(makeAdaptorAndInstance' ''TaggedArticle) $(makeAdaptorAndInstance' ''Tag) tagsTable :: OE.Table TagR TagR tagsTable = error "Fill in the definition of tagsTable" taggedArticlesTable :: OE.Table TaggedArticleR TaggedArticleR taggedArticlesTable = error "Fill in the definition of taggedArticlesTable" -- | Query all tags. allTagsQ :: OE.Select TagR allTagsQ = OE.selectTable tagsTable -- | Query all article-tag relations. allTaggedArticlesQ :: OE.Select TaggedArticleR allTaggedArticlesQ = OE.selectTable taggedArticlesTable -- | Join article-ids and tag names for all articles. articleTagNamesQ :: OE.Select (F OE.SqlInt8, F OE.SqlText, F OE.SqlDate) articleTagNamesQ = proc () -> do ta <- allTaggedArticlesQ -< () t <- allTagsQ -< () OE.restrict -< tagFk ta OE..=== tagKey t -- INNER JOIN ON returnA -< (articleFk ta, tagName t, createdAt ta) -- | Aggregate all tag names for all articles articleTagsQ :: OE.Select (F OE.SqlInt8, F (OE.SqlArray OE.SqlText)) articleTagsQ = OE.aggregate ((,) <$> P.lmap (\(i, _, _) -> i) OE.groupBy <*> P.lmap (\(_, t, _) -> t) OE.arrayAgg) (OE.limit 10 (OE.orderBy (OE.desc (\(_, _, ca) -> ca)) articleTagNamesQ))