Cdbcriteria - Mysql

Yii 1.1 General Discussion
1

Hi guys,

I have a sql query [sql]SELECT user.email_id

FROM user

JOIN subscribe ON user.id = subscribe.user_id

WHERE subscribe.group IN (1,2,3)

GROUP BY user.id;[/sql]

Want to fetch the email from user table where subscribe group is 1.

I have written like this but its not working


$criteria = new CDbCriteria();

	$criteria->select = 'user.email_id';

        $criteria->join = 'JOIN subscribe ON user.id = subscribe.user_id ';

        $criteria->order = ' u.start_date_time DESC';

        $criteria->group = ' user.id';

        $criteria->condition = 'subscribe.group=1';


	$user = User::model()->findByPk($criteria);

	var_dump($user); exit;

When I’m trying this, getting the following error


Object of class CDbCriteria could not be converted to string

I want to fetch the values using CDbCriteria, please any one help me?

2

Hi

Try with this code.




$criteria = new CDbCriteria();

$criteria->select = 't.email_id';

$criteria->join = 'JOIN subscribe as s ON t.id = s.user_id ';

$criteria->order = ' t.start_date_time DESC';

$criteria->group = ' t.id';

$criteria->condition = 's.group IN (1,2,3)';

$user = User::model()->find($criteria);

Thanks.

3

Thanks. but now getting the following error:


CDbException: 

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'user.email_id' in 'field list'. The SQL statement executed was: 

SELECT user.email_id 

FROM `user` `t`

JOIN subscribe ON user.id = subscribe.user_id 

WHERE subscribe.group IN (1,2,3)

GROUP BY user.id LIMIT 1

but my requirement is [sql]SELECT user.email_id

FROM user

JOIN subscribe ON user.id = subscribe.user_id

WHERE subscribe.group IN (1,2,3)

GROUP BY user.id;[/sql]

If [sql]SELECT user.email_id FROM user t[/sql], it gives the error, instant of the above i have write [sql]SELECT user.email_id FROM user[/sql] the output will come. why its not working with the (t). ?


$criteria = new CDbCriteria();

$criteria->select = 'user.email_id';


$criteria->join = 'JOIN subscribe ON user.id = subscribe.user_id ';

// $criteria->order = ' user.created_time DESC';

$criteria->group = 'user.id';

$criteria->condition = 'subscribe.group IN (1,2,3)';

$user = User::model()->find($criteria);

Edit: i got this with the following code.


$criteria = new CDbCriteria();

	$criteria->select = 't.email_id';

	$criteria->join = 'JOIN subscribe ON t.id = subscribe.user_id ';

	// $criteria->order = ' user.created_time DESC';

	$criteria->group = 't.id';

	$criteria->condition = 'subscribe.group IN (1)';

	

        $user = User::model()->findAll($criteria);

and now how can i pass the $criteria->condition = ‘subscribe.group IN (1)’;

group value from above the criteria>