代码之家 › 专栏 › 技术社区 › Ilja KO

如何使Rails中的DB查询方法更有效

postgresql regex ruby-on-rails

Ilja KO · 技术社区 · 7 年前

我正在对我的POSTGRESQL数据库进行查询。我的应用程序有文章和文章可以有一些标签。这些关系保存在Hashtags和Articles的联接表中。

我有一个工作方法,给我回的文章有一定的标签,或给我回所有的文章谁不包含一定的标签

  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)

它会给我所有的文章,除了那些有这些标签的人

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 ,也包括所有拥有包含或 瓷器 像这样:

(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 很不幸是假的

2 回复 | 直到 7 年前

Ilya Konyukhov 7 年前

你说得对

我不认为这是非常有效的,因为我在Ruby中做了这么多,而不是在DB级别。

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

简单明了:我们从 articles 使用2个内部联接的表 文章\u标签 hashtags 和 where IN 即使列表中只有一个hashtag,语句也能正常工作。

请注意 DISTINCT ON :如果同一个项目在给定的hashtag列表中有多个hashtag,则需要从resultset中删除重复的项目。

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

上面的方法将返回与您的条件匹配的项目数组,无论是否为空。

Rodrigo 7 年前

试试这个:

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