/** * Gets droplets whose database id is above the specified minimum * * @param int $user_id Logged in user id * @param int $river_id Database ID of the river * @param int $since_id Lower limit of the droplet id * @return array */ public static function get_droplets_since_id($user_id, $river_id, $since_id, $filters = array(), $photos = FALSE) { // Check the cache $request_hash = hash('sha256', $user_id . $river_id . $since_id . var_export($filters, TRUE) . ($photos ? 1 : 0)); $cache_key = 'river_drops_since_' . $request_hash; // If the cache key is available (with default value set to FALSE) if ($droplets = Cache::instance()->get($cache_key, FALSE)) { return $droplets; } $droplets = array('total' => 0, 'droplets' => array()); $river_orm = ORM::factory('river', $river_id); if ($river_orm->loaded()) { $query = DB::select(array('droplets.id', 'id'), array('rivers_droplets.id', 'sort_id'), 'droplet_title', 'droplet_content', 'droplets.channel', 'identity_name', 'identity_avatar', array(DB::expr('DATE_FORMAT(droplets.droplet_date_pub, "%b %e, %Y %H:%i UTC")'), 'droplet_date_pub'), array('user_scores.score', 'user_score'), array('links.url', 'original_url'), 'comment_count')->from('droplets')->join('rivers_droplets', 'INNER')->on('rivers_droplets.droplet_id', '=', 'droplets.id')->join('identities', 'INNER')->on('droplets.identity_id', '=', 'identities.id')->where('droplets.processing_status', '=', Model_Droplet::PROCESSING_STATUS_COMPLETE)->where('rivers_droplets.river_id', '=', $river_id)->where('rivers_droplets.id', '>', $since_id); if ($photos) { $query->where('droplets.droplet_image', '>', 0); } // Apply the river filters Model_Droplet::apply_droplets_filter($query, $filters, $user_id, $river_orm); // Left join for user scores $query->join(array('droplet_scores', 'user_scores'), 'LEFT')->on('user_scores.droplet_id', '=', DB::expr('droplets.id AND user_scores.user_id = ' . $user_id))->join('links', 'LEFT')->on('links.id', '=', 'droplets.original_url'); // Group, order and limit $query->order_by('rivers_droplets.id', 'ASC')->limit(self::DROPLETS_PER_PAGE)->offset(0); $droplets['droplets'] = $query->execute()->as_array(); // Encode content and title as utf8 in case they arent foreach ($droplets['droplets'] as &$droplet) { Model_Droplet::utf8_encode($droplet); } // Populate the metadata Model_Droplet::populate_metadata($droplets['droplets'], $river_orm->account_id); } // Cache the drops if (!empty($droplets['droplets'])) { Cache::instance()->set($cache_key, $droplets); } return $droplets; }
/** * Retrieves the list of droplets that matche the specified filters * * @param array $filters Set of filters to apply to the droplets list * @param int $user_id ID of the user initiating the search * @param int $page Page number - for calculating the offset of the resultset * @param bool $photos - When TRUE, filter out only those droplets with photos */ public static function search($filters, $user_id, $page = 1, $photos = FALSE) { $user_orm = ORM::factory('user', $user_id); $droplets = array(); if ($user_orm->loaded()) { // Sanity check for the page number $page = empty($page) ? 1 : $page; // Build Buckets Query $query = DB::select(array('droplets.id', 'id'), array(DB::expr('UNIX_TIMESTAMP(droplets.droplet_date_add)'), 'sort_id'), 'droplet_title', 'droplet_content', 'droplets.channel', 'identity_name', 'identity_avatar', array(DB::expr('DATE_FORMAT(droplet_date_pub, "%b %e, %Y %H:%i UTC")'), 'droplet_date_pub'), array(DB::expr('SUM(all_scores.score)'), 'scores'), array('user_scores.score', 'user_score'))->from('droplets')->join('identities')->on('droplets.identity_id', '=', 'identities.id')->join(array('droplet_scores', 'all_scores'), 'LEFT')->on('all_scores.droplet_id', '=', 'droplets.id')->join(array('droplet_scores', 'user_scores'), 'LEFT')->on('user_scores.droplet_id', '=', DB::expr('droplets.id AND user_scores.user_id = ' . $user_id))->where('droplets.processing_status', '=', Model_Droplet::PROCESSING_STATUS_COMPLETE)->where('droplets.parent_id', '=', 0); self::apply_droplets_filter($query, $filters, $user_id); if ($photos) { $query->where('droplets.droplet_image', '>', 0); } $query->group_by('droplets.id')->order_by('droplets.droplet_date_add', 'DESC')->limit(20)->offset(20 * ($page - 1)); $droplets = $query->execute()->as_array(); Model_Droplet::populate_metadata($droplets, $user_orm->account->id); } return $droplets; }