CODEWITHSUNDEEP
phpmysqldatabaseyiirelationships
Asfandyar Khan
Asfandyar Khan

Reputation: 1738

How to do a count of records in Yii Framework?

I have two tables 'Company' and 'Department'

CREATE TABLE Department (
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
deptName VARCHAR(40),
CompanyId TINYINT UNSIGNED NOT NULL,
FOREIGN KEY (CompanyId) REFERENCES Companies(id) ON DELETE CASCADE,
UNIQUE (deptName))

CREATE TABLE Company (
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
companyName VARCHAR(40),
email VARCHAR(60),
UNIQUE (companyName))

Im creating new records, updating, deleting and showing all records.. But Now i want to show Total number of Departments for specific company..For example:

Company A has 3 departments Company B has 2 departments. I just want to count number of departments. Output current is

ID: 1   
Company Name: CompanyA  
Email: [email protected]

but I want to show

ID: 1   
Company Name: CompanyA  
Email: [email protected]
Total Department: 3 //it can be different

This is just one record, but i have lots of companies showing in my output.. Department has different table.. which code do i have to change to show total number of departments

I have controllers 'CompanyController' and 'DepartmentController' created using CRUD operations in Gii have simple functions..

This is my index.php

<h1>Companies</h1>

<?php $this->widget('zii.widgets.CListView', array(
'dataProvider'=>$dataProvider,
'itemView'=>'_view',
)); ?>

This is my _view file

<div class="view">

<b><?php echo CHtml::encode($data->getAttributeLabel('id')); ?>:</b>
<?php echo CHtml::link(CHtml::encode($data->id), array('view', 'id'=>$data->id)); ?>
<br />

<b><?php echo CHtml::encode($data->getAttributeLabel('companyName')); ?>:</b>
<?php echo CHtml::encode($data->companyName); ?>

Im not sure where should count the data.. I have written a function for count total departments in department controller but it is not working here is my function

public function totalDept()
{
    $CompanyId=1;
    $totalDept=Department::model()->findAll($CompanyId);
    $numberOfDept=count($totalDept);
    if ($numberOfDept> 0) {
        echo "There are" .$numberOfDept. "in this company";
    } else {
        echo 'This company has No Department so far...';
    }

}

and when i show in my view it doesnot work.. it says property Department.totaldept not defined

<b><?php echo CHtml::encode($data->getAttributeLabel('Total Departments')); ?>:</b>
<?php echo CHtml::encode(Department::model()->totalDept); ?>
<br />

whatever im trying to do is this the correct way or im doing it wrong, Im just newbie to yii, just trying to learn yii..

Upvotes: 0

Views: 618

Answers (2)

batusa
batusa

Reputation: 41

You can simply define an "attribute" in your relations() method of your Company AR class, which will perform an statistical query (by default a count). You can do that by using "self::STAT" as the type of relation, see example below:

public function relations()
{
    return array(
        // other relationships
        'totalOfdepartments' => array(self::STAT, 'Department', 'CompanyId'),
    );
}

You can get more informations here: http://www.yiiframework.com/doc/guide/1.1/en/database.arr#statistical-query

Upvotes: 1

yetanotherse
yetanotherse

Reputation: 500

As per Yii's conventions. you should first define the relationship between Company and Department in the model files.

In your case - Company has many Department so in your Company model, define the relation as below (assuming you are using Yii 1.x)

public function relations()
{
    return array(
        // other relationships
        'departments' => array(self::HAS_MANY, 'Department', 'CompanyId'),
    );
}

Once you have defined this relationship, you can get the count for company's departments as shown below in your _view file.

echo count($data->departments);

Upvotes: 1

Related Questions