数据是这样的,我们需要找出满足name=bbb AND age=99的item_id

SELECT * FROM user where ((user.k='age' AND user.v='99') OR (user.k='name' AND user.v='bbb'))

这样可以找出符合条件的行,但是item_id=1并不满足name=bbb

 

所以我们需要再添加一个约束,让MYSQL再次针对item_id进行筛选

 

SELECT * FROM user where ((user.k='age' AND user.v='99') OR (user.k='name' AND user.v='bbb')) GROUP BY item_id HAVING COUNT(*) = 2

在YII2中我们需要这样写:

//过滤基本字段
		$where = self::filter_where($base_where);
		//进行搜索
		$query = BbItem::find()->asArray()->joinWith('ext_field');
		
		if(!empty($where['base'])) {$query->where($where['base']);}
		if(!empty($where['and']))
		{
			foreach($where['and'] as $one)
			{
				$query->andWhere($one);
			}
		}
		
		//追加扩展字段条件
		$ext = ['OR'];
		$ext_empty_flag = true;
		$ext_count = 0;

		foreach($category_info['ext_field'] as $k => $one)
		{
			if(isset($ext_where[$one['table_keyname']])) {
				$table_key_name = $one['table_keyname'];
				$ext_empty_flag = false;
				++$ext_count;
				//如果是数组则拼成字符串
				if(is_array($ext_where[$table_key_name]))
				{
					$val = implode('|',$ext_where[$table_key_name]);
				} else {
					$val = $ext_where[$table_key_name];
				}
				
				$ext[] = ['AND',
				BbItemExtFieldData::tableName().'.table_keyname=\''.$table_key_name.'\'',
				BbItemExtFieldData::tableName().'.value=\''.$val.'\'',
				BbItemExtFieldData::tableName().'.ext_id=\''.$one['id'].'\''];
			}
			
			
		}
		
		if(empty($ext_empty_flag))
		{

				$query->andWhere($ext);
			
		}
		//添加额外约束
		$query->groupBy(BbItemExtFieldData::tableName().'.item_id')->having('COUNT(*)='.$ext_count);