2011年05月30日

cakephp使いの為のsql構文

お願いします♪いいねとかつぶやいたりして下さい
 
cakephpのfind all とかそういうのを使いまくれば、別にmysql文なんて理解して無くても良い。
ただ、ちょっとだけ凝ったsqlを発行するときにごりごり書くとそれだけで相当処理が重くなる。
今回は、cakephpを使っていてあんまりsql構文を知らない人のために、構文をまとめてみた。

updateAll で primarykey 以外の条件での更新


$this->Girl->updateAll(
  array('Girl.girlphoto' => "'".$res['Attachment']['basename']."'"), //何に更新する?
  array(array('Girl.user_id' => u('id')))//どのレコードを更新する?
);		



mediaplugin 使用時などの hasMany関連モデルの件数をcount


これでAttachmentのalternativeがTeller のファイルを持っているユーザーのみを取得する。
また、何件持っているかを attachment_count に格納する。
一応 contain で attachment 1件だけってできるが、逆にsqlが重くなるので使わない方がいいかも。


function show()
{
    $this->paginate = array('Teller' =>  
    array(  
          'limit' => 10,  
          'order' => array('Teller.id' => 'desc'),  
          'joins' => array(  
          array('type' => 'inner', 'alias' => 'Attachment', 'table' => 'aiura_attachments',  
                'conditions' => 'Teller.id = Attachment.foreign_key')  
            ),
            /* 'contain' => array(
                'Attachment' => array('limit' => 1),
            ),*/
          'conditions' => array('Attachment.alternative' => 'Teller'),
          'group' => array('Teller.id'),//これが重要。ここでまとめないと、一つのテストに対して同じユーザーが何度も表示される。  
          'page' => 1)  
    );  
      
    $this->Teller->virtualFields = array('attachment_count' => 'count(Attachment.id)'); // SQL 発行前にバーチャルフィールド設定  
    $res = $this->paginate('Teller');
    pr($res);
}


サブモデルの件数をカウントして、順番に並び変え


$this->Ikemen = Classregistry::init('Ikemen');
$this->Ikemen->bindModel(array('belongsTo' => array('Attachment' => array('foreignKey' => 'attachment_id'))),false);

$this->Ikemen->recursive = 2;
$this->Ikemen->contain(
    array(
        'Attachment' => array(
            'fields' => array('id','basename')
        ),
    )
);
$ikemen = $this->Ikemen->find('all',array(
    'fields' => array('count(*) as score','Ikemen.attachment_id'),
    'conditions' => array('date_add(Ikemen.created,interval 30 day) > curdate()','1 = 1 group by Ikemen.attachment_id'),
    'order' => 'score desc'
));//30日以内のもの


pr($ikemen);
exit;


Userテーブル



sex は 男性を 0  、 女性 を 1 としている。

id name sex email item created

1 ひでまん 0 hoge@hoge.com  8 2011-04-28 18:00:32

2 花子 1 hanako@hoge.com 0 2011-03-28 18:00:32

3 きてれつ君 0 kite888888@yahoo.co.jp 2 2011-05-28 18:00:32


以下、全てモデルに記述して下さい。

どの性別のユーザーが多いかを取得



$res = $this->query('SELECT sex AS "sex", Count(sex) AS "sex_count"
FROM `'.$this->tablePrefix.$this->useTable.'` as '.$this->name.'
GROUP BY sex');

結果

[0] => Array
    (
        [users] => Array
            (
                [sex] => 0
            )

        [0] => Array
            (
                [sex_count] => 2
            )

    )

[1] => Array
    (
        [users] => Array
            (
                [sex] => 1
            )

        [0] => Array
            (
                [sex_count] => 2
            )

    )

ちなみに並び変えたい場合は
$res = Set::sort($res, '{n}.0.sex_count', 'desc');

をやると性別の多い順、少ない順に並び変えができる。



女性ユーザーを全て男性に変更



$this->query('UPDATE `'.$this->tablePrefix.$this->useTable.'` SET `sex` = 0 WHERE `'.$this->tablePrefix.$this->useTable.'`.`sex` = `1`'); 

これで sex が 1 だったユーザーの sex を一括で sex 0 にする。


指定したデータを一気に削除


$this->query('DELETE FROM `'.$this->tablePrefix.$this->useTable.'` WHERE `user_id` = '.u('id').' and `aite_flag` = 0');








日付系は、すべて curdate でなく、 nowにしたほうがいいかも。
結果として now() と sysdate() は同じ日付+時刻。
CURRENT_DATE() は日付のみを表示します。

24時間以内のデータを取得したい場合は、 now() にしないとバグる。






createdの30日後を end フィールドとして取得


$res = $this->find('all',array('fields' => array('date_add(created,interval 30 day) as end')));



createdが今日のを取得


$res = $this->find('first',array('conditions' => array('user_id' => $user_id , 'created = curdate()')));


createdが今日までのを取得 (今日を含む)


$res = $this->find('all',array('conditions' => array('date_add(created,interval 1 day) > curdate()')));


createdが今月に作られたもの


$res = $this->find('all',array('conditions' => array('mode' => $mode , 'created >= date_format(now(),"%Y-%m-01")')));



createdが昨日までのを取得 (今日は含まない)


$res = $this->find('all',array('conditions' => array('created < curdate()')));


created から 7日 以上経過しているものを取得


$res = $this->find('all',array('conditions' => array('date_add(Contact.lastanswer,interval 7 day) < curdate()')));



created の 30日後が 今日よりも新しいもののみ取得 (つまり、30日以内に作られたもの)


$res = $this->find('all',array('conditions' => array('date_add(created,interval 30 day) > curdate()')));


今から30分以内に作られたもの この now() にするのが味噌。


$data = $this->Sakura->find('all',array('conditions' => array('date_add(created,interval 30 MINUTE) > now()'))); 


created が今月の1日以降に作られたもの


$ct = $this->Subcontact->find('count',array('conditions' => array('from_id' => $v , 'created >= ' => date('Y-m-01'))));


id2のユーザーのcreatedを30日間伸ばす


$res = $this->find('first',array('conditions' => array('id' => '2'), 'fields' => array('date_add(created,interval 30 day) as end')));
$data['User'] = array('id' => '2' ,'created' => $res[0]['end']);
$this->set($data);
$this->save();

ちなみに日付登録は 
date('Y-m-d H:i:s');

をやっとくといいかもしれません。


mysql の 時間・分・秒 での計算


時間: HOUR
分: MINUTE
秒: SECOND




以上。
また、機会があればどんどん追加していきます。
関連するタグ: PHP cakephp MYSQL
あなたにとって有用な記事でしたか?是非ブックマークしておくことをおすすめします。
 




ライブラリを配布しちゃったり
webデザイン
Fireworks
HTMLコーディング
CSS
Dreamweaver
携帯サイト
webプログラム
PHP
正規表現
cakephp
MYSQL
javascript
webマーケティング
adwords
analytics
windows7
ショートカットキー
おすすめ情報
サイト
facebook
ライブラリ
配布
アプリ
iphone
ipad
サーバー
さくらサーバー
全ての記事を読む




トップ - 最新の記事一覧 - お問い合わせ