Cubix

ビューのサンプル(SQLite)

2006.08.25

うちの素材閲覧スクリプトより。

このスクリプトで使用するテーブルは以下の４つです。
グループ＝大カテゴリ、エントリ＝中カテゴリ、エントリタイプ＝小カテゴリ、と考えてください。
説明上不要なカラムは省略しています。

pict_group

gid	gp_dir	gp_sortkey	gp_name
1	button	2	ボタン
2	background	1	背景画像
3	icon	3	アイコン

pict_entry

eid	gid	entry_dir	entry_name	entry_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

tid	eid	type_dir	type_name	type_width	type_height
1	1	a	あお	61	31
2	1	b	あお	61	31
3	2	a	みどり	55	55
4	2	b	あお	55	55

pict_log

log_no	tid	log_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に置き換える）

正直ビューについてはあんまり詳しくありません。