mysql - 使用内部 n 引用创建 View

Question

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

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

不同的文章也可以通过reference列进行嵌套，引用cms_article中的id列如果是顶级文章这个cms_article.ref = NULL

表格:

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

我想从这两个表中创建一个可用于查找 URL 的 View 。因此，我想为 cms_article 中的每个给定元组构建一个 totalURL 字段，并将每个父/ref 作为 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 关系中的一篇文章和 1 个元组。 URL 字段来自相应的 cms_content.url 字段。

如何将每个元组的 totalURL 聚合为一个 View ，以便生成如下 View :

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 中找到引用(并为每个级别和每个元组重复此操作)。但由于这给服务器带来了很大压力，我正在寻找像 View 这样的优雅的 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