ruby-on-rails - 如何使我在 Rails 中的数据库查询方法更有效率

Question

我正在查询我的 POSTGRESQL 数据库。我的应用程序有文章，文章可以有多个标签。这些关系保存在标签和文章的连接表中。

我有一个工作方法可以返回具有特定主题标签的文章，或者返回所有不包含特定主题标签的文章

  def test(hashtags, include = true)
    articles= []
    hashtags.split(' ').each do |h|
      articles+= Article.joins(:hashtags).where('LOWER(hashtags.value) LIKE LOWER(?)', "#{h}")
    end
    if include
      articles.uniq
    else 
      (Article.all.to_set - articles.uniq.to_set).to_a
    end
  end

我可以这样调用它:

test("politics people china", true)

它会给我所有具有与以下主题标签之一相关的文章

或者我可以这样调用它

test("politics people china", false)

它会给我所有的文章，除了那些拥有这些主题标签之一的文章

它运行良好，但我认为这不是很有效，因为我在 Ruby 中做了很多事情，而不是在数据库级别。

我试过这个:

def test2(hashtags, include = true)
    articles= []
    pattern = ''
    hashtags.split(' ').each do |h|
      pattern += "#{h}|"
    end
    pattern = '(' + pattern[0...-1] + ')'

    if include
      articles = Article.joins(:hashtags).where('hashtags.value ~* ?', "#{pattern}")
    else 
      articles = Article.joins(:hashtags).where('hashtags.value !~* ?', "#{pattern}")
    end

    articles.uniq
  end

但它的行为并不像我想象的那样。首先，如果我这样调用它:

test2("politics china", true)

它不仅会给我所有带有标签 politics 或 china 的文章，还会给我所有带有包含 中的字母之一的标签的文章>politics 或 china 像这样:

(p|o|l|i|t|c|s|h|n|a)

但它实际上应该检查这一点，并且模式实际上看起来像这样，我在控制台中可以看到:

(politics|china)

我发现这并不奇怪……

和

test2("politics", false)

它只给我有一个或多个主题标签关联的文章，但忽略了那些根本没有主题标签的文章

有人可以帮助我提高工作效率吗？

编辑:这是我更新后的代码，就像答案中建议的那样

def test2(hashtags, include = false)    
    hashtags = 
    if include 
      Hashtag.where("LOWER(value) iLIKE ANY ( array[?] )", hashtags)
    else
      Hashtag.where("LOWER(value) NOT iLIKE ANY ( array[?] )", hashtags)
    end
    Slot.joins(:hashtags).merge(hashtags).distinct
  end

不幸的是，如果 incude 是假的，它仍然缺乏给我没有主题标签的文章

Answer 1

你是对的

I dont think this is very efficient as I do so much in Ruby and not on DB level.

ActiveRecord 适用于简单的查询，但当事情变得复杂时，使用纯 SQL 是合理的。因此，让我们尝试构建一个与您的测试用例匹配的查询:

1) 对于此调用 test("politics people china", true) 查询可能如下所示:

SELECT DISTINCT ON (AR.id) AR.*
FROM articles AR
  JOIN articles_hashtags AHSH ON AHSH.article_id = AR.id
  JOIN hashtags HSH ON HSH.id = AHSH.hashtag_id
WHERE LOWER(HSH.value) IN ('politics', 'people', 'china')
ORDER BY AR.id;

(我不确定您的连接表是如何命名的，所以假设它是 articles_hashtags)。

简单明了:我们使用 2 个内部联接从 articles 表中获取数据，其中包含 articles_hashtags 和 hashtags 以及 where 条件，过滤我们想看的标签；最终它为我们带来了所有带有该主题标签的文章。无论我们要过滤多少个主题标签:即使列表中只有一个主题标签，IN 语句也能正常工作。

请备注DISTINCT ON : 有必要从结果集中删除重复的文章，以防同一篇文章在给定主题标签列表中有多个主题标签。

2) 对于调用 test("politics people china", false) ，查询有点复杂。它需要排除带有主题标签的文章。因此，它应该返回具有不同主题标签的文章，以及根本没有主题标签的文章。为了简单起见，我们可以使用之前的查询:

SELECT A.*
FROM articles A
WHERE A.id NOT IN (
    SELECT DISTINCT ON (AR.id) AR.id
    FROM articles AR
      JOIN articles_hashtags AHSH ON AHSH.article_id = AR.id
      JOIN hashtags HSH ON HSH.id = AHSH.hashtag_id
    WHERE LOWER(HSH.value) IN ('politics', 'people', 'china')
    ORDER BY AR.id
);

我们在这里获取所有文章，但不包括那些具有任何给定主题标签的文章。

3) 将这些查询转换为 Ruby 方法可以得到以下结果:

def test3(hashtags, include = true)
  # code guard to prevent SQL-error when there are no hashtags given
  if hashtags.nil? || hashtags.strip.blank?
    return include ? [] : Article.all.to_a
  end

  basic_query = "
    SELECT DISTINCT ON (AR.id) AR.*
    FROM #{Article.table_name} AR
      JOIN articles_hashtags AHSH ON AHSH.article_id = AR.id
      JOIN #{Hashtag.table_name} HSH ON HSH.id = AHSH.hashtag_id
    WHERE LOWER(HSH.value) IN (:hashtags)
    ORDER BY AR.id"

  query = if include
            basic_query
          else
            "SELECT A.*
            FROM #{Article.table_name} A
            WHERE A.id NOT IN (#{basic_query.sub('AR.*', 'AR.id')})"
          end

  hashtag_arr = hashtags.split(' ').map(&:downcase) # to convert hashtags string into a list

  Article.find_by_sql [query, { hashtags: hashtag_arr }]
end

上面的方法将返回符合条件的文章数组，无论是否为空。