不使用“在一起”的内连接关系

Question

i am trying to filter products by category using a scope and i do that by inner joining the categories the problem is it only works when i turn together on, which messes up pagination

i recall doing that without a problem also tried using 'on'=> instead of 'condition'=> inside the with() definition, works pretty much the same only the latter is slower db-wise i think

any ideas?

ps. left joining products on a category is unfortunately not an option

<?php

/**
 * ProductCategory model
 */
class ProductCategory extends CActiveRecord
{
    //...
    public function relations()
    {
        return array(
            //...
            'parent' => array(self::BELONGS_TO, 'ProductCategory', 'parentId'),
            'children' => array(self::HAS_MANY, 'ProductCategory', 'parentId'),
            //...
        );
    }
    //...
}

/**
 * Product model
 */
class Product extends CActiveRecord
{
    //...
    public function relations()
    {
        return array(
            //...
            'categoriesJunction' => array(self::HAS_MANY, 'ProductCategoriesProducts', 'productId'),
            'categories' => array(self::HAS_MANY, 'ProductCategory', array('categoryId'=>'id'), 'through'=>'categoriesJunction'),
            //...
        );
    }

    /**
     * Category scope
     * 
     * @param mixed Category ids the product must belong to (int or array of int)
     * @return \Product
     */
    public function category($category = null) {
        if ($category) {
            $category = ProductCategory::model()->resetScope()->with('children')->findByPk($category);

            if ($category) {
                $categories = array($category->id);

                if (is_array($category->children)) {
                    foreach ($category->children as $child) {
                        $categories[] = $child->id;
                    }
                }

                $this->getDbCriteria()->mergeWith(array(
                    'with' => array(
                        'categories' => array(
                            'on'=>'categories.id=' . implode(' or categories.id=', $categories),
                            'joinType' => 'inner join',
                            //'together'=>true,
                        ),
                    ),
                ));
            }
        }

        return $this;
    }
    //...
}

Answer 1

UPDATE: select => false is the way to go

D.Mill Apr 5 at 9:33

I agree with aboce comment, how does this mess up pagination? Maybe you are looking for 'select'=>false (inside 'categories' section of your criteria array)

after all I found a quite simple solution that works well for a scope, just changing the scope criteria part to

$this->getDbCriteria()->mergeWith(array(
    'with'=>array(
        'categories'=>array(
            'select'=>'false',
            'on'=>'categories.id=' . implode(' or categories.id=', $categories),
            'joinType'=>'inner join',
        ),
    ),
    'together'=>true,
));