Ps. Forgot to include this link, which might be useful in creating the constraint on your Song model: http://cakephp.org/pastes/show/abc17e6d6081dc7c2f91540668447d9f
HTH, AD7six Please note: The manual/bakery is a good place to start any quest for info. The cake search (at the time of writing) erroneously reports less/no results for the google group. The wiki may contain incorrect info - read at your own risk (it's mainly user submitted) :) You may get your answer quicker by asking on the IRC Channel (you can access it with just a browser here: http://irc.cakephp.org). On Oct 26, 9:35 am, "AD7six" <[EMAIL PROTECTED]> wrote: > Hi Bingo, > > The sql above seems a bit overcomplex, maybe I'm mising something. > > Why not: > > Create a list of unique Singer/Artist Ids from the song model > Use that list of Ids. > > ? > > To create the singer id list would be something like $data = > $this->Singer->Song->findAll(null,array("DISTINCT > 'Song.singer_id'),null,null,null,-1) > To get your singers sorted would be something like > $this->Singer->findAll(array("Singer.id"=>$arrayOfIds),null,$sort,$limit,$page) > > It is quite rare that the cake find methods can't handle what you want > nowadays. > > HTH, > > AD7six > Please note: > The manual/bakery is a good place to start any quest for info. > The cake search (at the time of writing) erroneously reports less/no > results for the google group. > The wiki may contain incorrect info - read at your own risk (it's > mainly user submitted) :) > You may get your answer quicker by asking on the IRC Channel (you can > access it with just a browser here:http://irc.cakephp.org). > > On Oct 25, 8:56 pm, "bingo" <[EMAIL PROTECTED]> wrote: > > > hi, > > > Thanks to Christoph. I found a solution but wondering is there any > > better way to get rid of this long SQL query. Its fine for me to have a > > multistep procedure > > > $query = <<< SQL > > select > > Users.id as user_id, > > Users.name as user_name, > > Singers.id as singer_id, > > Singers.name as singer_name, > > count(Songs.id) as songs_in_lib > > from > > Users left join Songs_users on Users.id = Songs_users.user_id, > > Singers left join Singers_songs on Singers.id = > > Singers_songs.singer_id, > > Songs > > where > > Songs.id = Singers_songs.song_id and > > Songs_users.song_id = Songs.id and > > Users.id = 1 > > group by > > Users.id, > > Users.name, > > Singers.id, > > Singers.name > > order by > > Users.name, > > Singers.name > > SQL; > > > $data = $this->User->query($query); > > $this->set('data',$data); > > > Regards, > > Ritesh --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
