使用内部n引用创建视图

Question

对于CMS系统，我正在尝试一种优雅的方式来搜索文章关系中用户提供的url。

我有两个表:一个包含文章(cms_article)，另一个保存文章的语言特定内容(cms_content)。因此，cms_article中的1行可以在cms_content中有多个元组，因为它代表了文章的不同语言表示。

不同的文章也可以由reference列嵌套，如果它是顶级文章-- cms_article.ref = NULL，则引用cms_article中的id列。

表：

cms_article  id  | ref | published  
cms_content  cid | aid | lang      | url | browserTitle | content | etc etc

在这两个表中，我想创建一个视图，用于查找URL。因此，我想为cms_article中的每个给定元组构造一个cms_article字段，每个父/引用都作为URL的一部分。

例如，让我们回顾一下这个页面/文章的结构：

AID  English                             Dutch                               More languages
1    /Products                           /Producten
4    /Products/Product-2                 /Producten/Product-2
7    /Products/Product-2/screenshots     /Producten/Product-2/afbeeldingen
34   /Products/Product-2/pricing         /Producten/Product-2/prijzen
12   /Products/Product-5                 /Producten/Product-5
6    /About-us                           /Over-ons

每一行代表cms_article关系中的一篇文章和一个元组。URL-字段来自相应的cms_content.url字段。

如何将每个元组的totalURL聚合为视图，以便生成如下视图：

cms_view：

aid      = article ID       = integer
lang     = language         = NL|EN|... 
totalURL = agregated URL    = /products/product-2/pricing

对于上面的示例，它必须生成下表：

1  | EN | /Products
1  | NL | /Producten
4  | EN | /Products/Product-2
4  | NL | /Producten/Product-2/afbeeldingen
etc etc 

当然，我可以遍历cms_content中的每个元组，并在PHP中找到引用(并对每个级别和每个元组重复此引用)。但是，由于这给服务器带来了很大的压力，我正在寻找一个优雅的sql解决方案，比如一个视图。

编辑

这就是我走了多远，我觉得我离得很近。寻找解决方案:将url和parentURL合并到一个字段中。以及如何在三个层次上做到这一点。

SELECT 
a.id AS aid, c.lang, a.ref,
c.url,
(
    SELECT c2.url
    FROM cms_content AS c2
    WHERE c2.aid = a.ref
    AND
    c2.lang = c.lang
) AS parentURL
FROM cms_content AS c
LEFT JOIN cms_article AS a ON c.aid = a.id
WHERE a.domain = 'some.url'
AND a.published =1
ORDER BY aid

Answer 1

如果您了解所有语言，则可以连接子查询。

示例：

SELECT
aid
, CONCATENATE( 
  CASE WHEN (SELECT langcode FROM cms_article x WHERE a.aid = x.aid AND language = 'EN')
  THEN 
    CONCATENATE(
    (SELECT langcode FROM cms_article x WHERE a.aid = x.aid AND language = 'EN')
    , '|')
  ELSE ''
  END,
  CASE WHEN (SELECT langcode FROM cms_article x WHERE a.aid = x.aid AND language = 'DU')
  THEN 
    CONCATENATE(
    (SELECT langcode FROM cms_article x WHERE a.aid = x.aid AND language = 'DU')
    , '|')
  ELSE ''
  END) as language
, CONCATENATE( 
  CASE WHEN (SELECT totalUrl FROM cms_article x WHERE a.aid = x.aid AND language = 'EN')
  THEN 
    CONCATENATE(
    (SELECT totalUrl FROM cms_article x WHERE a.aid = x.aid AND language = 'EN')
    , '|')
  ELSE ''
  END,
  CASE WHEN (SELECT totalUrl FROM cms_article x WHERE a.aid = x.aid AND language = 'DU')
  THEN 
    CONCATENATE(
    (SELECT totalUrl FROM cms_article x WHERE a.aid = x.aid AND language = 'DU')
    , '|')
  ELSE ''
  END) as agregatedUrl
FROM cms_content a