#Model(post.rb)
class Post < ApplicationRecord
  acts_as_taggable_on :labels
  acts_as_taggable #この2行の指定でタグ関連テーブルと紐付けられる

###中略###

  scope :find_by_tag, -> tags {
    #タグが入力されている場合のみタグを条件に抽出
    if tags.present?
      tagged_with(tags, any: true) #タグ付けレコードの取得方法を指定
    end
  }

end

#Controller(posts_controller.rb)
class PostsController < ApplicationController

###中略###

  def search
    @tags = params[:tags]
    @posts = current_user.posts.find_by_tag(@tags) #current_userはログインユーザー取得のヘルパーメソッド
    respond_to do |format|
      format.js
    end
  end

end

/* js.erb(search.js.erb) */
var searchResultHtml = "";
var count = 0;
// 検索結果が存在する場合のみカウントを取得
<%if @posts.exists? %>
  searchResultHtml = '<%=j render @posts%>';
  count = <%=@posts.count%>
<%end%>
$('#post-search-result-table').html(searchResultHtml);
$('span#search-result-count').html(count.toString());

タグと関連付けたModelに対しtagged_with(tags, any: true)というメソッドを使うことで、検索条件で指定したタグのいずれかを含むレコードが検索できます。
今回の実装ではajaxで検索結果を表示するようviewというかformで指定しているため、controllerでインスタンス変数にセットした検索結果をjs.erbで画面に反映しています。

検索条件に指定したタグすべてを含むレコードを検索

