Reputation: 185
CakePHP Model uses the wrong fields in SQL statements!
I have a bunch of models with various associations set up between them and seems like Cakephp at times executes incorrect SQL statement and cause MySQL to barf.
For example, if I have two models, Comment and Tag and a code like this:
$this->Comment->id = 5;
$this->Comment->saveField('read_count', 3);
yields SQL statement:
UPDATE comments SET read_count = 3 WHERE Tag.id = 3;
It doesn't happen all the time but it eventually happens since I am doing everything in a tight loop.
Please help. This really makes me question my decision to go with Cake since this sounds bad.
Thanks.
EDIT 1 I just ran into the problem and here is the faulty SQL:
SELECT COUNT(*) AS `count` FROM `albums_songs` AS `AlbumSong` WHERE `ArtistGenre`.`id` = 26482
AlbumSong and ArtistGenre are two completely separate tables and they are not related at all.
EDIT 2 Just ran into another failure. The code is:
$this->Song->find('first', array('conditions' => array('Song.artist_id' => 30188, 'Song.name' => 'Pal Pal (By.Tarkhanz)'), 'fields' => array('Song.id')))
While the generated SQL is:
SELECT `Song`.`id` FROM `songs` AS `Song` WHERE `Artist`.`name` = 'Annie Villeneuve' LIMIT 1
As you can see no were in the conditions do I specify an Artist.name yet the SQL generated is looking at it.
EDIT 3 Another example failure. Call is as followed:
$this->Song->id = $song_id;
$library_count = $this->Song->field('Song.library_count');
Yet the SQL is:
SELECT `Song`.`library_count` FROM `songs` AS `Song` WHERE `Artist`.`name` = 'Mazikana_Ragheb_Allama' LIMIT 1
where Artist.name is not a column of Song as it belongs to the 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 is pretty much of it. For sake of brevity I removed validation code.
Thanks a lot!
Upvotes: 2
Views: 1530
Answers (5)
Reputation: 1439
I have had possibly the same problem. The issue I encountered was cache collision in cake's implementation of caching the conditions (i.e. the WHERE clause) of a parsed sql.
For cake 1.3, the results of DboSource::conditions() and DboSource::name() are cached by default. see: DboSource. The cache uses crc32 hashing algorithm, which has a higher chance of collision. Also, running queries in a tight loop can also increase the chance of collision. That may explain why you have mismatching table names in the form of
select * from `table_A` where `table_B`.`field` ...
The solution was to set the data source to not do this caching. So, try
$ds = $this->Comment->getDataSource();
$ds->cacheMethods = false;
before using methods that generate sql statements.
Upvotes: 1
Reputation: 14504
I've been learning CakePHP in the last few months and this kind of thing occasionally drove me crazy. dogmatic's comment about using ->create() before your call can help if you've been using that model earlier in your function, it will reset the model's internal state so stale values don't interfere. That may not be your problem.
I agree with yvover and bancer that it's probably an issue of relationships. Posting your models (or links to the code) would be a big help. The thing that caught me more than once during development was thinking I was editing the model for a class when I was actually editing something else due to a name mismatch, so changes weren't reflected because my 'model' was never loaded.
Upvotes: 0
Reputation: 7575
try sticking $this-ModelName->create() before you set the id and save / find. all that does is clear any other data that is hanging around. a bit of a hack, but if it works could provide some clues to the real problem.
Upvotes: 0
Reputation: 1031
Not sure if this bug has anything to do with it, what version are you running:
[eb76ab9] Fixed issue where Model::saveAll() would incorrectly commit a transaction which was not started in that function call itself.
http://cakephp.org/changelogs/1.3.6
Upvotes: 0
Reputation: 7516
Make sure not to have something like this:
class Tag extends AppModel
{
public $belongsTo = array (
'Comment' => array(
'conditions' => array(
'id' => 3, // Will add this condition on every query.
),
)
);
}
Upvotes: 0
Related Questions
- CakePHP not getting all fields from DB?
- CakePHP 2.x Field is Empty or null
- CakePHP 3 - Two foreign keys linked to the same table - wrong foreign key being used
- CakePHP 1.3 - Unknown column in where clause
- Model association trouble with cakephp
- CakePHP 2.4.2 Why is Cake using model names as SQL queries?
- cakephp model has 0 or 1 relationship
- CakePHP model, unknown column in fieldlist
- Why does cakephp not insert a field in a mysql table?
- Cakephp returns empty but sql query has results