portal shit!

+----------+        +------------+        +-----------+
|          |        |            |        |           |
|   Blog   +---+--->+  Category  +------->+   Entry   |
|          |   |    |            |        |           |
+----------+   |    +------------+        +-----------+
               |
               |
               |    +------------+        +-----------+
               |    |            |        |           |
               +--->+  Category  +---+--->+   Entry   |
                    |            |   |    |           |
                    +------------+   |    +-----------+
                                     |
                                     |
                                     |    +-----------+
                                     |    |           |
                                     +--->+   Entry   |
                                     |    |           |
                                     |    +-----------+
                                     |
                                     |
                                     |    +-----------+
                                     |    |           |
                                     +--->+   Entry   |
                                          |           |
                                          +-----------+

select entries.id
from entries
inner join (
  select
    category_id,
    GROUP_CONCAT(id order by created_at desc) as entry_ids,
    max(created_at) as last_created_at
  from entries
  where entries.draft = false
  group by category_id
) as grouped_entries
on grouped_entries.category_id = entries.category_id
and FIND_IN_SET(id, entry_ids) between 1 and 4
order by last_created_at desc, entries.id desc;

query = <<~SQL
  select entries.id
  from entries
  inner join (
    select
      category_id,
      group_concat(id order by created_at desc) as entry_ids,
      max(created_at) as last_created_at
    from entries
    where entries.draft = false
    group by category_id
  ) as grouped_entries
    on grouped_entries.category_id = entries.category_id
    and find_in_set(id, entry_ids) between 1 and 4
  inner join categories on categories.id = entries.category_id
  order by last_created_at desc, entries.id desc;
SQL
entry_ids = ActiveRecord::Base.connection.select_all(query).rows.flatten
entries = Entry.includes(:category, :user, :tags, :comments).where(id: entry_ids)

- "雑談":303
- "技術/プログラミング":272
- "映画/ドラマ/テレビ":150
- "Mac/iPhone":134
- "WWW":113
- "散財":95
- "旅行/ハイキング":70
- "ブログ":69
- "音楽":63
- "読書":34
- "写真":32
- "料理/食事":31
- "労働":27

- 雑記
- パソコン・インターネット
- 見た・読んだ・聞いた
- 出かけた・撮った・食べた

require 'yaml'
require 'pathname'

files = Dir.glob(File.join(__dir__, '_posts', '*.markdown'))
files.each do |file|
  content = File.read(file)
  _, header, body = content.split('---')
  header_yml = YAML.load(header)
  title = header_yml['title']
  tags = header_yml['category']
  tags = tags.is_a?(Array) ? tags.map(&:downcase) : [tags&.downcase]
  slug = Pathname.new(file).basename.to_s.sub(/\d{4}\-\d{2}\-\d{2}\-(.+?)\.markdown/, '\1').gsub('_', '-')
  body = body.strip.gsub(/\n/, "\\n").gsub('\'', '\'\'')
  created_at = File.birthtime(file).to_s.sub(' +0900', '')
  updated_at = File.mtime(file).to_s.sub(' +0900', '')
  puts <<~EOS
    INSERT INTO entries(title, user_id, category_id, slug, markup, type, draft, body, frozen_tag_list, created_at, updated_at) VALUES('#{title}', 1, 6, '#{slug}', 'redcarpet', 'Post', 0, '#{body}', '#{tags.join(',')}', '#{created_at}', '#{updated_at}');
  EOS