上記の実装でtagged_with(tags, any: true)としていた個所をtagged_with(tags, match_all: true)とします(“any"を"match_all"に変えている)。

詰まったところ

どちらの取得方法でも件数を取得するとこで詰まりました。。

検索条件に指定したタグのいずれかを含むレコードを検索した場合の件数取得

上記の実装でタグを条件に含む検索を実行すると、見事にエラー。こんなログ出ました(見やすいように改行を入れています)。

ActionView::Template::Error (SQLite3::SQLException: near "*": syntax error: 
SELECT COUNT( posts.*) FROM "posts" WHERE "posts"."user_id" = ? 
AND (EXISTS (SELECT 1 FROM taggings posts_taggings_7e240de 
WHERE posts_taggings_7e240de.taggable_id = posts.id 
AND posts_taggings_7e240de.taggable_type = 'Post' 
AND posts_taggings_7e240de.tag_id in (2)
))):

おいおいまさかのSyntax error…
ぱっと見何がダメなのか分からなかったのですが、よくよく見てみると。。。最後のカッコが1つ多いような。。。

GitHubのリポジトリ見てみたら、issueに挙がってました。

.tagged_with([], any: true).count throws MySQL error · Issue #649 · mbleigh/acts-as-taggable-on · GitHub

issueはopenのままなんですが、解決方法を書いてくれてる人が。
どうやら.count⇒.count(:all)とすれば解消するようです。。

/* 修正後のsearch.js.erb */
var searchResultHtml = "";
var count = 0;
<%if @posts.exists? %>
  searchResultHtml = '<%=j render @posts%>';
  count = <%=@posts.count(:all)%> // .count⇒.count(:all)
<%end%>
$('#post-search-result-table').html(searchResultHtml);
$('span#search-result-count').html(count.toString());

修正後の内容で発行されるクエリはこんな感じになりました。カッコの数が正しくなってます。あとcountの取り方が変わってますね(ここだけ見ても結果は同じですが)。

SELECT COUNT(*) FROM "posts" WHERE "posts"."user_id" = ?
AND (EXISTS (SELECT 1 FROM taggings posts_taggings_a94a8fe
 WHERE posts_taggings_a94a8fe.taggable_id = posts.id
 AND posts_taggings_a94a8fe.taggable_type = 'Post'
 AND posts_taggings_a94a8fe.tag_id in (1)
))

検索条件に指定したタグすべてを含むレコードを検索した場合の件数取得

こちらも件数がちゃんと取得できておらず。
こちらはログに出力されたクエリがこんな感じになってました。

/* タグあり、match_all指定 */
SELECT COUNT(*) AS count_all,
       posts.id AS posts_id
FROM "posts"
JOIN taggings posts_taggings_a94a8fe
ON  posts_taggings_a94a8fe.taggable_id = posts.id
AND posts_taggings_a94a8fe.taggable_type = 'Post'
AND posts_taggings_a94a8fe.tag_id = 1
LEFT OUTER JOIN taggings posts_taggings_group
ON  posts_taggings_group.taggable_id = posts.id
AND posts_taggings_group.taggable_type = 'Post'
WHERE "posts"."user_id" = ?
GROUP BY posts.id
HAVING (COUNT(posts_taggings_group.taggable_id) = 1)
ORDER BY "posts"."created_at" DESC

どうもcountだけじゃなくpostのIDも取得されてしまってるようです。
結果はHashとして、{“1” => “1”,“1” => “2”}(件数 => ID)のような形で返却されます。
postのIDごとにgroup byしてるけど、件数はすべて1件ずつになる気がするので.count.lengthでHashの要素数取れば合計の件数が取れるかなぁ。
しかしタグを指定していないと、こんな感じでcountだけを取得するクエリになっています。

/* タグなし */
SELECT COUNT(*)
FROM "posts"
WHERE "posts"."user_id" = ?

タグが指定されてるかどうかで件数の取り方を変えなあかん、てことでこんな実装になりました。汚い。

/* match_all指定時のjs.erb実装 */
var searchResultHtml = "";
var count = "0";
<%if @posts.exists? %>
  searchResultHtml = '<%=j render @posts%>';
  // タグを検索条件に含めるとModel#countの結果がHashとなってしまうため
  // hashの長さを取得(件数分hashの要素が作られる想定)
  <%if @tags.present?%>
    count = <%=@posts.count.length%>;
  <%elsif%>
    count = <%=@posts.count%>
  <%end%>
<%end%>
$('#post-search-result-table').html(searchResultHtml);
$('span#search-result-count').html(count.toString());

うーん詰まりまくったけどひとまずこんな感じでOKかなぁ。

railsというかrubyというか、まだまだ色々分かってないので詰まるとなかなか大変です。

長くなってしまいましたが今回はこの辺で。

2017-04-02

PostgreSQLのALTER TABLEに苦戦

PostgreSQL H2 SQL

PlayframeworkではDDLを予め用意しておくことでアプリケーションの起動時にDDLを実行してテーブルを自動生成してくれます(evolutions)。
※Java版だとModelからDDLを自動生成してくれますが、Scala版だとDDLは自分で書く必要があります(という認識)。

自作アプリケーション作成時に、ローカルではH2、本番(Heroku)ではPostgreSQLを使用しています。
ところが、ローカルでは実行できていたDDLがHerokuではコケるという事象が。
PostgreSQLのエラーログがいまいちわかりづらくてなかなか原因が把握しづらかったのですが、どうやら以下の通りらしい。

H2とPostgreSQLではALTER TABLEの文法が異なる

今回コケたのはALTER TABLE文で、カラムの型変更をしようとしてました。

/* H2 */
alter table TABLE1 alter column COLUMN1 (DATATYPE); 
/* PostgreSQL */
alter table TABLE1 alter column COLUMN1 [SET DATA] TYPE (DATATYPE);

(DATATYPE)は変更後の型、[]内の有無は任意です。

さらに、今回カラムの型変更と同時にデフォルト値の設定をしようとしたのですが、H2では1文で実行できる一方、PostgreSQLでは2文に分けないと実行できないようです。

/* H2 */
alter table TABLE1 alter column COLUMN1 TIMESTAMP SET DEFAULT CURRENT_TIMESTAMP;
/* PostgreSQL */
alter table TABLE1 alter column COLUMN1 [SET DATA] TYPE TIMESTAMP;
alter table TABLE1 alter column COLUMN1 SET DEFAULT CURRENT_TIMESTAMP;