Filtering by HABTM model count
My problem: Show the user 4 Albums where each Album has >= 1 Images.
I started this project in CakePHP 1.1 and realized quickly I couldn't
create an elegant solution, so I've recently upgraded to 1.2.2 and am
trying again.
My code below works correctly and returns a wonderful array with all
the pieces of data I need. The really odd thing is that after Cake has
sent the first query, joining all the necessary tables, it then goes
back to query the Image model 4 times, each time failing because it
attempts to use the non-joined AlbumsImage HABTM association in the
where clause.
Here's the error and offending SQL:
Warning (512): SQL Error: 1054: Unknown column 'AlbumsImage.image_id'
in 'where clause' [CORE/cake/libs/model/datasources/dbo_source.php,
line 525]
SELECT `image`.`id`,`image`.`title`,`image`.`host`,`image`.`path`
FROM `images` AS `image`
WHERE`image`.`status` = 0
AND `image`.`id` = `albumsimage`.`image_id`
ORDER BY `image`.`created` ASC
I don't know why this query is even being issued, but as I say it's
all the more odd because the main query (below) returns successfully.
SELECT
`album`.`id`,`album`.`user_id`,`album`.`title`,USER.id,USER.username,`user`.`id`
FROM `albums` AS `album`
LEFT JOIN `users` AS `user`
ON (`album`.`user_id` = `user`.`id`)
LEFT JOIN `albums_images` AS `albumsimage`
ON (`albumsimage`.`album_id` = `album`.`id`)
INNER JOIN `images` AS `image`
ON (`image`.`status` = 0
AND `image`.`id` = `albumsimage`.`image_id`)
WHERE`album`.`status` = 0
GROUP BY `album`.`id`,`album`.`title`
HAVING Count(* ) >= 1
ORDER BY `album`.`created` DESC,`image`.`created` ASC
LIMIT4
Here is the method of the Album model I use to query Albums where
Image count >= 1:
function gimme($num=6) {
$contain = array(
'AlbumsImage',
'Image'=>array('ImageSize',
'fields'=>array
('Image.id','Image.title','Image.host','Image.path'),
'order' => 'Image.created ASC'
),
'User'=>array('fields'=>'User.id,User.username'));
$this->resetAssociations(); // just in case
$this->bindModel(array('hasOne'=>array('AlbumsImage',
'Image'=>array(
'className'=>'Image',
'foreignKey'=>false,
'type'=>'INNER',
'conditions'=>array(
'Image.status =
0',
'Image.id =
AlbumsImage.image_id')
)
)
));
$albums = $this->find('all', array('contain' => $contain,
'conditions' => array
('Album.status'=>'0'),
'fields' => array
('Album.id','Album.user_id','Album.title'),
'order' => 'Album.created
DESC',
'limit' => $num,
'group' => array
('Album.id','Album.title HAVING COUNT(*) >= 1'))); // we only want
Albums with 1 or more images
return $albums;
}
I've been trying to figure this out all day so would /really/
appreciate any insights into this weird SQL error.
Thanks everyone!
-volve
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
Re: dAuth v0.3 [Session losing 'salt' variable]
I have found the cause of the problem. I appended a trac bug about it: https://trac.cakephp.org/ticket/830#comment:7 Basically high & medium security modes trigger PHP's session.referer_check and tell it to look for $this->host in every request's HTTP_REFERER. This logic immediately breaks down if the server serving the CakePHP application has a proxy or cacheing server in front of it. In my case, new sessions were created upon every request because $this- >host was always "localhost" (the proxy server) where-as HTTP_REFERER was always "http://testdomain.com/users/login"; So, I guess I'll be using low security from now on eh? :-/ Thanks for your help Dieter, and thanks for dAuth! -volve On Jan 24, 9:58 pm, volve <[EMAIL PROTECTED]> wrote: > I also just compared dev to production: > > dev: > php 5.2.5, php-default session settings > apache 2.2.6 > > production > php 5.2.5, php-default session settings > apache 2.2.8 > > When I upgraded production I actually switched to default httpd.conf > settings in case any of the previous Apache1.3 settings were part of > the problem. > > As I said earlier, I'm not sure what to check next... > > On Jan 24, 9:30 pm,volve<[EMAIL PROTECTED]> wrote: > > > Changing cake_session storage back to 'cake' (app/tmp/sessions/*) made > > no difference. I can't find anything in trac.cakephp.org, and I > > managed to misspell your name - sorry! > > > I don't know what to do now... :( > > > -volve > > > On Jan 24, 9:18 pm,volve<[EMAIL PROTECTED]> wrote: > > > > Ok... well... all was going well with Apache2 and PHP5 on the new dev > > > environment, so I upgraded the production server... and the same > > > problem is still occurring! I am so confused! I didn't do anything > > > custom with the Apache2 or PHP5 configs on production; I used the > > > recommended settings. :( > > > > The grep -i showed some Session->valid and Session->read calls but the > > > only Session->write or Session->delete calls were in d_auth.php. > > > > What's more is that I'm using this Users/login action as the default / > > > route for this project so this issue is occurring without the user > > > having to navigate to any other pages/actions at all... :-/ > > > > I'm off to search trac.cakephp.org now but this is crazy. The only > > > difference between dev and prod now is dev has mysql5 and prod is > > > still mysql4 (I am using cake_sessions in the database - I'll try > > > switching back to files, see if it helps). > > > > Thanks again Diether, > > > -volve > > > > On Jan 20, 5:34 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > > > > Well, something is removing items from ( or maybe totally wiping ) > > > > your session, and i don't think it's dAuth ;-) > > > > did grep -i session on your project yield anything? > > > > But at least you have an environment where it works. try comparing > > > > php.ini's, apache.conf's , looking up bugs for cake and php ( session > > > > bugs will probably be also in this group), downgrading the php of the > > > > new environment etc ... > > > > > good luck! > > > > Dieter > > > > > On Jan 19, 5:33 pm,volve<[EMAIL PROTECTED]> wrote: > > > > > > Thank you very much for getting back to me Dieter! > > > > > > The salt is definitely "in there" before attemptLogin is called as I > > > > > can stick a print_r in Users/login action and I see the salt at the > > > > > top of the page when I load the login form. (But the print_r inside > > > > > attemptLogin shows it as missing when submitting the login form.) > > > > > > I spent some time last night setting-up a second development > > > > > environment with PHP5 and... the problem hasn't reappeared... I know > > > > > this doesn't really make sense, but I wanted to share anyway. Maybe > > > > > there's some quirky CakePHP Session handling bug that isn't present in > > > > > PHP5 ? All I did was copy over my previous app directory into this new > > > > > environment and changed database logins. > > > > > > I'm still baffled, but at least it's progress (I think). :) > > > > > > Thanks again, > > > > > -volve > > > > > > On Jan 19, 6:38 am, "[EMAIL PROTECTED]" <[EMAIL
Re: dAuth v0.3 [Session losing 'salt' variable]
I also just compared dev to production: dev: php 5.2.5, php-default session settings apache 2.2.6 production php 5.2.5, php-default session settings apache 2.2.8 When I upgraded production I actually switched to default httpd.conf settings in case any of the previous Apache1.3 settings were part of the problem. As I said earlier, I'm not sure what to check next... On Jan 24, 9:30 pm, volve <[EMAIL PROTECTED]> wrote: > Changing cake_session storage back to 'cake' (app/tmp/sessions/*) made > no difference. I can't find anything in trac.cakephp.org, and I > managed to misspell your name - sorry! > > I don't know what to do now... :( > > -volve > > On Jan 24, 9:18 pm,volve<[EMAIL PROTECTED]> wrote: > > > Ok... well... all was going well with Apache2 and PHP5 on the new dev > > environment, so I upgraded the production server... and the same > > problem is still occurring! I am so confused! I didn't do anything > > custom with the Apache2 or PHP5 configs on production; I used the > > recommended settings. :( > > > The grep -i showed some Session->valid and Session->read calls but the > > only Session->write or Session->delete calls were in d_auth.php. > > > What's more is that I'm using this Users/login action as the default / > > route for this project so this issue is occurring without the user > > having to navigate to any other pages/actions at all... :-/ > > > I'm off to search trac.cakephp.org now but this is crazy. The only > > difference between dev and prod now is dev has mysql5 and prod is > > still mysql4 (I am using cake_sessions in the database - I'll try > > switching back to files, see if it helps). > > > Thanks again Diether, > > -volve > > > On Jan 20, 5:34 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > > > Well, something is removing items from ( or maybe totally wiping ) > > > your session, and i don't think it's dAuth ;-) > > > did grep -i session on your project yield anything? > > > But at least you have an environment where it works. try comparing > > > php.ini's, apache.conf's , looking up bugs for cake and php ( session > > > bugs will probably be also in this group), downgrading the php of the > > > new environment etc ... > > > > good luck! > > > Dieter > > > > On Jan 19, 5:33 pm,volve<[EMAIL PROTECTED]> wrote: > > > > > Thank you very much for getting back to me Dieter! > > > > > The salt is definitely "in there" before attemptLogin is called as I > > > > can stick a print_r in Users/login action and I see the salt at the > > > > top of the page when I load the login form. (But the print_r inside > > > > attemptLogin shows it as missing when submitting the login form.) > > > > > I spent some time last night setting-up a second development > > > > environment with PHP5 and... the problem hasn't reappeared... I know > > > > this doesn't really make sense, but I wanted to share anyway. Maybe > > > > there's some quirky CakePHP Session handling bug that isn't present in > > > > PHP5 ? All I did was copy over my previous app directory into this new > > > > environment and changed database logins. > > > > > I'm still baffled, but at least it's progress (I think). :) > > > > > Thanks again, > > > > -volve > > > > > On Jan 19, 6:38 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > > > > > > I basically did a > > > > > > fresh install of the v0.3 files from bakery.cakephp.org and wanted > > > > > > to > > > > > > get them all working as-is before customizing (to make sure there > > > > > > was > > > > > > nothing in my project interfering, I grep'd the entire source tree > > > > > > for > > > > > > references to 'salt' and only found the new dAuth ones). > > > > > > Well, this is weird indeed. I also used the powers of grep on the > > > > > dauth sources and found out that at only 1 place the salt is written > > > > > into the session, which is in the function newSalt() of the component. > > > > > By grepping on Session i could not find a place where the salt might > > > > > be removed or the session cleared or anything like that. I suggest > > > > > you also grep on Session in your entire project to find out if > > > > > something else might be interfering. ( use grep -i to be sure you > > > > > dont miss anything) > > > > > > Also make sure that the salt is put in the session before trying to > > > > > read it out ( eg you're not going directly to the attemptLogin logic > > > > > before opening the login page. and make sure you have a salt there > > > > > first) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: dAuth v0.3 [Session losing 'salt' variable]
Changing cake_session storage back to 'cake' (app/tmp/sessions/*) made no difference. I can't find anything in trac.cakephp.org, and I managed to misspell your name - sorry! I don't know what to do now... :( -volve On Jan 24, 9:18 pm, volve <[EMAIL PROTECTED]> wrote: > Ok... well... all was going well with Apache2 and PHP5 on the new dev > environment, so I upgraded the production server... and the same > problem is still occurring! I am so confused! I didn't do anything > custom with the Apache2 or PHP5 configs on production; I used the > recommended settings. :( > > The grep -i showed some Session->valid and Session->read calls but the > only Session->write or Session->delete calls were in d_auth.php. > > What's more is that I'm using this Users/login action as the default / > route for this project so this issue is occurring without the user > having to navigate to any other pages/actions at all... :-/ > > I'm off to search trac.cakephp.org now but this is crazy. The only > difference between dev and prod now is dev has mysql5 and prod is > still mysql4 (I am using cake_sessions in the database - I'll try > switching back to files, see if it helps). > > Thanks again Diether, > -volve > > On Jan 20, 5:34 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > > Well, something is removing items from ( or maybe totally wiping ) > > your session, and i don't think it's dAuth ;-) > > did grep -i session on your project yield anything? > > But at least you have an environment where it works. try comparing > > php.ini's, apache.conf's , looking up bugs for cake and php ( session > > bugs will probably be also in this group), downgrading the php of the > > new environment etc ... > > > good luck! > > Dieter > > > On Jan 19, 5:33 pm,volve<[EMAIL PROTECTED]> wrote: > > > > Thank you very much for getting back to me Dieter! > > > > The salt is definitely "in there" before attemptLogin is called as I > > > can stick a print_r in Users/login action and I see the salt at the > > > top of the page when I load the login form. (But the print_r inside > > > attemptLogin shows it as missing when submitting the login form.) > > > > I spent some time last night setting-up a second development > > > environment with PHP5 and... the problem hasn't reappeared... I know > > > this doesn't really make sense, but I wanted to share anyway. Maybe > > > there's some quirky CakePHP Session handling bug that isn't present in > > > PHP5 ? All I did was copy over my previous app directory into this new > > > environment and changed database logins. > > > > I'm still baffled, but at least it's progress (I think). :) > > > > Thanks again, > > > -volve > > > > On Jan 19, 6:38 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > > > > > I basically did a > > > > > fresh install of the v0.3 files from bakery.cakephp.org and wanted to > > > > > get them all working as-is before customizing (to make sure there was > > > > > nothing in my project interfering, I grep'd the entire source tree for > > > > > references to 'salt' and only found the new dAuth ones). > > > > > Well, this is weird indeed. I also used the powers of grep on the > > > > dauth sources and found out that at only 1 place the salt is written > > > > into the session, which is in the function newSalt() of the component. > > > > By grepping on Session i could not find a place where the salt might > > > > be removed or the session cleared or anything like that. I suggest > > > > you also grep on Session in your entire project to find out if > > > > something else might be interfering. ( use grep -i to be sure you > > > > dont miss anything) > > > > > Also make sure that the salt is put in the session before trying to > > > > read it out ( eg you're not going directly to the attemptLogin logic > > > > before opening the login page. and make sure you have a salt there > > > > first) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: dAuth v0.3 [Session losing 'salt' variable]
Ok... well... all was going well with Apache2 and PHP5 on the new dev environment, so I upgraded the production server... and the same problem is still occurring! I am so confused! I didn't do anything custom with the Apache2 or PHP5 configs on production; I used the recommended settings. :( The grep -i showed some Session->valid and Session->read calls but the only Session->write or Session->delete calls were in d_auth.php. What's more is that I'm using this Users/login action as the default / route for this project so this issue is occurring without the user having to navigate to any other pages/actions at all... :-/ I'm off to search trac.cakephp.org now but this is crazy. The only difference between dev and prod now is dev has mysql5 and prod is still mysql4 (I am using cake_sessions in the database - I'll try switching back to files, see if it helps). Thanks again Diether, -volve On Jan 20, 5:34 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Well, something is removing items from ( or maybe totally wiping ) > your session, and i don't think it's dAuth ;-) > did grep -i session on your project yield anything? > But at least you have an environment where it works. try comparing > php.ini's, apache.conf's , looking up bugs for cake and php ( session > bugs will probably be also in this group), downgrading the php of the > new environment etc ... > > good luck! > Dieter > > On Jan 19, 5:33 pm,volve<[EMAIL PROTECTED]> wrote: > > > Thank you very much for getting back to me Dieter! > > > The salt is definitely "in there" before attemptLogin is called as I > > can stick a print_r in Users/login action and I see the salt at the > > top of the page when I load the login form. (But the print_r inside > > attemptLogin shows it as missing when submitting the login form.) > > > I spent some time last night setting-up a second development > > environment with PHP5 and... the problem hasn't reappeared... I know > > this doesn't really make sense, but I wanted to share anyway. Maybe > > there's some quirky CakePHP Session handling bug that isn't present in > > PHP5 ? All I did was copy over my previous app directory into this new > > environment and changed database logins. > > > I'm still baffled, but at least it's progress (I think). :) > > > Thanks again, > > -volve > > > On Jan 19, 6:38 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > > > > I basically did a > > > > fresh install of the v0.3 files from bakery.cakephp.org and wanted to > > > > get them all working as-is before customizing (to make sure there was > > > > nothing in my project interfering, I grep'd the entire source tree for > > > > references to 'salt' and only found the new dAuth ones). > > > > Well, this is weird indeed. I also used the powers of grep on the > > > dauth sources and found out that at only 1 place the salt is written > > > into the session, which is in the function newSalt() of the component. > > > By grepping on Session i could not find a place where the salt might > > > be removed or the session cleared or anything like that. I suggest > > > you also grep on Session in your entire project to find out if > > > something else might be interfering. ( use grep -i to be sure you > > > dont miss anything) > > > > Also make sure that the salt is put in the session before trying to > > > read it out ( eg you're not going directly to the attemptLogin logic > > > before opening the login page. and make sure you have a salt there > > > first) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: dAuth v0.3 [Session losing 'salt' variable]
Thank you very much for getting back to me Dieter! The salt is definitely "in there" before attemptLogin is called as I can stick a print_r in Users/login action and I see the salt at the top of the page when I load the login form. (But the print_r inside attemptLogin shows it as missing when submitting the login form.) I spent some time last night setting-up a second development environment with PHP5 and... the problem hasn't reappeared... I know this doesn't really make sense, but I wanted to share anyway. Maybe there's some quirky CakePHP Session handling bug that isn't present in PHP5 ? All I did was copy over my previous app directory into this new environment and changed database logins. I'm still baffled, but at least it's progress (I think). :) Thanks again, -volve On Jan 19, 6:38 am, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > I basically did a > > fresh install of the v0.3 files from bakery.cakephp.org and wanted to > > get them all working as-is before customizing (to make sure there was > > nothing in my project interfering, I grep'd the entire source tree for > > references to 'salt' and only found the new dAuth ones). > > Well, this is weird indeed. I also used the powers of grep on the > dauth sources and found out that at only 1 place the salt is written > into the session, which is in the function newSalt() of the component. > By grepping on Session i could not find a place where the salt might > be removed or the session cleared or anything like that. I suggest > you also grep on Session in your entire project to find out if > something else might be interfering. ( use grep -i to be sure you > dont miss anything) > > Also make sure that the salt is put in the session before trying to > read it out ( eg you're not going directly to the attemptLogin logic > before opening the login page. and make sure you have a salt there > first) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
Re: dAuth v0.3 [Session losing 'salt' variable]
Admittedly I gave this post an awfully vague subject so hopefully it
won't be totally dismissed. Upon further investigation I found some
more details.
Simply put: my $this->Session->read('salt'); is always empty when
attemptLogin is called. If I do print_r($this->Session->read()); in
attemptLogin, I only have:
Array
(
[Config] => Array
(
[userAgent] => 7a98565405c4c8c1b17961f9f9edd2fd
[time] => 1200631921
[rand] => 52815838
)
)
But if I do the same print_r from the Users/login action I have:
Array
(
[Config] => Array
(
[userAgent] => 7a98565405c4c8c1b17961f9f9edd2fd
[time] => 1200631921
[rand] => 52815838
)
[salt] => 282191378
)
I'm very confused why the salt is disappearing... :( I basically did a
fresh install of the v0.3 files from bakery.cakephp.org and wanted to
get them all working as-is before customizing (to make sure there was
nothing in my project interfering, I grep'd the entire source tree for
references to 'salt' and only found the new dAuth ones). Obviously
this means "Credentials mismatch" is the only result attemptLogin ever
yields because it can never successfully compare the passwords. I'm
baffled!
Any pointers anyone might have?
Thanks,
-volve
On Jan 17, 10:47 pm, volve <[EMAIL PROTECTED]> wrote:
> Hey all,
>
> I'm trying to get dAuth v0.3 working but am constantly presented with
> "Login failed: Credentials mismatch.". I added a slew of debug output
> to the component and sure enough my password hash and the one
> submitted are completely different.
>
> Some log_debug output from a login attempt:
>
> 2008-01-17 22:37:57 Debug: dAuthComponent attemptLogin...
> 2008-01-17 22:37:57 Debug: clean host...
> 2008-01-17 22:37:57 Debug: username and password present...
> 2008-01-17 22:37:57 Debug: username found in database...
> 2008-01-17 22:37:57 Debug: clear text = false...
> 2008-01-17 22:37:57 Debug: password hash:
> [7025cd2543c0f3a446487c973a124d0b25daa753] submitted hash:
> [fcb55b0e784e713ec1116766ffac3e75a833bcda]
> 2008-01-17 22:37:57 Debug: hashed password compare failed...
>
> Thinking maybe it was some stale user accounts, I went through the
> dAuth Users/register action again and that was successful. I also
> checked my files for copy-n-paste whitespace that might cause header
> issues but couldn't find any.
>
> I realize I may be behind the times on implementing dAuth but I hope
> someone else has resolved this. :)
>
> Thanks!
> -volve
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups "Cake
PHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~--~~~~--~~--~--~---
dAuth v0.3
Hey all, I'm trying to get dAuth v0.3 working but am constantly presented with "Login failed: Credentials mismatch.". I added a slew of debug output to the component and sure enough my password hash and the one submitted are completely different. Some log_debug output from a login attempt: 2008-01-17 22:37:57 Debug: dAuthComponent attemptLogin... 2008-01-17 22:37:57 Debug: clean host... 2008-01-17 22:37:57 Debug: username and password present... 2008-01-17 22:37:57 Debug: username found in database... 2008-01-17 22:37:57 Debug: clear text = false... 2008-01-17 22:37:57 Debug: password hash: [7025cd2543c0f3a446487c973a124d0b25daa753] submitted hash: [fcb55b0e784e713ec1116766ffac3e75a833bcda] 2008-01-17 22:37:57 Debug: hashed password compare failed... Thinking maybe it was some stale user accounts, I went through the dAuth Users/register action again and that was successful. I also checked my files for copy-n-paste whitespace that might cause header issues but couldn't find any. I realize I may be behind the times on implementing dAuth but I hope someone else has resolved this. :) Thanks! -volve --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Cake PHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~--~~~~--~~--~--~---
