Complex find query for hasMany relationship in cakePHP 1.3

Question

Hi,

I have been busy with the cakePHP framework for a couple of months now and I really love it. At the moment I'm working on a very new project and it does the job like it should (I think ...) but I feel uncomfortable with some code I wrote. In fact I should optimize my paginate conditions query so I get immediately the right results (right now I manipulate the result set by a bunch of Set::extract method calls.

I'll sketch the relevant aspects of the application. I have a model 'Site' who has a hasMany relationship with the model 'SiteMeta'. This last table looks as follow: id, site_id, key, value, created.

In this last model I record several values of the site at various periods. The name of the key I want to store (e.g. alexarank, google pagerank, ...), and off course also the value. At a given interval I let my app update this database so I can track evolution of this values.

Now my problem is this.

On the overview page of the various websites (controller => Sites, action => index) I'd like to show the CURRENT pagerank of the website. Thus I need one exact SiteMeta record where the 'created' field is the highest and the value in 'key' should be matching the word 'pagerank'. I've tried several things I read on the net but got none of them working (containable, bindmodel, etc.). Probably I'm doing something wrong.

Right now I get results like this when I do a $this->paginate

Array
(
    [0] => Array
        (
            [Site] => Array
                (
                    [id] => 1
                    [parent_id] => 0
                    [title] => test
                    [url] => http://www.test.com
                    [slug] => www_test_com
                    [keywords] => cpc,seo
                    [language_id] => 1
                )
            [SiteMeta] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [site_id] => 1
                            [key] => pagerank
                            [value] => 5
                            [created] => 2010-08-03 00:00:00
                        )

                    [1] => Array
                        (
                            [id] => 2
                            [site_id] => 1
                            [key] => pagerank
                            [value] => 2
                            [created] => 2010-08-17 00:00:00
                        )

                    [2] => Array
                        (
                            [id] => 5
                            [site_id] => 1
                            [key] => alexa
                            [value] => 1900000
                            [created] => 2010-08-10 17:39:06
                        )
                )
        )

To get the pagerank I just loop through all the sites and manipulate this array I get. Next I filter the results with Set::extract. But this doens't feel quite right :)

$sitesToCheck = $this->paginate($this->_searchConditions($this->params));

foreach($sitesToCheck as $site) {
            $pagerank = $this->_getPageRank($site['Site']);
            $alexa = $this->_getAlexa($site['Site']);
            $site['Site']['pagerank'] = $pagerank;
            $sites[] = $site;
        }

if (isset($this->params['named']['gpr']) && $this->params['named']['gpr']) {
                $rank = explode('-', $this->params['named']['gpr']);
                        $min = $rank[0];$max = $rank[1];
                $sites = Set::extract('/Site[pagerank<=' . $max . '][pagerank>=' . $min .']', $sites);
        }

$this->set(compact('sites', 'direction'));          

Could you guys please help me to think about a solution for this? Thanks in advance.

Answer 1

Try something like this:

   $this->paginate = array(
        'fields'=>array(
            'Site.*',
            'SiteMeta.*',
            'MAX(SiteMeta.created) as last_date'
         ),
        'group' => 'SiteMeta.key'
        'conditions' => array(
            'SiteMeta.key' => 'pagerank'
        )
    );
    $data = $this->paginate('Site');

Or this:

   $conditions = array(
        'recursive' => 1,
        'fields'=>array(
            'Site.*',
            'SiteMeta.*',
            'MAX(SiteMeta.created) as last_date'
         ),
        'group' => 'SiteMeta.key'
        'conditions' => array(
            'SiteMeta.key' => 'pagerank'
        )
    );
    $data = $this->Site->find('all', $conditions);

If that does not work check this and this. I am 100% sure that it is possible to get the result you want with a single query.

Answer 2

Hi Laurent, as you are trying to retrieve data in a hasMany relationship, cakephp doesn't join the tables by default. If you go for joins you can do something like:

$this->paginate = array(
                   'joins'=>array(
                              array(
                               'table'=>'accounts',
                               'alias'=>'Account',
                               'type' =>'inner',
                               'conditions' =>array('User.id = Account.user_id')
                              )
                            ),
                            'conditions'=> array('OR' => 
                               array(
                                'Account.name'=>$this->params['named']['nickname'],
                                'User.id' => 5)
                               )
                            );
$users = $this->paginate();
         $this->set('users',$users);
debug($users);
$this->render('/users/index');

You have to fit this according to your needs of course. More on joins, like already mentioned in another answer.

Kind regards, benjamin.

Edit 1: This is because you are missing the second 'conditions'. See my code snippet. The first 'conditions' just states where the join happens, whereas the second 'conditions' makes the actual selection.

