SandyRig
(Mysandyrig)
June 10, 2014, 12:09am
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);
Keith
(Kburton)
June 10, 2014, 9:08am
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.
SandyRig
(Mysandyrig)
June 11, 2014, 12:22am
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();
mmbalaa
(Mmbalasundaram)
June 11, 2014, 7:32am
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