mysql - CakePHP 模型在 SQL 语句中使用了错误的字段!

Question

我有一堆模型，它们之间建立了各种关联，似乎 Cakephp 有时会执行不正确的 SQL 语句并导致 MySQL 呕吐。

例如，如果我有两个模型 Comment 和 Tag 以及如下代码:

$this->Comment->id = 5;
$this->Comment->saveField('read_count', 3);

生成 SQL 语句:

UPDATE comments SET read_count = 3 WHERE Tag.id = 3;

它不会一直发生，但它最终会发生，因为我在一个紧密的循环中做每件事。

请帮忙。这真的让我质疑我选择 Cake 的决定，因为这听起来很糟糕。

谢谢。

编辑 1我刚遇到问题，这是错误的 SQL:

SELECT COUNT(*) AS `count` FROM `albums_songs` AS `AlbumSong`   WHERE `ArtistGenre`.`id` = 26482

AlbumSong 和 ArtistGenre 是两个完全独立的表，它们根本没有关系。

编辑 2刚遇到另一个失败。代码是:

$this->Song->find('first', array('conditions' => array('Song.artist_id' => 30188, 'Song.name' => 'Pal Pal (By.Tarkhanz)'), 'fields' => array('Song.id')))

虽然生成的 SQL 是:

SELECT `Song`.`id` FROM `songs` AS `Song`   WHERE `Artist`.`name` = 'Annie Villeneuve'    LIMIT 1 

如您所见，我没有在条件中指定 Artist.name，但生成的 SQL 正在查看它。

编辑 3另一个失败的例子。调用如下:

$this->Song->id = $song_id;
$library_count = $this->Song->field('Song.library_count');

然而 SQL 是:

SELECT `Song`.`library_count` FROM `songs` AS `Song`   WHERE `Artist`.`name` = 'Mazikana_Ragheb_Allama'    LIMIT 1

其中 Artist.name 不是 Song 的列，因为它属于 Artist 模型。

谢谢。

编辑 4

