3个表如何关联

International Chinese
1

我的3个表关联如下：

我想写成这样的SQL语句




mysql> select url_info.id,url_info.url_name,url_info.url_path,url_info.url_style

,url_info.created,url_info.modify,url_info.cid,group_concat(tag_name),url_info.s

tatus,category.category_name from url_info,category,tags_info where cid=category

.id and cid=952 and find_in_set(tags_info.id,tag_ids) group by url_info.id order

 by url_info.id;

在控制层中我是这么写的




$dataProvider=new CActiveDataProvider('url_info', array(

			'criteria'=>array(

                //'select'=>'t.id,t.url_name,t.url_path,t.url_style,t.created,t.modify,t.cid,group_concat(tags_info.tag_name),t.status,category.category_name',

			    'with'=>array('category','tags_info'),

			    'condition'=> 'cid=category.id and cid=:cid and find_in_set(tags_info.id,tag_ids)',

                            'params'=>'cid=category.id',

			    'group'=>'t.id',

		            'order'=>'t.id desc ',

            ),

			'pagination'=>array(

				'pageSize'=>self::PAGE_SIZE,

			),

		));

显示层中：




$this->widget('zii.widgets.grid.CGridView', array(

	'dataProvider'=>$dataProvider,

	'columns'=>array(

		'id',

		'category.category_name',

		'url_name',

		'url_path',

	       [b] 'group_concat(tags_info.tag_name)',//这边有问题[/b]

		//'created',

		'status',

		'clicks',

		/*

		'url_style',

		'modify',

		*/

		array(

			'class'=>'CButtonColumn',

		),

	),

))

抛出的错误是：The column must be specified in the format of "Name:Type:Label", where "Type" and "Label" are optional.




Source File


D:\www\www\htdocs\yii\framework\zii\widgets\grid\CGridView.php(236)


00224:         foreach($this->columns as $column)

00225:             $column->init();

00226:     }

00227: 

00228:     /**

00229:      * Creates a {@link CDataColumn} based on a shortcut column specification string.

00230:      * @param string the column specification string

00231:      * @return CDataColumn the column instance

00232:      */

00233:     protected function createDataColumn($text)

00234:     {

00235:         if(!preg_match('/^([\w\.]+)(<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />\w*))?(<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />.*))?$/',$text,$matches))

00236: throw new CException(Yii::t('zii','The column must be specified in the format of "Name:Type:Label", where "Type" and "Label" are optional.'));

00237:         $column=new CDataColumn($this);

00238:         $column->name=$matches[1];

00239:         if(isset($matches[3]))

00240:             $column->type=$matches[3];

00241:         if(isset($matches[5]))

00242:             $column->header=$matches[5];

00243:         return $column;

00244:     }

00245: 

00246:     /**

00247:      * Registers necessary client scripts.

00248:      */

不知道如何写！ 请教大家！

2

这个我问题 我解决了！能显示出来！ 但是出来的数据！和我要查询出的数据不对！

3

我还有一个和这个问题相关的问题！

我2个表A和B

A表结构如下： ID ,TAG_IDS

B表结构如下： ID ，name

A表数据： ID TAG_IDS

1 2,5,4

2 1,6,3,6

3 5

4 2,3

B表数据： ID name

1 足球

2 篮球

3 羽毛球

4 高尔夫球

5 棒球

6 乒乓球

其中A表中TAG_IDS 中的号码 等于B表中的ID

目标查询出：ID TAG_IDS

1 篮球,棒球,高尔夫球

2 足球,乒乓球,羽毛球,高尔夫球

3 棒球

4 篮球,羽毛球

如何写他们的关系：




	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'xxx'=>array(xxx),//这里面如何写？

		);

	}

假如不用这个 那我可以用JOIN

mysql> select a.id, group_concat(b.name) from a inner join b on find_in_set(b.id,a.tag_ids) group by a.id;

但是也不好写 find_in_set（）函数里面的东西 会报错！

看来只能 用写纯数据查询。 AR我可能用不起来！！！！

但是JO

4 



			$db = Yii::app()->db;

			$sql ="select url_info.id,url_info.url_name,url_info.cid,GROUP_CONCAT(tags_info.tag_name) AS tag_ids,url_info.status,category.category_name from url_info,category,tags_info where 1 ".$condition." group by url_info.id order by url_info.id desc limit 0,50;";

			$results = $db->createCommand($sql)->query($params);

			foreach($results as $result){

				$dataProvider=$result;

				}