cake-php@googlegroups.com
[Top] [All Lists]

Re: CakePHP Model uses the wrong fields in SQL statements!

Subject: Re: CakePHP Model uses the wrong fields in SQL statements!
From: ""
Date: Mon, 22 Nov 2010 12:18:35 -0800 PST
Here is all the code:
$this->out("this->Album->field('Album.library_count')");
$this->Album->id = $album_id;
$library_count = $this->Album->field('Album.library_count');
$this->out('Get Album library count and it is: ' . $library_count);

The final out statement produced only "Get Album library count and it
is: " as I think $library_count is false or null at that point.

Here is full SQL warning message:
Warning: SQL Error: 1054: Unknown column 'Song.artist_id' in 'where
clause' in /svr/web/cake1.3.6/cake/libs/model/datasources/
dbo_source.php on line 684
Query: SELECT `Album`.`library_count` FROM `albums` AS `Album`   WHERE
`Song`.`artist_id` = 7590 AND `Song`.`name` = 'One Thing Leads to
Another'    LIMIT 1

Thanks.

On Nov 22, 12:10 pm, Jeremy Burns | Class Outfit
<jeremybu...@xxxxxxxxxxxxxxx> wrote:
> Is this ALL of the SQL being produced or just an extract?
>
> What happens if you do:
>
> echo $this->Album->field('Album.library_count');
> or
> $variable = $this->Album->field('Album.library_count');
>
> ...instead of:
> $this->Album->field('Album.library_count');
>
> (or is this code just an extraction?)
>
> Jeremy Burns
> Class Outfit
>
> jeremybu...@xxxxxxxxxxxxxxxxxxx://www.classoutfit.com
>
> On 22 Nov 2010, at 20:05, qua...@xxxxxxxxx wrote:
>
> > As I said before, I WANT this to be my problem; I want to know I made
> > the right choice picking Cake as the framework. I want to know that I
> > screw up and not Cake.
>
> > The problem is repeatable in the sense that it will always happen...it
> > may happen 20 minuses into the loop or as long as an hour before it
> > hits the snag. And it doesn't just occur in a specific portion of the
> > loop; it is all over the places.
>
> > In the loop, I do bunch of Find and Save operations. I have tons of
> > debug statements and that is how I can tell you guys what code
> > generated what erroneous SQL.
>
> > I posted model associations as well and it is simple really.
>
> > Let's look at my last error again.
>
> > Code:
> > $this->Album->id = $album_id;
> > $this->Album->field('Album.library_count');
>
> > SQL:
> > SELECT `Album`.`library_count` FROM `albums` AS `Album`   WHERE
> > `Song`.`artist_id` = 7590 AND `Song`.`name` = 'One Thing Leads to
> > Another'    LIMIT 1
>
> > I specify an ID and field() the model for the data. It really can't
> > get simpler than that and yet it still produced wrong SQL. Cake is
> > trying to be too smart there?
>
> > Thanks.
>
> > On Nov 22, 11:36 am, Jeremy Burns | Class Outfit
> > <jeremybu...@xxxxxxxxxxxxxxx> wrote:
> >> If this were a genuine problem there'd be lots of people complaining - 
> >> this is one of the fundamentals of any app. As much as you can't see it 
> >> now and don't want to hear it, the problem DOES lie in your code, 
> >> somewhere.
>
> >> Are the failures you have specified repeatable? In other words, does it 
> >> always fail at the same point(s)? Are you doing any data manipulation as 
> >> part of the loop? If so, are you taking account of the changed data for 
> >> the rest of the loop? What happens if you disable any data modifying code?
>
> >> Here's what I'd do (some of this has already been suggested - not sure if 
> >> you followed them through):
> >> - Clear all of my caches
> >> - Turn caching off
> >> - Download the latest version of Cake (or a clean copy of the version I am 
> >> using) and replace the contents of my Cake directory with the clean, 
> >> fresh, untouched one
> >> - Check my model associations; do I have belongsTo and hasMany the wrong 
> >> way around - it can happen, and even one instance could be enough to trip 
> >> it up
> >> - Check that I don't have any joins of routes or queries - anything - hard 
> >> coded that could be causing this
> >> - Examine my looping code using debug statements
> >> - Strip the code down to the bone and gradually build back up until it 
> >> breaks
>
> >> Jeremy Burns
> >> Class Outfit
>
> >> jeremybu...@xxxxxxxxxxxxxxxxxxx://www.classoutfit.com
>
> >> On 22 Nov 2010, at 19:20, qua...@xxxxxxxxx wrote:
>
> >>> Ran into another stop.
>
> >>> Code:
> >>> $this->Album->id = $album_id;
> >>> $this->Album->field('Album.library_count');
>
> >>> SQL:
> >>> SELECT `Album`.`library_count` FROM `albums` AS `Album`   WHERE
> >>> `Song`.`artist_id` = 7590 AND `Song`.`name` = 'One Thing Leads to
> >>> Another'    LIMIT 1
>
> >>> I probably will need to write custom queries myself instead of relying
> >>> on Cake to do the right thing for me.
>
> >>> On Nov 22, 10:35 am, "qua...@xxxxxxxxx" <qua...@xxxxxxxxx> wrote:
> >>>> I am gong to try with recursive = -1.
>
> >>>> Thanks.
>
> >>>> On Nov 22, 9:35 am, John Andersen <j.andersen...@xxxxxxxxx> wrote:
>
> >>>>> Have recreated a test environment with your tables and models in
> >>>>> accordance with your specified model associations. Added some very few
> >>>>> test data and executed some of your find statements, to see if I could
> >>>>> make it produce the same (erroneous) SQL statements.
>
> >>>>> Testing using the code below:
> >>>>> [code]
> >>>>> $this->Song->recursive = -1;
> >>>>> for($i = 0; $i < 3500; $i++){
> >>>>>    $this->Song->find(
> >>>>>       'first', array(
> >>>>>          'conditions' => array(
> >>>>>             'Song.artist_id' => rand(1,2),
> >>>>>             'Song.name' => 'Song number '.rand(1,7)
> >>>>>          ),
> >>>>>          'fields' => array('Song.id', 'Song.name')
> >>>>>       )
> >>>>>    );}
>
> >>>>> [/code]
>
> >>>>> After having executed more than 20000 find statements, I still could
> >>>>> not see any erroneous SQL statement. All created statements looks like
> >>>>> this:
> >>>>> [code]
> >>>>> SELECT `Song`.`id`, `Song`.`name` FROM `songs` AS `Song` WHERE
> >>>>> `Song`.`artist_id` = 2 AND `Song`.`name` = 'Song number 5' LIMIT 1
> >>>>> [/code]
>
> >>>>> Testing using the code below (from Song controller):
> >>>>> [code]
> >>>>> $this->Song->AlbumSong->recursive = -1;
> >>>>> for($i = 0; $i < 3500; $i++){
> >>>>>    $this->Song->AlbumSong->find(
> >>>>>       'count', array(
> >>>>>          'conditions' => array(
> >>>>>             'AlbumSong.album_id' => rand(1,3),
> >>>>>             'AlbumSong.song_id' => rand(1,7)
> >>>>>          )
> >>>>>       )
> >>>>>    );}
>
> >>>>> [/code]
>
> >>>>> After having executed more than 20000 find statements, I still could
> >>>>> not see any erroneous SQL statement. All created statements looks like
> >>>>> this:
> >>>>> [code]
> >>>>> SELECT COUNT(*) AS `count` FROM `albums_songs` AS `AlbumSong` WHERE
> >>>>> `AlbumSong`.`album_id` = 3 AND `AlbumSong`.`song_id` = 4
> >>>>> [/code]
>
> >>>>> My environment:
> >>>>> Apache/2.2.14
> >>>>> PHP 5.3.1
> >>>>> MySQL  5.0.5
> >>>>> CakePHP 1.3.2
>
> >>>>> Enjoy,
> >>>>>    John
>
> >>>>> On 21 Nov., 19:51, "qua...@xxxxxxxxx" <qua...@xxxxxxxxx> wrote:
>
> >>>>>> Here are model setup:
>
> >>>>>> 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' => '',
>
> ...
>
> read more »

Check out the new CakePHP Questions site http://cakeqs.org and help others with 
their CakePHP related questions.

You received this message because you are subscribed to the Google Groups 
"CakePHP" group.
To post to this group, send email to cake-php@xxxxxxxxxxxxxxxx
To unsubscribe from this group, send email to
cake-php+unsubscribe@xxxxxxxxxxxxxxxx For more options, visit this group at 
http://groups.google.com/group/cake-php?hl=en

<Prev in Thread] Current Thread [Next in Thread>