CakePHPで複数joinする方法 INNER JOIN

複数テーブルからリレーション関係にある
カラム同士を紐付けて(INNER JOIN)クエリー実行。

            $options =  array(
                ‘fields’ => $val[‘fields’],
                ‘conditions’ => array(
                    ‘GrouponList.category_id’ => $category_id,
                ),
                ‘limit’ => $val[‘limit’],
                ‘order’ => $val[‘order’],
                ‘group’ => $val[‘group’],
                ‘joins’ => array(
                    array(
                        ‘type’ => ‘INNER’,
                        ‘table’ => ‘groupon_lists’,
                        ‘alias’ => ‘GrouponList’,
                        ‘conditions’ => array(
                            ‘SoBookmark.groupon_list_id = GrouponList.id’,
                        )
                    ),
                    array(
                        ‘type’ => ‘INNER’,
                        ‘table’ => ‘tw_counts’,
                        ‘alias’ => ‘TwCount’,
                        ‘conditions’ => array(
                            ‘SoBookmark.groupon_list_id = TwCount.id’,
                        )
                    ),
                ),
            );

        $this->set($key, $this->SoBookmark->find(‘all’, $options) );

CakePHPのACLで権限一覧を見る時のMySQLクエリー

# MySQLクエリー – パーミッション一覧
SELECT
  aros_acos.id AS aros_acos_id,
  aros.model,
  groups.name,
  acos.alias,
  acos.lft,
  acos.rght,
  aros_acos._create,
  aros_acos._read,
  aros_acos._update,
  aros_acos._delete
FROM
  aros_acos
LEFT JOIN aros   ON aros_acos.aro_id = aros.id
LEFT JOIN acos   ON aros_acos.aco_id = acos.id
LEFT JOIN groups ON aros.foreign_key = groups.id
;

↓↓↓ こんな感じの結果になる ↓↓↓

aros_acos_id model name alias lft rght _create _read _update _delete
115 Group users edit 81 82 1 1 1 1
97 Group administrators logout 51 52 -1 -1 -1 -1
98 Group administrators view 47 48 -1 -1 -1 -1

CakePHP で SQL Error : 2006 MySQL server has gone away

mysql_ping() を実行すると延長できるので、
ループ処理内とかで実行するようにすればOK。

■環境
 ・さくらインターネット
 ・CakePHP 1.2.x
 ・MySQL 5.1.x

■経緯
 CakePHPの appvendorsshells にあるプログラムを
 定期的にcronで実行していたところ、

 「SQL Error : 2006 MySQL server has gone away」

 というエラーが発生し、DBにinsertできていなかった。

■原因
 MySQL側のwait_timeout(DB接続をopenにしている時間)の時間が
 過ぎてしまい、DB接続がcloseしてしまった。

■理由
 さくらインターネットでは、 wait_timeout = 300 (5分) となっていて、
 処理が5分以上かかっていたため、DB接続がcloseになっていた。
 (wait_timeout は、MySQLで「SHOW VARIABLES」コマンドで確認できる)

■対応策
 案1.my.cnf で wait_timeout を伸ばす
 → さくらインターネットでは編集できない

 案2.PHP側で mysql_query(‘SET GLOBAL WAIT_timeout=3000’)を実行し
 時間を延長する
 → さくらインターネットでは変更権限がない

 案3.ループ処理内でmysql_ping関数を実行する
 → これで解決。ループ処理ごとに都度DB接続のwait_timeoutが延長できた

■英語用
 [Q]
 What can I fix “SQL Error : 2006 MySQL server has gone away” error for CakePHP?
 I can’t edit my.cnf and excute mysql_query “SET”.

 [A]
 Run mysql_ping() to end of loop program. You can extend MySQL wait_timeout when end of loop program.

MySQLでクロス集計する方法

■クロス集計で行数をカウント
SELECT `list_id`, FIELD(`category_id`, 10), FIELD(`category_id`, 11)  FROM `bookmarks` GROUP BY `list_id`;

■カウントした行数に値を掛けてクロス集計結果
SELECT `list_id`, SUM(FIELD(`category_id`, 10) * `count`), SUM(FIELD(`category_id`, 11) * `count`) FROM `bookmarks` GROUP BY `list_id`;

■参考サイト

 MySQLでクロス集計 日々是好日/ウェブリブログ

CakePHP 月ごとにサイト数をカウントするクエリー

月ごとにサイト数をカウントするクエリー
(CakePHPでMySQLのクエリーを実行)

        $query = $this->SiteList->query(“
            SELECT
                DATE_FORMAT(`launch_date`, ‘%Y-%m’),
                    COUNT(DATE_FORMAT(`launch_date`, ‘%W %M %Y’))
            FROM
                `site_lists`
            WHERE
                `launch_date` > ‘2010-01’ AND `category_id` = 1
            GROUP BY
                DATE_FORMAT(`launch_date`, ‘%Y-%m’);”)
            ;