CakePHP Paginate conditions on Join Table

Question

My cakephp app (2.2) app has the following:

NewsArticle HMBTM NewsCategory
NewsCategory HMBTM NewsArticle 

In my News article controller function index() I'm trying to get a paginated list of news articles that have the news category id 2.

Heres my code (which is wrong):

$this->paginate = array(
        'conditions' => array('newsArticle.news_category_id = 2'),
        'limit' => 10,
        'order' => array(
            'newsArticle.modified' => 'asc'
        )
    );
    $this->set('newsArticles', $this->paginate());

Could someone tell me where I'm going wrong? I'm guessing this is to do with the join table.

Heres the error I'm getting:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'newsArticle.news_category_id' in 'where clause'

Heres the SQL its producing:

SQL Query:

SELECT
    `NewsArticle`.`id`,
    `NewsArticle`.`title`,
    `NewsArticle`.`brief`,
    `NewsArticle`.`body`,
    `NewsArticle`.`filename`,
    `NewsArticle`.`dir`,
    `NewsArticle`.`mimetype`,
    `NewsArticle`.`filesize`,
    `NewsArticle`.`live`,
    `NewsArticle`.`user_id`,
    `NewsArticle`.`created`,
    `NewsArticle`.`modified`,
    `User`.`id`,
    `User`.`username`,
    `User`.`password`,
    `User`.`forename`,
    `User`.`surname`,
    `User`.`company`,
    `User`.`position`,
    `User`.`role`,
    `User`.`version_numbers_id`,
    `User`.`support_case_reference`,
    `User`.`support_web_password`,
    `User`.`webforms_email`,
    `User`.`tech_email`,
    `User`.`group_id`,
    `User`.`user_status_id`,
    `User`.`view_uat`,
    `User`.`manuals`,
    `User`.`filename`,
    `User`.`dir`,
    `User`.`mimetype`,
    `User`.`filesize`,
    `User`.`created`,
    `User`.`modified`,
    `User`.`live`,
    `User`.`tokenhash`
FROM `cakeclientarea`.`news_articles` AS `NewsArticle`
LEFT JOIN `cakeclientarea`.`users` AS `User`
    ON (`NewsArticle`.`user_id` = `User`.`id`)
WHERE
    `newsArticle`.`news_category_id` = 2
ORDER BY
    `newsArticle`.`modified` asc
LIMIT 10 

I can see from this it's not even touching the join table news_articles_news_categories.

Can anyone help, surely this is fairly simple to do? What am I missing? Thanks in advance.

Answer 1

Works for CakePHP 2.3.4 (June 2013)

The trouble is in Controller/Component/PaginatorComponent.php

The function validateSort(), return the wrong order line, as it is set to only return order on attributes in the same Model. As the documentation says on validateSort

Only fields or virtualFields can be sorted on.

To work around this:

In the Controller code (using User Controller as example):

public $components = array(
    'Paginator' => array(
        'className' => 'JoinedPaginator'),
    );

In the function:

$this->paginate = array(
    'recursive'=>-1, // should be used with joins
    'joins'=>array(
        array(
            'table'=>'groups',
            'type'=>'inner',
            'alias'=>'Group',
            'conditions'=>array('User.group_id = Group.id')
        )
    )
)

$fields = array('User.name', 'User.id', 'Group.name');

$users = $this->paginate('User', array(), $fields);

The third parameter in the paginate function is a white list, normally only items in the current object, but we added all white listed objects.

Overriding Paginator, making the Component/JoinedPaginator.php

<?php
App::uses('PaginatorComponent', 'Controller/Component');
class JoinedPaginatorComponent extends PaginatorComponent {

    public $Controller;

    function startup(Controller $controller) {
        $this->Controller = $controller;
    }

