ビューのメモ(SQLite)

このエントリーをはてなブックマークに追加

やり方忘れそうなのでメモ。

このスクリプトで使用するテーブルは以下の4つです。

グループ=大カテゴリ、エントリ=中カテゴリ、エントリタイプ=小カテゴリ。
不要なカラムは省略しています。

pict_group

gidgp_dirgp_sortkeygp_name
1 button 2 ボタン
2 background 1 背景画像
3 icon 3 アイコン

pict_entry

eidgidentry_direntry_nameentry_entrydate
1 1 004 ドット#01 2000-09-13 00:00:00
2 1 008 バーコード#01 2000-09-13 00:00:00
3 2 008 花#01 2000-10-20 00:00:00
4 3 001 矢印 2000-09-13 00:00:00

pict_entry_type

tideidtype_dirtype_nametype_widthtype_height
1 1 a あお 61 31
2 1 b あお 61 31
3 2 a みどり 55 55
4 2 b あお 55 55

pict_log

log_notidlog_date
1 2 2007-03-12 01:00:59

エントリ毎のDL数&最終DL日を使用する為のビュー

CREATE VIEW pict_log_count_view AS
      SELECT pict_entry_type.eid as eid, MAX(pict_log.log_date) as recent_date, COUNT(pict_log.log_no) as dl_num
        FROM pict_entry_type
  INNER JOIN pict_log ON pict_entry_type.tid = pict_log.tid
    GROUP BY pict_entry_type.eid;

このビューは、エントリIDごとの最新ダウンロード日、ダウンロード数をあらかじめ持つテーブルとなります。

クエリ例

以下のようなクエリで使用しています。

全てのグループの一覧を取得。

$query = $this->db->query('
    SELECT pict_group.gid as gid,
           pict_group.gp_name as gp_name,
           COUNT(pict_entry.eid) as entry_num,
           MAX(pict_log.recent_date) as recent_date,
           SUM(pict_log.dl_num) as dl_num
      FROM pict_group
 LEFT JOIN pict_entry ON (pict_group.gid = pict_entry.gid)
 LEFT JOIN pict_log_count_view as pict_log ON pict_entry.eid = pict_log.eid
  GROUP BY pict_group.gid
  ORDER BY gp_sortkey asc
');

最終的に取得したフィールドは以下のとおり。

  • グループID
  • グループ名
  • グループに属するエントリの数
  • グループ全体の最新ダウンロード日
  • グループ全体のダウンロード数

特定グループに登録されているエントリの一覧を取得。

$query = $this->db->query('
    SELECT pict_entry.eid as eid,
           pict_entry.gid as gid,
           pict_entry.entry_status as entry_status,
           pict_entry.entry_dir as entry_dir,
           pict_entry.entry_name as entry_name,
           pict_entry.entry_entrydate as entry_entrydate,
           ifnull(pict_log.dl_num, 0) as dl_num
      FROM pict_entry
 LEFT JOIN pict_log_count_view as pict_log ON pict_entry.eid = pict_log.eid
     WHERE pict_entry.gid = '.intval($this->af->get('gid')).'
  GROUP BY pict_entry.eid
  ORDER BY entry_name asc
');

最終的に取得したフィールドは以下のとおり。

  • エントリID
  • グループID
  • エントリのステータス
  • エントリのディレクトリ
  • エントリ名
  • エントリの登録日
  • エントリのダウンロード数(ない場合は0に置き換える)

あまり使ってないけど。


Edited on 2015.06.27 Created on 2006.08.25 PHPSQLite
PAGE TO TOP