sql - 优化多个左连接 SQLite 查询

Question

我有一个帖子表，每个帖子都有标签，每个标签都有一个主题，每个帖子也有关键字，例如：

    Post name: Anything
    Post tag: 1, 2, 3
    Post keywords: 1, 2, 3
    
    Tag 1: is the id of Male
    Tag 2: is the id of United States
    Tag 3: is the id of India
    
    The 1 tag belongs to the gender topic, the 2 and 3 tags belong to the country topic.
    
    Keyword 1: is the id of the Test keyword 1
    Keyword 2: is the id of the Test keyword 2 
    Keyword 3: is the id of the Test keyword 3

但是帖子可能有关键字，也可能没有。

这里的表格是这样的：

    posts:
     - id
     - name
    
    tags:
     - id
     - name
     - topic_id
    
    topics:
     - id
     - name
    
    tags_posts:
     - tag_id
     - post_id
    
    keywords:
     - id
     - keyword
    
    posts_keywords:
     - keyword_id
     - post_id

搜索屏幕允许用户搜索帖子名称、帖子关键字和属于具有 id 的特定主题的标签，1用户也可以通过选择多个标签进行过滤。

现在，如果用户在 UI 上选择了男性和美国，我想做一个这样的过滤器，所以我应该选择所有具有男性和美国标签的帖子。

    SELECT * FROM posts where tag_relation = Male and tag_relation = United States
    
    tag_relation here means JOIN to tags_posts table

但是如果用户在 UI 上选择了男性、美国和印度，那么我应该选择所有具有男性和（美国或印度）标签的帖子。

    SELECT * FROM posts where tag_relation = Male and (tag_relation = United States or tag_relation = India)
    
    tag_relation here means JOIN to tags_posts table

如您所见，标签是否属于同一主题，则应为 OR，但如果它们属于不同的主题，则应为 AND。

最后，我想出了一个解决方案，但是在移动设备上运行需要太多时间，这是我的解决方案

    SELECT posts.id, posts.name FROM posts
    LEFT JOIN posts_texts ON posts_texts.post_id = posts.id
    LEFT JOIN texts ON texts.id = posts_texts.text_id
    LEFT JOIN posts_tags ON posts_tags.post_id = posts.id
    LEFT JOIN tags ON tags.id = posts_tags.tag_id
    WHERE CASE WHEN 1 THEN posts.id in
    (SELECT tags_relation.post_id FROM tags tag
    LEFT JOIN posts_tags tags_relation ON tags_relation.tag_id = tag.id
    WHERE tag.name IN ('Male')
    GROUP BY tags_relation.post_id
    HAVING count(DISTINCT tag.topic_id) = (SELECT count(DISTINCT tag.topic_id) FROM tags tag
    WHERE tag.name IN ('Male'))) ELSE 1 END
    AND (posts.name LIKE '%' || 'a' || '%' OR texts.name LIKE '%' || 'a' || '%' OR tags.name LIKE '%' || 'a' || '%')
    GROUP BY posts.id

我正在WHERE CASE WHEN 1 THEN根据用户是否选择标签从代码中更改它

标签： sqldatabasesqlite