    public function validateSort(Model $object, array $options, array $whitelist = array()) {
        if (isset($options['sort'])) {
            $direction = null;
            if (isset($options['direction'])) {
                $direction = strtolower($options['direction']);
            }
            if (!in_array($direction, array('asc', 'desc'))) {
                $direction = 'asc';
            }
            $options['order'] = array($options['sort'] => $direction);
        }

        if (!empty($whitelist) && isset($options['order']) && is_array($options['order'])) {
            $field = key($options['order']);
            if (!in_array($field, $whitelist)) {
                $options['order'] = null;
                return $options;
            }
        }

        if (!empty($options['order']) && is_array($options['order'])) {
            $order = array();
            foreach ($options['order'] as $key => $value) {
                $field = $key;
                $alias = $object->alias;
                if (strpos($key, '.') !== false) {
                    list($alias, $field) = explode('.', $key);
                }
                // Changed the order field list, to include items in join tables
                if (isset($object->{$alias}) && $object->{$alias}->hasField($field, true)) {
                    $order[$alias . '.' . $field] = $value;
                } else if(in_array($alias.'.'.$field, $whitelist)){
                    // Adding a white list order, to include items in the white list
                    $order[$alias . '.' . $field] = $value;
                } else if ($object->hasField($field)) {
                    $order[$object->alias . '.' . $field] = $value;
                } elseif ($object->hasField($key, true)) {
                    $order[$field] = $value;
                }
            }
            $options['order'] = $order;
        }

        return $options;
    }

    public function paginate($object = null, $scope = array(), $whitelist = array()) {
        $this->settings = am ($this->Controller->paginate, $this->settings);
        return parent::paginate($object, $scope, $whitelist );
    }
}

As you see the settings is not sent to the new paginate object, so I override the paginate function as well, just to collect the settings.

This let you order on JOINED tables.

Answer 2

'conditions' => array('newsArticle.news_category_id = 2')

shoule be:

'conditions' => array('newsArticle.news_category_id'=>'2')

That doesn't really match the error but nothing else really sticks out.

You could try approaching it a different way to make sure it's not your model relationships causing the error.

$this->paginate('NewsArticle');
$this->paginate['NewsArticle']['conditions'] = array('newsArticle.news_category_id'=>'2');
pr($this->paginate);

Answer 3

You could also switch your function to the NewsCategory controller, use containable and paginate the results that way. This link provides a good example on how to get it working http://www.24100.net/2012/07/cakephp-hasandbelongstomany-relationship-queries-demystified/

Answer 4

I have spent 2 days looking for a solution for cakephp multiple join condition with pagination. Couldn't find any solution so thought of posting my solution to pagination with multiple joins and conditions. Hope this will come in handy for someone.

 public function index(){

        $data = $this->request->data;
        $searchText ='';

        if($this->request->is("post")) {
        $url = array('action'=>'index');
        $filters = array();
        echo("hello hello helloa");

        echo ($this->request->data);
                if(isset($data['Skinauditreport']['searchText']) && $data['Skinauditreport']['searchText']){
                        echo ($data['Skinauditreport']['searchText']);
                        //maybe clean up user input here??? or urlencode??
                        $filters['searchText'] = $data['Skinauditreport']['searchText'];
                }
                //redirect user to the index page including the selected filters
                $this->redirect(array_merge($url,$filters));
        }


        $this->Skinauditreport->recursive = 1;

        //Joining 2 tables, skinauditreports_users and users table
        $options['joins'] = array(
                array('table' =>'skinauditreports_users',
                'alias' => 'SkaUser',
                'type' => 'inner',
                'conditions' => array(
                        'Skinauditreport.id = SkaUser.ska_id'
                        )
                ),
                array('table' => 'users',
                'alias'=> 'User',
                'type' => 'inner',
                'conditions' => array(
                        'SkaUser.user_id = User.id'
                        )
                )
        );
        // Check user is admin user if then dont filter the report.
        if($this->Session->read('Auth.User.group_id') != '3'){
                $options['conditions'] = array(
                        'User.id' => $this->Auth->user('id')
                );
        }
        //Add this condition if there is a search text.
        if(isset($this->passedArgs["searchText"])) {
                 $options['conditions'] =array( array('OR' => array(
                                array('Skinauditreport.name LIKE' => "%".strtoupper($this->passedArgs["searchText"])."%"),
                                array('Skinauditreport.name LIKE' => "%".$this->passedArgs["searchText"]."%")
                        ))
                );

                $searchText = $this->passedArgs["searchText"];
        }

        $this->set('searchText',$searchText);
        //$skaReports = $this->Skinauditreport->find('all',$options);
        //$this->set('skaReports', $skaReports);

        $this->Paginator->settings = $options;
        $this->set('skaReports', $this->paginate('Skinauditreport'));

}