CakePHP Model uses the wrong fields in SQL statements!

Question

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!

Did you defined default conditions, in the association definitions (belongsTo, HABTM or hasMany), for tags model or comments model ? — yvoyer
– yvoyer, Commented Nov 15, 2010 at 18:04
Could you show us your models associations definiton and the content of the method where you make you call. — yvoyer
– yvoyer, Commented Nov 15, 2010 at 18:53
Could you please post the call you are using to create the sql. — yvoyer
– yvoyer, Commented Nov 15, 2010 at 19:37
Possibly related to this bug? cakephp.lighthouseapp.com/projects/42648/tickets/… Please contribute any information to it you can. — deceze
– deceze ♦, Commented Nov 17, 2010 at 1:59

EricC · Accepted Answer · 2013-11-06 20:24:26Z

1

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.

answered Nov 6, 2013 at 20:24

EricC

1,4572 gold badges23 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

yvoyer · Accepted Answer · 2010-11-15 18:08:39Z

0

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.
            ),
        )
    );
}

answered Nov 15, 2010 at 18:08

yvoyer

7,5465 gold badges35 silver badges37 bronze badges

1 Comment

TopQ Over a year ago

No I don't have something like that. Thanks.

woodscreative · Accepted Answer · 2010-11-16 17:48:10Z

0

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

answered Nov 16, 2010 at 17:48

woodscreative

1,0319 silver badges26 bronze badges

Comments

dogmatic69 · Accepted Answer · 2010-11-22 00:05:19Z

0

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.

answered Nov 22, 2010 at 0:05

dogmatic69

7,5834 gold badges34 silver badges50 bronze badges

Comments

MBCook · Accepted Answer · 2010-11-22 00:49:50Z

0

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.

answered Nov 22, 2010 at 0:49

MBCook

14.5k7 gold badges40 silver badges41 bronze badges

2 Comments

TopQ Over a year ago

I do call model->create() before all save calls. Do I need to do that for any ->find() calls too?

TopQ Over a year ago

I posted model association as Edit 4. Thanks.

Collectives™ on Stack Overflow

CakePHP Model uses the wrong fields in SQL statements!

5 Answers 5

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related