end

  // クリックされたアイテムが `this.state.disabled` 配列の中にすでに存在していれば除外し、
  // 存在してなければ追加する
  selectBar(event) {
    let dataKey = event.dataKey.trim()
    if (this.state.disabled.includes(dataKey)) {
      this.setState({ disabled: this.state.disabled.filter(item => item !== dataKey) })
    } else {
      this.setState({ disabled: this.state.disabled.concat([dataKey]) })
    }
  }

  render() {
    return (
      <ResponsiveContainer height={500}>
        <BarChart
          data={this.state.data}
          margin={{
            top: 20, right: 20, left: 0, bottom: 20,
          }}
          style={{ fontSize: '14px' }}
        >
          <CartesianGrid strokeDasharray="3 3" />
          <XAxis dataKey="year" />
          <YAxis />
          <Tooltip labelStyle={{ color: '#000', fontWeight: 'bold' }} itemStyle={{ margin: '0 2px 0 4px', padding: '0' }} />
          {// Legend クリック時のコールバックに `this.selectBar` を指定する }
          <Legend onClick={this.selectBar} />
          {/*
            `this.state.categories` 配列と `this.state.disabled` 配列の内容を比較し、
            `this.state.disabled` に追加済のカテゴリーは dataKey に空白を追加することで非表示に
          */}
          {this.state.categories.map((category, index) => {
            let dataKey = this.state.disabled.includes(category) ? category + " " : category
            let color = this.colors[index % this.colors.length]
            return(<Bar key={index} dataKey={dataKey} stackId="a" fill={color} />)
          })}
        </BarChart>
      </ResponsiveContainer>
    );
  }

import React, { Component } from 'react'
import ReactDOM from 'react-dom'
import Select from 'react-select'
import PropTypes from 'prop-types'
import { withRouter } from 'react-router-dom'

import history from './history'

class YearSelect extends Component {
  constructor(props) {
    super(props)
    this.state = {
      data: [],
      selectedOption: null
    }
    this.handleChange = this.handleChange.bind(this)
  }

  static propTypes = {
    match: PropTypes.object.isRequired,
    location: PropTypes.object.isRequired,
    history: PropTypes.object.isRequired
  }

  async loadYearSelectFromServer() {
    const request = await fetch('/archives/years.json')
    const response = await request.json()
    this.setState({ data: response })
  }

  componentDidMount() {
    this.loadYearSelectFromServer()
  }

  handleChange(selectedOption) {
    const year = selectedOption ? selectedOption.value : null
    if (year) {
      this.props.history.push(`/archives/${year}`)
    } else {
      this.props.history.push("/archives")
    }
    this.setState(
      { selectedOption },
      () => { this.props.update(year) }
    )
  }

  render() {
    const options = this.state.data.map(year => {
      return { value: year, label: year }
    })
    return (
      <div className="year-list">
        <Select
          value={this.state.selectedOption}
          onChange={this.handleChange}
          options={options}
          placeholder="Year"
          isClearable
        />
      </div>
    )
  }
}

const YearList = withRouter(YearSelect)

export default YearList

import React, { Component } from 'react'
import { BrowserRouter as Router, Switch, Route, Redirect } from 'react-router-dom'

import YearList from './YearList'
import CategoryList from './CategoryList'
import Archives from './Archives'

class App extends Component {
  constructor(props) {
    super(props)
    this.state = {
      category: null,
      year: null,
      length: 0
    }
    this.updateCategory = this.updateCategory.bind(this)
    this.updateYear = this.updateYear.bind(this)
    this.setLength = this.setLength.bind(this)
  }

  updateCategory(category) {
    this.setState({ category })
  }

  updateYear(year) {
    this.setState({ year })
  }

  setLength(length) {
    this.setState({ length })
  }

  render() {
    return(
      <Router history={history}>
        <div className="archive-filter">
          <YearList update={this.updateYear} />
          <CategoryList update={this.updateCategory} activeCategory={this.state.category} />
          <div className="entry-length"><p>{this.state.length} entries</p></div>
        </div>
        <Switch>
          <Route
            exact path="/archives"
            render={(props) =>
              <Archives
                category={this.state.category}
                setLength={this.setLength}
                {...props}
              />
            }
          />
          <Route
            path="/archives/:year(\d{4})"
            render={(props) =>
              <Archives
                category={this.state.category}
                setLength={this.setLength}
                year={this.state.year}
                {...props}
              />
            }
          />
        </Switch>
      </Router>
    )
  }
}

export default App