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
-~----------~----~----~----~------~----~------~--~---
