Select Criteria

Yii 1.1 General Discussion
1

I need some help to build the criteria to the below sql…

[sql]

select company.company_name AS company, company.website AS domain_name, company.customer_id AS id,

contact.email AS emailid FROM customer_company AS company LEFT JOIN (SELECT email,

customer_id FROM contact GROUP BY customer_id ) AS contact ON

( company.customer_id = contact.customer_id ) WHERE company.group_id = ‘$group_id’

and ORDER BY $column_order $sort_type LIMIT $start,$limitrange;[/sql]

2

It would be good for you to read http://www.yiiframework.com/doc/api/1.1/CDbCriteria

and try it yourself first. See what works and what not. Then search in the forum for the issues you are seeing and if you cannot find the solution ask specific questions in the forum rather than asking for someone to give you the final solution.

3

Hi,

To the SQL code…

[sql]

select company.company_name AS company, company.website AS domain_name, company.customer_id AS id,

contact.email AS emailid FROM customer_company AS company LEFT JOIN (SELECT email,

customer_id FROM contact GROUP BY customer_id ) AS contact ON

( company.customer_id = contact.customer_id ) WHERE company.group_id = 7

and ORDER BY customer_id DESC;

[/sql]

I am using the criteria given below. When I executed… the values returned is having 1…

Can anybody help to find any mistake in the criteria I am using?




$criteria = new CDbCriteria;

$criteria->select = 't.company_name AS company, t.website AS domain_name, t.customer_id AS id,'.

								  'contact.email AS emailid';

$criteria->join = 'LEFT JOIN (SELECT email, customer_id FROM contact GROUP BY customer_id ) AS contact ON ( t.customer_id = contact.customer_id )';			

$criteria->compare('t.group_id', 7);

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

$model = Customer::model()->find($criteria);
4

What do you get when you execute the whole query in phpmyadmin and what do you get when you print it in yii. Also can you please show how exactly you are printing the result in Yii.

5

Hi bettor,

When I execute the query in phpmyadmin I fetch three rows




company domain_name 	id 	  emailid

BPCL 	www.bp.com 	KEY0004   arun@gmail.com

HP 	www.hp.com      KEY0007   anand@gmail.com

IOC 	www.ioc.com     KEY0010   akash@gmail.com

In the Controller


$this->_model = Customer::model()->find($criteria);

The above code should fetch only one row and after the above line I am checking it by coding


echo print_r($this->_model->customer_website);exit();

it shows value one to all the columns…If I fetch value I need to pass this to the view…

Please let me know If I am not correct … I am not using any debug tool…

6 

$this->_model = Customer::model()->find($criteria);

This will return only one result. Please read what find() stands for in the manual:

http://www.yiiframework.com/doc/api/1.1/CActiveRecord#find-detail

To return all records associated to your query you should use:


$this->_model = Customer::model()->findAll($criteria);

Let me know if this helps

7

I am getting value 1 in all the columns…Instead of company name, email-id and domain name…

8

ok simplify your query above. Remove the join just make it very simple until it works. Once you get it to work start building it back up again until you hit into what’s the problem

9

Hi bettor, thank you for replying…

I tried without join and without alias for column names to the ‘t’…rows are fetched from the table…

And also I tried with join and alias name for the the joining table … I get the emailid with the alias name…

But Why the alias name for column names is not working to the ‘t’ table?it is giving errors…

10

Hi Bettor,

I solved the problem… it is because attributes names are not same as column alias names… After renaming it… I am able to fetch the rows…