MySQLで集計するときに使える小技

期間を分けて集計

過去7日間とその前の7日間を比較するといった場合は、CASE文が使えます。

クエリ

SELECT CASE
           WHEN (DATE(m.created_at) >= DATE_ADD(CURRENT_DATE(), INTERVAL - 7 DAY)) THEN "過去7日間"
           ELSE "比較期間"
       END AS period,
       COUNT(DISTINCT m.id) AS count
FROM members AS m
WHERE DATE(m.created_at) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY period

期間毎に集計する

対象期間を、7日間毎や30分毎に分けて集計したい場合は、TRANCATE()が使えます。 以下は、過去28日間の登録者数を7日間毎に集計するという例です。

クエリ

SELECT if(TRUNCATE((DATEDIFF(CURRENT_DATE(), DATE(m.created_at)) - 1) / 7, 0) >= 1, "比較期間", "過去7日間") AS period,
       COUNT(DISTINCT m.id) AS count
FROM members AS m
WHERE DATE(m.created_at) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY TRUNCATE((DATEDIFF(CURRENT_DATE(), DATE(m.created_at)) - 1) / 7, 0)

(DATEDIFF(CURRENT_DATE(), DATE(m.created_at)) - 1)で、今日とm.created_atの差を出します。 -1しているので、差は0~13になります。

この差を7で割り、少数点以下をTRANCATE()で切り捨てることで、0~3のグループに分けることができます。

Macにrbenvを入れてからRails + Vue.jsをインストールする

rbenvでRubyのバージョン管理

$ brew update
$ brew install rbenv ruby-build
$ rbenv --version
  rbenv 1.1.1
$ rbenv install --list
$ rbenv install 2.4.1
$ rbenv global 2.4.1
$ rbenv rehash
$ ruby -v
  ruby 2.4.1p111 (2017-03-22 revision 58053) [x86_64-darwin15]

Railsを導入する

$ gem install bundler
$ mkdir new_app
$ cd new_app
$ bundle init

# gem 'rails'のコメントアウトをはずす
$ vi Gemfile 

# オプション指定でディレクトリ配下のvendor/bundleにインストール
$ bundle install --path vendor/bundle --jobs=4

# オプションを確認
$ bundle exec rails new -h 

# カレントディレクトリに作成。GemfileはOverwriteするか聞かれるのでY。
$ bundle exec rails new ./ -B -d mysql --skip-turbolinks --skip-test-unit --webpack=vue

サーバーを起動して、http://localhost:3000で確認。

$bundle exec rails s

Vueを入れる

Rails 5.1からはwebpackをサポートするようになったそうで、Vueを簡単に導入できます。

# Gemfileに`gem 'webpacker'`を追加
$ vi Gemfile
$ bundle install

# webpackとvueのインストール
$ bundle exec rails webpacker:install
$ bundle exec rails webpacker:install:vue

webpackまたはwebpack-dev-serverを使うことで、webpackを起動することができます。 webpack-dev-serverの場合は、以下のようなことをしてくれるそうですが、今回は試していません。

  • ファイルの変更を検知して、自動リビルド&ブラウザの自動リロード
  • ローカルサーバーを起動
# 以下のようなエラーがでる
$ bin/webpack
 Your `bin/bundle` was not generated by Bundler, so this binstub cannot run.
 Replace `bin/bundle` by running `bundle binstubs bundler --force`, then run this command again.


# エラーのコメント通りに実行
$ bundle binstubs bundler --force

# 再実行
$ bin/webpack

これで最低限恩開発環境のセットは終了です。

参考: - Rails 5.1 + Vue.js で開発を行う - part1 環境構築 - Qiita - 【動画付き】Rails 5.1で作るVue.jsアプリケーション ~Herokuデプロイからシステムテストまで~ - Qiita - rails newの先輩オススメ手順 - まえとうしろ

AWS S3からほかのサーバーにファイルを一括でダウンロードする

AWS S3からファイルを一括ダウンロードするには、公式クライアントであるaws-cliが便利です。 ほかにもツールがあるようですが、今回の用途のためには公式ツール一択と考えて良いでしょう。

aws-cliをダウンロード

S3からほかのサーバーにファイルを移行する場合は、aws-cliが便利です。 まずは、移行先のサーバーにaws-cliをインストールします。

公式ドキュメントは以下の通り。

docs.aws.amazon.com

Python 2.6.5以上またはPython3.3以上が必要なので、もしインストールされていなければインストールします。

yum info python
yum install -y python python-pip
pip install awscli --upgrade --user

awsコマンドを使えるようにパスを通して、aws --versionで確認をします。

$ ls ~/.local/bin
  aws
$ export PATH=~/.local/bin:$PATH
$ source ~/.bash_profile
$ aws --version
  aws-cli/1.11.84 Python/3.6.2 Linux/4.4.0-59-generic botocore/1.5.47

次にaws-cliの設定を行います。

docs.aws.amazon.com

aws-cliの設定には、IAMユーザーのアクセスキーとシークレットアクセスキーが必要です。 IAMユーザーとは、Identity and Access Managementを省略されたもので、ユーザー毎にアクセス権限が設定されています。

