public static function getTopBuilder($count, $location = null) { if ($location) { $city = GeoCityApi::getCityByName($location['city']); if ($count) { $sql = 'SELECT * from user_builder_profile JOIN (SELECT builder_id from user_builder_rating r GROUP BY r.builder_id order by AVG(r.rate) DESC LIMIT ' . "{$count}" . ') AS ratings ON (user_builder_profile.id=ratings.builder_id) where city_id=' . "{$city->id}" . ' ORDER BY rand()'; } $sql = 'SELECT * from user_builder_profile JOIN (SELECT builder_id from user_builder_rating r GROUP BY r.builder_id order by AVG(r.rate) DESC) AS ratings ON (user_builder_profile.id=ratings.builder_id) where city_id=' . "{$city->id}" . ' ORDER BY rand()'; $properties = Yii::app()->db->createCommand($sql)->queryAll(); $countProperties = count($properties); if ($countProperties < $count) { $total = $count - $countProperties; $sql = 'SELECT * from user_builder_profile JOIN (SELECT builder_id from user_builder_rating r GROUP BY r.builder_id order by AVG(r.rate) DESC) AS ratings ON (user_builder_profile.id=ratings.builder_id) where city_id!=' . "{$city->id}" . ' AND state_id=' . "{$city->state_id}" . ' ORDER BY rand() limit ' . "{$total}" . ''; $propertiesState = Yii::app()->db->createCommand($sql)->queryAll(); $properties = ArrayUtils::joinArray($properties, $propertiesState); $countProperties = count($properties); if ($countProperties < $count) { $total = $count - $countProperties; $sql = 'SELECT * from user_builder_profile JOIN (SELECT builder_id from user_builder_rating r GROUP BY r.builder_id order by AVG(r.rate) DESC) AS ratings ON (user_builder_profile.id=ratings.builder_id) where city_id!=' . "{$city->id}" . ' AND state_id!=' . "{$city->state_id}" . ' ORDER BY rand() limit ' . "{$total}" . ''; $propertiesCountry = Yii::app()->db->createCommand($sql)->queryAll(); $properties = ArrayUtils::joinArray($properties, $propertiesCountry); } } } else { if ($count) { $sql = 'SELECT * from user_builder_profile JOIN (SELECT builder_id from user_builder_rating r GROUP BY r.builder_id order by AVG(r.rate) DESC LIMIT ' . "{$count}" . ') AS ratings ON (user_builder_profile.id=ratings.builder_id) ORDER BY rand()'; } $sql = 'SELECT * from user_builder_profile JOIN (SELECT builder_id from user_builder_rating r GROUP BY r.builder_id order by AVG(r.rate) DESC) AS ratings ON (user_builder_profile.id=ratings.builder_id) ORDER BY rand()'; $properties = Yii::app()->db->createCommand($sql)->queryAll(); } if ($properties) { return $properties; } else { return false; } /*$criteria=new CDbCriteria; $criteria->limit=$count; $criteria->order='rand()'; $criteria->with = 'propertyRatings r'; $criteria->order='MAX(AVG(r.rate))'; $properties=Property::model()->findAll($criteria); if($properties) return $properties; else return false;*/ }
/** * This method gets the count and returns the featured properties * Returns the model if found. * Returns false if not found. * * @param string $count * @return model || boolean */ public static function getFeaturedProperties($count, $location = null) { if ($location) { $city = GeoCityApi::getCityByName($location['city']); $criteria = new CDbCriteria(); $criteria->condition = 'featured=:featured AND city_id=:city'; $criteria->params = array(':featured' => 1, ':city' => $city->id); if ($count) { $criteria->limit = $count; } $criteria->order = 'rand()'; $properties = Property::model()->findAll($criteria); $countProperties = count($properties); if ($countProperties < $count) { $total = $count - $countProperties; $country = GeoCountryApi::getCountryByName($location['country']); $criteria = new CDbCriteria(); $criteria->condition = 'featured=:featured AND state_id=:state AND city_id!=:cityId'; $criteria->params = array(':featured' => 1, ':state' => $city->state_id, ':cityId' => $city->id); $criteria->limit = $total; $criteria->order = 'rand()'; $propertiesState = Property::model()->findAll($criteria); $properties = ArrayUtils::joinArray($properties, $propertiesState); $countProperties = count($properties); if ($countProperties < $count) { $total = $count - $countProperties; $criteria = new CDbCriteria(); $criteria->condition = 'featured=:featured AND state_id!=:state AND city_id!=:cityId'; $criteria->params = array(':featured' => 1, ':state' => $city->state_id, ':cityId' => $city->id); $criteria->limit = $total; $criteria->order = 'rand()'; $propertiesState = Property::model()->findAll($criteria); $properties = ArrayUtils::joinArray($properties, $propertiesState); } } if ($properties) { return $properties; } else { return false; } } else { $criteria = new CDbCriteria(); $criteria->condition = 'featured=:featured'; $criteria->params = array(':featured' => 1); if ($count) { $criteria->limit = $count; } $criteria->order = 'rand()'; $properties = Property::model()->findAll($criteria); if ($properties) { return $properties; } else { return false; } } }