models/album.php

    <?php
    class Album extends AppModel {
            var $name = 'Album';
            var $belongsTo = array(
                    'Artist' => array(
                            'className' => 'Artist',
                            'foreignKey' => 'artist_id',
                            'conditions' => '',
                            'fields' => '',
                            'order' => ''
                    )
            );

            var $hasAndBelongsToMany = array(
                    'Song' => array(
                            'className' => 'Song',
                            'joinTable' => 'albums_songs',
                            'foreignKey' => 'album_id',
                            'associationForeignKey' => 'song_id',
                            'unique' => true,
                            'conditions' => '',
                            'fields' => '',
                            'order' => '',
                            'limit' => '',
                            'offset' => '',
                            'finderQuery' => '',
                            'deleteQuery' => '',
                            'insertQuery' => ''
                    )
            );

            var $hasMany = array(
                    'AlbumSong' => array(
                            'className' => 'AlbumSong',
                            'foreignKey' => 'album_id',
                            'dependent' => false,
                            'conditions' => '',
                            'fields' => '',
                            'order' => '',
                            'limit' => '',
                            'offset' => '',
                            'exclusive' => '',
                            'finderQuery' => '',
                            'counterQuery' => ''
                    )
            );
    }

    ?>

    models/album_song.php

    <?php
    class AlbumSong extends AppModel {
            var $name = 'AlbumSong';
            var $useTable = 'albums_songs';
            var $belongsTo = array(
                    'Song' => array(
                            'className' => 'Song',
                            'foreignKey' => 'song_id',
                            'conditions' => '',
                            'fields' => '',
                            'order' => ''
                    ),
                    'Album' => array(
                            'className' => 'Album',
                            'foreignKey' => 'album_id',
                            'conditions' => '',
                            'fields' => '',
                            'order' => ''
                    )
            );
    }

    ?>

    models/artist.php
    <?php
    class Artist extends AppModel {
            var $name = 'Artist';
            var $hasMany = array(
                    'Album' => array(
                            'className' => 'Album',
                            'foreignKey' => 'artist_id',
                            'dependent' => false,
                            'conditions' => '',
                            'fields' => '',
                            'order' => '',
                            'limit' => '',
                            'offset' => '',
                            'exclusive' => '',
                            'finderQuery' => '',
                            'counterQuery' => ''
                    ),
                    'Song' => array(
                            'className' => 'Song',
                            'foreignKey' => 'artist_id',
                            'dependent' => false,
                            'conditions' => '',
                            'fields' => '',
                            'order' => '',
                            'limit' => '',
                            'offset' => '',
                            'exclusive' => '',
                            'finderQuery' => '',
                            'counterQuery' => ''
                    ),
                    'ArtistGenre' => array(
                            'className' => 'ArtistGenre',
                            'foreignKey' => 'artist_id',
                            'dependent' => false,
                            'conditions' => '',
                            'fields' => '',
                            'order' => '',
                            'limit' => '',
                            'offset' => '',
                            'exclusive' => '',
                            'finderQuery' => '',
                            'counterQuery' => ''
                    )
            );
    }

    ?>

    models/artist_genre.php

    <?php
    class ArtistGenre extends AppModel {
            var $name = 'ArtistGenre';
            var $useTable = 'artists_genres';
            var $belongsTo = array(
                    'Artist' => array(
                            'className' => 'Artist',
                            'foreignKey' => 'artist_id',
                            'conditions' => '',
                            'fields' => '',
                            'order' => ''
                    ),
                    'Genre' => array(
                            'className' => 'Genre',
                            'foreignKey' => 'genre_id',
                            'conditions' => '',
                            'fields' => '',
                            'order' => ''
                    )
            );
    }

    ?>

    models/genre.php

    <?php
    class Genre extends AppModel {
            var $name = 'Genre';
            var $hasAndBelongsToMany = array(
                    'Artist' => array(
                            'className' => 'Artist',
                            'joinTable' => 'artists_genres',
                            'foreignKey' => 'genre_id',
                            'associationForeignKey' => 'artist_id',
                            'unique' => true,
                            'conditions' => '',
                            'fields' => '',
                            'order' => '',
                            'limit' => '',
                            'offset' => '',
                            'finderQuery' => '',
                            'deleteQuery' => '',
                            'insertQuery' => ''
                    )
            );

            var $hasMany = array(
                    'ArtistGenre' => array(
                            'className' => 'ArtistGenre',
                            'foreignKey' => 'genre_id',
                            'dependent' => false,
                            'conditions' => '',
                            'fields' => '',
                            'order' => '',
                            'limit' => '',
                            'offset' => '',
                            'exclusive' => '',
                            'finderQuery' => '',
                            'counterQuery' => ''
                    )
            );
    }

    ?>

    models/song.php

    <?php
    class Song extends AppModel {
            var $name = 'Song';
            var $belongsTo = array(
                    'Artist' => array(
                            'className' => 'Artist',
                            'foreignKey' => 'artist_id',
                            'conditions' => '',
                            'fields' => '',
                            'order' => ''
                    )
            );
            /*
            var $hasAndBelongsToMany = array(
                    'Album' => array(
                            'className' => 'Album',
                            'joinTable' => 'albums_songs',
                            'foreignKey' => 'song_id',
                            'associationForeignKey' => 'album_id',
                            'unique' => true,
                            'conditions' => '',
                            'fields' => '',
                            'order' => '',
                            'limit' => '',
                            'offset' => '',
                            'finderQuery' => '',
                            'deleteQuery' => '',
                            'insertQuery' => ''
                    )
            );
            */
            var $hasMany = array(
                    'AlbumSong' => array(
                            'className' => 'AlbumSong',
                            'foreignKey' => 'song_id',
                            'dependent' => false,
                            'conditions' => '',
                            'fields' => '',
                            'order' => '',
                            'limit' => '',
                            'offset' => '',
                            'exclusive' => '',
                            'finderQuery' => '',
                            'counterQuery' => ''
                    )
            );
    }

    ?>

差不多就这些了。为了简洁起见，我删除了验证代码。

非常感谢!

Answer 1

我可能遇到过同样的问题。我遇到的问题是在 cake 实现缓存已解析 sql 的条件(即 WHERE 子句)时发生缓存冲突。

对于 cake 1.3，默认缓存 DboSource::conditions() 和 DboSource::name() 的结果。参见:DboSource .缓存采用crc32哈希算法，碰撞几率较高。此外，在紧密循环中运行查询也会增加发生冲突的机会。这可以解释为什么您的表格名称不匹配

select * from `table_A` where `table_B`.`field` ...

解决方案是将数据源设置为不进行此缓存。所以，试试

$ds = $this->Comment->getDataSource();
$ds->cacheMethods = false;

在使用生成sql语句的方法之前。