need help with specifying a self-join + group by query

[bingo]

Hi,

I am trying to specify this self join + groub by query (see below my
query example). However, I am not joining the table on id but on
another field.

select ArticlesKeywordsUser.article_id, SelfJoinOnArticle.article_id,
count(*) as nos from articles_keywords_users as ArticlesKeywordsUser
leftJoin articles_keywords_users as SelfJoinOnArticle where
ArticlesKeywordsUser.article_id = SelfJoinOnArticle.article_id group
by ArticlesKeywordsUser.keyword_id, SelfJoinOnArticle.keyword_id

To do this, I defined my model as follows

class ArticlesKeywordsUser extends AppModel{
  var $name = 'ArticlesKeywordsUser';

  var $belongsTo = array(
                     'SelfJoinOnKeyword' => array(
                               'className' => 'ArticlesKeywordsUser',
                               'foreignKey' => 'article_id',
                               'associationForeignKey' => 'article_id'
                      )

  );

}

and in my controller I am specifying the query like this

$this->{$this->modelClass}->expects('SelfJoinOnArticle');
$this->{$this->modelClass}->findAll('1 = 1 group by
ArticlesKeywordsUser.keyword_id, SelfJoinOnArticle.keyword_id',
'ArticlesKeywordsUser.article_id, SelfJoinOnArticle.article_id,
count(*) as nos')

However the final query produced by CakePHP is not what I intented.
Everything works as I thought, but instead of join on article_id it
does ArticlesKeywordsUser.article_id = SelfJoinOnArticle.id --- which
I dont want

the query produced by cakephp is
select ArticlesKeywordsUser.article_id, SelfJoinOnArticle.article_id,
count(*) as nos from articles_keywords_users as ArticlesKeywordsUser
leftJoin articles_keywords_users as SelfJoinOnArticle where
ArticlesKeywordsUser.article_id = SelfJoinOnArticle.id and 1 = 1 group
by ArticlesKeywordsUser.keyword_id, SelfJoinOnArticle.keyword_id

Looking for some help

Regards,
bingo


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Cake 
PHP" group.
To post to this group, send email to [EMAIL PROTECTED]
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
-~----------~----~----~----~------~----~------~--~---