Edit 2: Here some info on how to write conditions in order to select needed data. You may want to use the max function of your rdbms on column created in your refined condition.

Edit 3: Containable and joins should not be used together. Quoted from the manual: Using joins with Containable behavior could lead to some SQL errors (duplicate tables), so you need to use the joins method as an alternative for Containable if your main goal is to perform searches based on related data. Containable is best suited to restricting the amount of related data brought by a find statement. You have not tried my edit 2 yet, I think.

Edit 4: One possible solution could be to add a field last_updated to the table Sites. This field can then be used in the second conditions statement to compare with the SiteMeta.created value.

Answer 3

Thanks for the contributions. I tried these options (also something with bindmodel but not working also) but still can't get this to work like it should be. If I define this

$this->paginate = array(
                    'joins'=>   array(
                                array(
                                    'table'=>'site_metas',
                                    'alias'=>'SiteMeta',
                                    'type' =>'inner',
                                    'conditions' =>array('Site.id = SiteMeta.site_id')  
                                    )                                                   
                        ),                                                                
        );

I get duplicate results I have a site with 3 different SiteMeta records and a site with 2 different record. The paginate method returns me 5 records in total. There's probably an easy solution for this, but I can't figure it out :)

Also I tried to write a sql query myself, but seems I can't use the pagination magic in that case. Query I'd like to imitate with pagination options and conditions is the following. The query returns exactly as I would like to get.

$sites = $this->Site->query('SELECT * FROM sites Site, site_metas SiteMeta WHERE SiteMeta.id = (select SiteMeta.id from site_metas SiteMeta WHERE Site.id = SiteMeta.site_id AND SiteMeta.key = \'pagerank\' order by created desc limit 0,1 )');

Answer 4

I also tried this kind of hack.

$sites = $this->paginate(
    array('1' => '1 AND SiteMeta.id = (SELECT SiteMeta.id FROM site_metas SiteMeta WHERE Site.id = SiteMeta.site_id AND SiteMeta.key = \'pagerank\' ORDER BY SiteMeta.created desc limit 1)'));

Result is I get the right amount of Site records in return, but each site has multiple SiteMeta records in the array, instead of one.

The raw query I ran before which is very similar to this actually returns just one.

Answer 5

Try something like this (with containable set up on your models):

$this->Site->recursive = -1;

$this->paginate = array(
    'conditions' => array(
        'Site.title' => 'title') //or whatever conditions you want... if any
    'contain' => array(
        'SiteMeta' => array(
             'conditions' => array(
                 'SiteMeta.key' => 'pagerank'),
             'limit' => 1,
             'order' => 'SiteMeta.created DESC')));

I use containable so much that I actually have this in my app_model file so it applies to all models:

var $actsAs = array('Containable');

Answer 6

Many thinks to all who managed to help me through this :) I got it fixed after all hehe.

Eventually this has been the trick for me

$this->paginate = array(
                    'joins'=>   array(
                                     array(
                                         'table'=>'site_metas',
                                         'alias'=>'SiteMeta',
                                         'type' =>'inner',
                                         'conditions' => array('Site.id = SiteMeta.site_id'))                                                                       
                                ), 
                    'group' => 'Site.id',
                    'contain' => array(
                        'SiteMeta' => array(
                                'conditions' => array(
                                    'SiteMeta.key' => 'pagerank'),
                                    'limit' => 1,
                                    'order' => SiteMeta.created DESC',
                                    )));

        $sites = $this->paginate();

Answer 7

Hi,

I think I'm getting closer. I get the right amount of results, but I can't seem to get the LATEST Pagerank value. What I have for now that works

$this->paginate = array(
                    'recursive' => -1,
                    'joins'=>   array(
                                array(
                                    'table'=>'site_metas',
                                    'alias'=>'SiteMeta',
                                    'type' =>'inner',
                                    'conditions' =>array('Site.id = SiteMeta.site_id'),  
                                    'order' => array('SiteMeta.created DESC')
                                    )                                                   
                        ),
                    'conditions' => array(
                        'SiteMeta.key = \'pagerank\'',

                    ),
                    'fields'=>array(
                            'Site.id', 
                            'Site.title',
                            'Site.url',
                            'SiteMeta.site_id',
                            'SiteMeta.key', 
                            'SiteMeta.value', 
                            'SiteMeta.created'),
                    'group' => 'Site.id'                                                 
        );

You mentioned I might be using the max function on SiteMeta.created but I can't figure where I need to implement this.

Greetings, Laurent

Answer 8

Hello Laurent,

I reviewed the posts again, and another thing that I could add is, that your database schema isn't fully compliant with the first normal form (field keys consists of multiple entries). Normalization could save you some trouble in the future, like inefficient queries.

Kind regards, Benjamin.