Re: need help with specifying a complex query
Hi Bingo, The key to being able to sort is to get the findAll method (or if that turns out to not be direcltly possible your custom, but exactly the same format, sql query) to return you what you want rather than a sql query followed by a php for each loop. http://groups-beta.google.com/group/cake-php/search?group=cake-phpq=max+nateqt_g=Search+this+group Why do you have models for your join tables...? You may find that counterCache does what you want regarding the counting how many Songs a singer has, I haven´t needed to use it yet so can't offer more than a point in that direction. 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, 5:08 pm, bingo [EMAIL PROTECTED] wrote: hi bakers (especially AD7six and Christoph) Now, I did find a solution for specifying the query, but getting a trouble on how to set pagination. I want to set the pagination on name, count or id any ideas on how to do to set pagination. Regards --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: need help with specifying a complex query
hi AD7six,
this was the best solution I could think of...can you think this can be
further optimized.. I am not sure about counterCache...I will looking
into ...please let me know if you can think this query can be further
optimized...
Regards,
bingo
SOLUTION=
$this-SongsUser-recursive = 0;
$userid = $this-othAuth-user('id');
if(empty($userid)) return;
$criteria= array('user_id' = {$userid});
// get list of songs for a given user
$plist = $this-SongsUser-findAll($criteria, array('song_id'), null,
null, -1);
$mySongList = array();
foreach($plist as $value){
$mySongList[] = $value['SongsUser']['song_id'];
}
// get list of singers
$singers = array();
if(!empty($mySongList)){
// unbind songs...as we don't want to retrieve songs
$this-Singer-SingersSong-unbindModel(array('belongsTo'=array('Song')));
// set parameters
$where = 'SingersSong.song_id in (' . implode(',', $mySongList) . ')
Group by SingersSong.singer_id';
$fields = array('Singer.id', 'Singer.name', 'count(*) as nos');
// options for pagination
$options = Array (
'modelClass' ='SingersSong',
sortByClass=SingersSong, // Different default sort class
'sortBy' ='singer_id'
);
//set pagination
list($order, $limit, $page) = $this-Pagination-init($where, NULL,
$options);
// if order is based on count of songs
if(strpos($order,'nos')){
if(strpos($order, 'ASC'))
$order = '`nos` ASC';
else
$order = '`nos` DESC';
}
// get list of singers
$alist = $this-Singer-SingersSong-findAll($where, $fields, $order,
$limit, $page, null, 1);
$this-set('data', $alist);
}
==
On Oct 30, 2:50 am, AD7six [EMAIL PROTECTED] wrote:
Hi Bingo,
The key to being able to sort is to get the findAll method (or if that
turns out to not be direcltly possible your custom, but exactly the
same format, sql query) to return you what you want rather than a sql
query followed by a php for each loop.
http://groups-beta.google.com/group/cake-php/search?group=cake-phpq=...
Why do you have models for your join tables...? You may find that
counterCache does what you want regarding the counting how many Songs a
singer has, I haven´t needed to use it yet so can't offer more than a
point in that direction.
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, 5:08 pm, bingo [EMAIL PROTECTED] wrote:
hi bakers
(especially AD7six and Christoph)
Now, I did find a solution for specifying the query, but getting a
trouble on how to set pagination. I want to set the pagination on name,
count or id
any ideas on how to do to set pagination.
Regards- Hide quoted text -- Show quoted text -
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
Re: need help with specifying a complex query
hi AD7six, I posted my solution ...but not sure why its not here..I will post it again if fails to appear...but after you mentioned about countercache, I checked into it...it seems I wont be able to use countercache..as I just don't to calculate total number of songs by each singer...but total number of songs by a singer based on what songs a user has in his/her library... 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 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 -~--~~~~--~~--~--~---
Re: need help with specifying a complex query
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
-~--~~~~--~~--~--~---
Re: need help with specifying a complex query
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
-~--~~~~--~~--~--~---
Re: need help with specifying a complex query
Thanks AD7six and Christoph,
got everything working with minimum SQL and in a clean way...
For others reference.this was my solution
$criteria = array('user_id' = {$userid}); // basicaly get id of the
user
$songlist = $this-SongsUser-findAll($criteria, array('song_id'),
null, null, -1); // find all the songs that in the library of the user
and set recursive to -1
$mySongList = array();
foreach($songlist as $value){
$mySongList[] = $value['SongsUser']['song_id']; // this is data
massaging for the next step
}
// now based on the list of the songs get unique list of singers
$this-Singer-SingersSong-unbindModel(array('belongsTo'=array('Song')));
// I don't want to retrieve details about songs and hence unbinding
songs model
$singerList =
$this-Singer-SingersSong-findAll(array(SingersSong.song_id =
$mySongList), array('DISTINCT Singer.id', 'Singer.name'), null, null,
null, null, 1);
// since i also wanted count...I need to run a loop and use findCount
...I am cleaning some data
$singers = array();
foreach($singerList as $value){
$temp['id'] = $value['Singer']['id'];
$temp['name'] = $value['Singer']['fname'];
$temp['count'] =
$this-SingersSong-findCount(array('SingersSong.song_id' =
$mySongList,
'SingersSong.singer_id' = $value['Singer']['id']));
$singers[] = $temp;
}
print_r($singers);
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
Re: need help with specifying a complex query
hi bakers (especially AD7six and Christoph) Now, I did find a solution for specifying the query, but getting a trouble on how to set pagination. I want to set the pagination on name, count or id any ideas on how to do to set pagination. Regards --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: need help with specifying a complex query
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
-~--~~~~--~~--~--~---
Re: need help with specifying a complex query
Hmm, posted an answer ... but it does not appear?!?! --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: need help with specifying a complex query
Hello Bingo,
tried the whole scenario:
?php
class User extends AppModel
{
var $name = 'User';
var $recursive = 2;
var $hasAndBelongsToMany = array('Song');
}
?
?php
class Singer extends AppModel
{
var $name = 'Singer';
var $recursive = 2;
var $hasAndBelongsToMany = array('Song');
}
?
?php
class Song extends AppModel
{
var $name = 'Song';
var $recursive = 2;
var $hasAndBelongsToMany = array('Singer','User');
}
?
// in the UsersController
...
...
$data = $this-User-findAll();
$this-set('data',$data);
...
...
You should get a structure like this:
_
Array
(
[0] = Array
(
[User] = Array
(
[id] = 1
[name] = Christoph
)
[Song] = Array
(
[0] = Array
(
[id] = 2
[title] = Another One Bites The Dust
[album] =
[Singer] = Array
(
[0] = Array
(
[id] = 1
[name] = Queen
)
)
[User] = Array
(
[0] = Array
(
[id] = 1
[name] = Christoph
)
)
)
[1] = Array
(
[id] = 3
[title] = You Are So Beautiful
[album] =
[Singer] = Array
(
[0] = Array
(
[id] = 2
[name] = Joe Cocker
)
)
[User] = Array
(
[0] = Array
(
[id] = 1
[name] = Christoph
)
[1] = Array
(
[id] = 2
[name] = Hanny
)
)
)
)
)
[1] = Array
(
[User] = Array
(
[id] = 2
[name] = Hanny
)
[Song] = Array
(
[0] = Array
(
[id] = 1
[title] = We Will Rock You
[album] =
[Singer] = Array
(
[0] = Array
(
[id] = 1
[name] = Queen
)
)
[User] = Array
(
[0] = Array
(
[id] = 2
[name] = Hanny
)
)
)
[1] = Array
(
[id] = 3
[title] = You Are So Beautiful
[album] =
[Singer] = Array
(
[0] = Array
(
[id] = 2
[name] = Joe Cocker
)
)
[User] = Array
(
[0] = Array
(
[id] = 1
[name] = Christoph
)
[1] = Array
(
[id] = 2
[name] = Hanny
)
)
)
)
)
)
_
Detailed information on associations:
http://wiki.cakephp.org/docs:understanding_associations
Hope it works for you!
Greetings, Christoph :-)
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
Re: need help with specifying a complex query
Hi Christoph, Thanks for the query. I got it working. About your question...It is not necessary to fetch all the data at onceactually I was expecting a multistep procedure...the way it will be going to work is that the user clicks on button (title Show singers) and then in the view a list all the singers is presented. Although, I am able to use the above sql query, I am interested in knowing any simple multistep process. By using multistep process, probably I can avoid writing SQL query. I really hate writing SQL queries :( Regards --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