初めて作成するAWS アカウントはルートユーザーと呼ばれ、AWS上のすべてのサービスに対して完全なアクセス権限を持っています。 サーバーで言うrootユーザーと同じで権限が強すぎるため、適切に権限を割り当てたIAMユーザーを作成して、これを使うことが推奨されています。

まだ、ユーザーを作成していない場合やユーザーを追加したい場合は、IAMコンソールにアクセスし「ユーザー追加」をクリックします。 作成途中の権限設定で、「AmazonS3FullAccess」を選択するようにしてください。

$ aws configure
  AWS Access Key ID [None]: アクセスキーを入力
  AWS Secret Access Key [None]: シークレットアクセスキーを入力
  Default region name [None]: 東京の場合はap-northeast-1と入力
  Default output format [None]: json

設定がうまくいっているか確認するために、s3のバケットを確認します。

$ aws s3 ls
  2016-10-17 18:49:32 example.com

S3からダウンロード

ファイルのダウンロードはコマンドひとつで可能です。 ディレクトリ内のファイルをすべてダウンロードする場合は、オプション--recursiveをつけます。

aws s3 cp s3://バケット名/ディレクトリ/ /保存先ディレクトリ/ --recursive

たとえば、以下のようなコマンドになります。

aws s3 cp s3://example.com/wp-content/uploads/ /var/www/html/wp-content/ --recursive

kaminariを使って外部APIのページネーションをする

外部のAPIから取得したデータをkaminariを使ってページングするときのメモです。

結論

kaminariのpaginate_arrayメソッドに、以下のように配列とtotal_countを指定することでできる。

@posts = Kaminari.paginate_array(配列, total_count: 総数)
  .page(現在のページ)
  .per(各ページ毎の表示数)

具体例

今回は、jsonを出力するAPIを用意し、そのjsonをパースしてページネーションをするということをします。

API

投稿一覧を出力するだけのシンプルなAPIを作成します。 こちらもKaminariをインストール済みとします。

コントローラー

class PostsController < ApplicationController

  def index
    @posts = Post.published.page(params[:page]).per(1)

    respond_to do |format|
      format.html { render :index }
      format.json { render 'index.json.jbuilder' }
    end
  end

end

