Getting Count Of Images Of Every List Item By Using Csqldataprovider And Clistview

In controller,

		$listsql = 'SELECT refno, majorCategoryId, serviceTypeId, builtupArea, category, emirate, Path, imageURL, liveURL, district, sellprice, rent, property, bedrooms, bathrooms FROM unitlistings where '.$whereClause;

		$dataProvider=new CSqlDataProvider($listsql, array(





					 'sellprice', 'rent',







In search view





							  'viewData'=>array('areaUnit'=>$areaUnit, 'areaType'=>$areaType, 'currencyUnit' => $currencyUnit, 'currencyType' => $currencyType),





							)); ?>

In searchlist itemview

<a href ="<?php echo Yii::app()->createUrl('site/detail',array('refno'=>$data['refno'])); ?>"><img title="Click here to see 3 images for this property" style="border:none;" src="<?php echo $src; ?>" alt=""  width="184" height="131"/></a>

I’m trying to modify the query for changing the static img title to “Click here to see <?ph echo $data[‘imgs’]; ?> images for this property”

The old query is bringing property items from unitlistings table. the images table contain all the images for each item. So how can I bring the count of each item’s images ?

I modified the query to

$listsql = ‘SELECT ul.refno,… ul.bathrooms, COUNT(img.refno) as imgs FROM unitlistings ul, images img’.$whereClause;

but in list item view I get COUNT of images all the items based on $whereclause, not the count of images of each individual item

You have to add “grouping by” statement BY for example images’ table foreign key (I guess it’s refno?)

So your query should looks like:

$groupClause = 'GROUP BY images.refno';

$listsql = 'SELECT ul.refno,.... ul.bathrooms, COUNT(img.refno) as imgs FROM unitlistings ul, images img'.$whereClause . $groupClause;

There is no relation defined between the tables. Is it necessary for getting this done ?