Yii框架：帮助翻译关系SQL查询 [英] Yii Framework : Help to translate relational Sql query

问题描述

我遇到一个问题来写这个查询在Yii的模型风格为主。

I encountered a problem to write this query in a Yii model oriented style.

我有3个主要的表：问题，类别，国家  和关系表：questions_has_categories，questions_has_countries。

I have a 3 main tables : questions, categories, countries and relational tables : questions_has_categories, questions_has_countries.

现在我试图寻找属于国家，类别的问题。

Now i try to search questions that belongs to country, category.

通过正常的SQL语句我写的：

With normal SQL statement i write :

SELECT q.id, q.question, c.name, co.name
                FROM questions AS q, 
                     categories AS c, 
                     countries AS co,
                     questions_has_countries AS ta,
                     questions_has_categories AS qhc

                WHERE 
                     q.id = qhc.questions_id
                AND  c.id = qhc.categories_id
                AND  q.id = ta.questions_id 
                AND  co.id = ta.countries_id
                AND  c.id = 1 
                AND  co.id = 2

这工作得很好。 现在，随着Yii的活动记录我试图寻找这样的：

This works fine. Now with Yii Active Record i try to search like this :

$model = Questions::model()->with( 
         array( 'categories' => array (
                     'select' => 'name',
                     'condition' => 'categories.id=:cat_id',
                     'params' => array (':cat_id' => $_POST["Questions"]["categories"])
                     ) 
              ), 
         array( 'countries' => array (
                     'select' => 'name',
                     'condition' => 'countries.id=:cou_id',
                     'params' => array (':cou_id' => $_POST["Questions"]["countries"])
                     ) 
        )
)->findAll(array ('select' => 'question'));

这code方面只有类，而不是国家（适用于所有国家的回报，那并不过滤，缩小）。

This code respects only the categories, not countries ( returns for all countries, doesnt filter, narrow down ).

我究竟做错了什么？

我的提问模型关系函数如下所示：

My Question Model relations function looks like this :

public function relations()
{
return array('categories' => array(self::MANY_MANY, 'Categories',  'questions_has_categories(questions_id, categories_id)'),
             'countries' => array(self::MANY_MANY, 'Countries', 'questions_has_countries(questions_id, countries_id)'),
    );
}

请帮助:) 谢谢

Pls help :) Thanks

推荐答案

试试这个

$model = Questions::model()->with( 
             array( 'categories' => array (
                      'select' => 'name',
                      'condition' => 'categories.id=:cat_id',
                      'params' => array (':cat_id' => $_POST["Questions"]["categories"])
                    ),
                    'countries' => array (
                      'select' => 'name',
                      'condition' => 'countries.id=:cou_id',
                      'params' => array (':cou_id' => $_POST["Questions"]["countries"])
                    ) 
                  )
    )->findAll(array ('select' => 'question'));

这篇关于Yii框架：帮助翻译关系SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！