ビュー(jbuilder

json.meta do # kaminariのメソッド
  json.total_pages @posts.total_pages
  json.current_page @posts.current_page
  json.total_count @posts.total_count
  json.limit_value @posts.limit_value
end
json.data do
  json.array! @posts do |post|
    json.id post.id
    json.title post.title
  end
end

json

以下のようなjsonが出力されます。

{
  "meta":{
    "total_pages":2,
    "current_page":1,
    "total_count":2,
    "limit_value":1
  },
  "data":[
    {
      "id":1,
      "title":"タイトル1",
    }
    {
      "id":2,
      "title":"タイトル2",
    }
  ]
}

ページネーション

コントローラー

class ExternalPostsController < ApplicationController

  def index
    require 'net/http'
    require 'uri'
    require 'json'

    end_point = "https://sample.com/posts.json?page=#{params[:page]}"
    uri = URI.parse(end_point)
    response = Net::HTTP.get_response(uri)
    body = JSON.parse(response.body)
    meta = body["meta"]

    @posts = Kaminari.paginate_array(body["data"], total_count: meta["total_count"])
      .page(meta["current_page"])
      .per(meta["limit_value"])
  end
end

ビュー

- @posts.each do |post|
    # postを表示する処理
- end

= paginate @posts

jQueryで複数のフォームに文字数カウンターを表示する

文字数カウンターを複数フォームに表示するためのjQueryです。

コード

HTMLは以下のようにします。

.input-field以下に文字数を表示する.input-field__countをつくり、データ属性(data-count)に最大文字数を設定します。

<div class="input-field">
  <input type="text">
  <div class="input-field__count" data-count="45"></div>
</div>

jQueryは以下のようにします。 すべてのtextarea, input[type=text]に文字数カウンターを表示します。

$(function(){
  function countInputText(input) {
    //文字数を表示する.input-field__countを取得
    var counter = $(input).parents('.input-field').find(".input-field__count");

    // データ属性(data-count)から最大文字数を取得
    var maxLength = counter.data("count");

    // 現在の文字数を取得
    var currentLength = $(input).val().length;
    var htmlVal = currentLength + " / " + maxLength;

   // 現在の文字数と最大文字数を比較して、
   // 現在の文字数が多ければ赤く表示する
    if ( parseInt(currentLength) >= parseInt(maxLength) ) {
      counter.html("<span style='color:red'>" + htmlVal + "</span>");
    } else {
      counter.html(htmlVal);
    }
  }

  // ページ表示時に文字数をカウントして表示
  $('textarea, input[type=text]').each(function(){
    countInputText($(this));
  });

  // フォーム入力時に文字数をカウントして表示
  $('textarea, input[type=text]').on('keydown keyup keypress change',function(){
    countInputText($(this));
  });
});

RailsでRSSフィードを作成する:builder

Railsでbuilderを使ってRSSフィードを追加するためのメモです。

要件

  • Gemを追加しない
  • ルーティングを指定できる
  • 動的に更新できる

最終的には、http://example.com/feed/RSSフィードを提供することが目的です。

今回は、上記の要件を満たすためにbuilderを使ってRSSフィードを作成しました。

参照: Builder::XmlMarkup - APIdock

ルーティング:routes.rb

routes.rbにルーティングを記載します。このとき、デフォルトのフォーマットを指定するのを忘れないようにしてください。

get :feed, to: 'rss#index', defaults: { format: :rss }

コントローラー:rss_controller.rb

コントローラー側ではあまりやることはなく、@postsを取得するのみです。

atomなどフォーマット毎に、RSSを提供する場合はrespond_toを使って指定します。

class RssController < ApplicationController
  layout false
  def index
    @posts = Post.published.limit(10)

    respond_to do |format|
      format.rss
      format.atom
    end
  end
end

ビュー:index.rss.builder

appview/rssにindex.rss.builderを作成します。

xml.instruct! :xml, :version => "1.0"
xml.rss(
  "version" => "2.0",
  "xmlns:content" => "http://purl.org/rss/1.0/modules/content/",
  "xmlns:wfw" => "http://wellformedweb.org/CommentAPI/",
  "xmlns:dc" => "http://purl.org/dc/elements/1.1/",
  "xmlns:atom" => "http://www.w3.org/2005/Atom",
  "xmlns:sy" => "http://purl.org/rss/1.0/modules/syndication/",
  "xmlns:slash" => "http://purl.org/rss/1.0/modules/slash/"
) do
  xml.channel do
    xml.title "サイト名"
    xml.description "説明"
    xml.link "サイトURL"
    xml.language "ja-ja"
    xml.ttl "40"
    xml.pubDate(Time.now.strftime("%a, %d %b %Y %H:%M:%S %Z"))
    xml.atom :link, "href" => "RSSのURL", "rel" => "self", "type" => "application/rss+xml"
    @posts.each do |p|
      xml.item do
        xml.title p.title #タイトル
        xml.description do
          xml.cdata! strip_tags(p.content)[0..110] #本文
        end
        xml.pubDate p.created_at #公開日
        xml.guid "http://example.com/#{p.id}"
        xml.link "http://example.com/#{p.id}"
      end
    end
  end
end

これで完了です。

Rails5でコントローラー別に実行するJavaScriptを分ける

Railsでコントローラー毎に実行するJavaScriptを分けたいときの対応方法です。

対応方法としては、

  1. コントローラー毎にファイルを作り読み込む
  2. ファイルは1つにしてコントローラー毎に実行するJavascriptを分ける

という方法があるかと思います。

1の方法でも良いのですが、Turbolinksを使うのであればファイルを分けるのは望ましくないので2の方法で実装します。

1. HTMLにコントローラー名を設定

JavaScriptからコントローラーを取得するために、HTMLのカスタムデータ属性を使いコントローラー名を設定します。 bodyタグでカスタムデータ属性を使っていますが、ほかの方法でも問題ありません。

Slimでカスタムデータ属性を設定する場合。

body data-controller="#{params[:controller]}"

2. コントローラー名を取得する

HTMLからコントローラー名を取得します。

var controller = $('body').data('controller');

JavaScriptの量が少なく、とにかくシンプルにコントローラー毎に処理を分けたいという場合は、以下のように単純な分岐でも良いかもしれません。

if ( controller == "コントローラー名") {
  //コントローラー別の処理
}

3. 初期化処理をする

しかし、これでは分岐が増えると大変なことになるので、以下のような初期化処理をつくります。 コントローラー名と同じコンストラクタをインスタンス化して実行するという非常にシンプルなものです。

$(document).on('turbolinks:load', function(){
  var controller = $('body').data('controller');
  if (window[controller]) {
    new window[controller];
  }
});

JavaScriptの場合は、以下のようにコントローラー別にコンストラクタをつくります。

var user = function() {
  console.log("userコントローラーで実行されます");
}

CoffeeScriptを使う場合は、以下のようにコンストラクタの名前に@を付けて外部から参照できるようにしてください。

@user = ->
    console.log("userコントローラーで実行されます")

コントローラー名と同じコンストラクタを作ればインスタンス化して実行するようになります。これで完了です。

[おまけ] RailsScriptを使う

RailsScriptというGemを使うことでも可能です。

RailsScriptも内部の実装は、上記の方法とほぼ同じようなものです。

しかし、使い方が悪かったのか、Turbolinksとあわせると画面遷移したときにJavaScriptが実行されなかったので採用していません。

Gemfileにgem 'rails_script', '~> 2.0'を追加をしてインストール。

bundle install
bundle exec rails g rails_script:install
Running via Spring preloader in process 3112
      create  app/assets/javascripts/base.coffee
      create  app/assets/javascripts/global.coffee
      append  app/assets/javascripts/application.js

layout/application.html.slimnclude_rails_scriptを追加

application.jsに設定を追加。

//= require rails_script
//= require_tree .

こんな感じで使えます。

class App.Users extends App.Base

   show: =>
      console.log 'users#show'

参考