How To Write This Query In Yii

Yii 1.1 General Discussion
1

I have been trying for last 2 days but its not working. I need to write this query in a modal. It works fine in MySql.


SELECT DISTINCT (SUBSTRING_INDEX(REPLACE(REPLACE(href, 'www.', ''), 'http://', ''), '/', 1)) as domain_name FROM cfk_linkdata group by domain_name order by domain_name ASC

This is what I have tried so far and they both dont work.


Yii::app()->db->createCommand()

  	->select("SUBSTRING_INDEX(REPLACE(REPLACE(href, 'www.', ''), 'http://', ''), '/', 1) as domain_name")

  	->select('id, weight')

  	->from($this->tableName())

  	->queryAll();

  		

$criteria = new CDbCriteria;

$criteria->select = "SUBSTRING_INDEX(REPLACE(REPLACE(href, 'www.', ''), 'http://', ''), '/', 1) as domain_name, id, weight";

$criteria->group = 'domain_name';

$criteria->order = 'domain_name ASC';

$campaigns=LinkData::model()->findAll($criteria);
2

Try joining the two select clauses. This syntax will probably work (I don’t tend to use the command builder so I’m not so familiar with it):




Yii::app()->db->createCommand()

        ->select("id, weight, SUBSTRING_INDEX(REPLACE(REPLACE(href, 'www.', ''), 'http://', ''), '/', 1) as domain_name")

        ->from($this->tableName())

        ->queryAll();

Although you don’t seem to have included the “distinct” constraint anywhere in your Yii attempts.

3

The distinct part was not working. I tried adding DISTINCT to the code above but it didnt work. So I came up with another solution that seems to be working.


Yii::app()->db->createCommand()

  			->select("id, weight, SUBSTRING_INDEX(REPLACE(REPLACE(href, 'www.', ''), 'http://', ''), '/', 1) as domain_name")

  			->from($this->tableName())

  			->group('domain_name')

  			->queryAll();
4

Try This. It is working fine for me.




$user = Yii::app()->db->createCommand()

/** ->select('DISTINCT user_id, user_name')**/

    ->selectDistinct('user_id, user_name')

    ->from('user_name')

    ->queryAll();

Output Query:

SELECT DISTINCT user_id, user_name FROM tbl_user_commants