Unable To Get Data By Active Records Relations

Yii 1.1 General Discussion
1

Hi, i am trying to get data from 2 table (products, categories), but unable to get both tables data. The relationship is ONE-TO-MANY. Here is the relationship code in Product Model





class Product extends CActiveRecord

{

public function relations()

{		

	return array(

		'cat' => array(self::BELONGS_TO, 'Categories', 'catid'),	

			

	);

}

}

Relationship code in Category Model




class Categories extends CActiveRecord

{

public function relations()

{

	return array(

		'products' => array(self::HAS_MANY, 'Product', 'catid'),

	);

}

}

I am getting data in controller like this




$criteria = new CDbCriteria();

			$criter->select = "*";			

			$criteria->alias = "p";

			$criteria->condition = "cat.id = '".$categoryid."'";

			$criteria->order = "p.id";

			$criteria->together = true;

			$category_prods = Product::model()->with('cat')->findAll($criteria);

"

the code generates an error

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘cat.id’ in ‘where clause’

"

please guide me what is the problem here, how i can get data from multiple tables.

2

you want write a query just like it…




        criteria = new CDbCriteria;

	$criteria->select = 't.*, tu.* ';

	$criteria->join = ' JOIN `cat` AS `tu` ON t.cat_id = tu.id';

	$criteria->addCondition("cat.id='".$categoryid."'");

	$resultSet    =    Product::model()->findAll($criteria);
3

Thanks for your reply. But this is not returning fields from both tables. it is aonly returning data from table 1 "Product table".

4

yes if you want to fetch the second tabel value you want to must defind the public proerperty in

Product Model

for e.g

i want to disply the name on category table

  1. write a condition

$criteria->select = 't.*, tu,cat_name* ';

2)Defind the cat_name on Product model(Main model)

so in Product model


public cat_name;

i hope you got my point

5

Hi, sheraz.s,

I think your code should work as it is, if in fact the primary key of the Categories table is ‘id’. I’m afraid you don’t have ‘id’ column in Categories table.

BTW, I would code like the following:




$criteria = new CDbCriteria();

$criteria->with = array('cat');

$criteria->compare('cat.id', $categoryid);

// or 

// $criteria->compare('t.catid', $categoryid);

$criteria->order = "t.id";

$category_prods = Product::model()->findAll($criteria);

  1. No need to set the alias ‘p’ for Product table. Yii will use ‘t’ for the main table by default. (Of course you may use the alias ‘p’ for it if you want. It’s a personal preference.)

  2. ‘compare’ is always convenient and safe to use, because it automatically uses the parameter binding. It’s the short-cut for




$critaria->addCondition('cat.id = :catid');

$criteria->params[':catid'] = $catetoryid;
  1. No need to set ‘together’ to true, because it is a BELONGS_TO relation.

There’s no need to define a property for a field of Categories table in Product model, unless if you want to search and/or sort by it. You can access the category name by the syntax of ‘$prod->cat->name’.

6

hey! you talking about ‘Product’ model eh?

so just wondering if this line




$criteria->compare('cat.id', $categoryid);

should be, or i’m blind on something?




$criteria->compare('catid', $categoryid);
7

Ahhhaaaaaaaaaaaa, Issue resolved. Thank you dear softark :)