Re: need help with specifying a complex query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: need help with specifying a complex query

AD7six

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-php&q=max+nate&qt_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" <[hidden email]> 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 [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: need help with specifying a complex query

bingo-2


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" <[hidden email]> 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-php&q=...
>
> 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" <[hidden email]> 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 [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php
-~----------~----~----~----~------~----~------~--~---

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: need help with specifying a complex query

bingo-2
In reply to this post by AD7six

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 [hidden email]
To unsubscribe from this group, send email to [hidden email]
For more options, visit this group at http://groups.google.com/group/cake-php
-~----------~----~----~----~------~----~------~--~---

Loading...