mysql - CakePHP Model uses the wrong fields in SQL statements! -
i have bunch of models various associations set between them , seems cakephp @ times executes incorrect sql statement , cause mysql barf.
for example, if have 2 models, comment , tag , code this:
$this->comment->id = 5; $this->comment->savefield('read_count', 3); yields sql statement:
update comments set read_count = 3 tag.id = 3; it doesn't happen time happens since doing in tight loop.
please help. makes me question decision go cake since sounds bad.
thanks.
edit 1 ran problem , here faulty sql:
select count(*) `count` `albums_songs` `albumsong` `artistgenre`.`id` = 26482 albumsong , artistgenre 2 separate tables , not related @ all.
edit 2 ran failure. code is:
$this->song->find('first', array('conditions' => array('song.artist_id' => 30188, 'song.name' => 'pal pal (by.tarkhanz)'), 'fields' => array('song.id'))) while generated sql is:
select `song`.`id` `songs` `song` `artist`.`name` = 'annie villeneuve' limit 1 as can see no in conditions specify artist.name yet sql generated looking @ it.
edit 3 example failure. call followed:
$this->song->id = $song_id; $library_count = $this->song->field('song.library_count'); yet sql is:
select `song`.`library_count` `songs` `song` `artist`.`name` = 'mazikana_ragheb_allama' limit 1 where artist.name not column of song belongs artist model.
thanks.
edit 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' => '' ) ); } ?> that pretty of it. sake of brevity removed validation code.
thanks lot!
i have had possibly same problem. issue encountered cache collision in cake's implementation of caching conditions (i.e. clause) of parsed sql.
for cake 1.3, results of dbosource::conditions() , dbosource::name() cached default. see: dbosource. cache uses crc32 hashing algorithm, has higher chance of collision. also, running queries in tight loop can increase chance of collision. may explain why have mismatching table names in form of
select * `table_a` `table_b`.`field` ... the solution set data source not caching. so, try
$ds = $this->comment->getdatasource(); $ds->cachemethods = false; before using methods generate sql statements.
Comments
Post a Comment