You are here

Home » API reference » Open Social 8.9 » ContentBuilder.php » ContentBuilder

protected function ContentBuilder::sortBy in Open Social 8.9

Same name and namespace in other branches
  1. 8.8 modules/social_features/social_content_block/src/ContentBuilder.php \Drupal\social_content_block\ContentBuilder::sortBy()
  2. 10.3.x modules/social_features/social_content_block/src/ContentBuilder.php \Drupal\social_content_block\ContentBuilder::sortBy()
  3. 10.0.x modules/social_features/social_content_block/src/ContentBuilder.php \Drupal\social_content_block\ContentBuilder::sortBy()
  4. 10.1.x modules/social_features/social_content_block/src/ContentBuilder.php \Drupal\social_content_block\ContentBuilder::sortBy()
  5. 10.2.x modules/social_features/social_content_block/src/ContentBuilder.php \Drupal\social_content_block\ContentBuilder::sortBy()

Sorting and range logic by specific case.

Parameters

\Drupal\Core\Database\Query\SelectInterface $query: The query.

\Drupal\Core\Entity\EntityTypeInterface $entity_type: The entity type that is being queried.

string $sort_by: The type of sorting that should happen.

1 call to ContentBuilder::sortBy()
ContentBuilder::getEntities in modules/social_features/social_content_block/src/ContentBuilder.php
Function to get all the entities based on the filters.

File

modules/social_features/social_content_block/src/ContentBuilder.php, line 392

Class

ContentBuilder
Class ContentBuilder.

Namespace

Drupal\social_content_block

Code

protected function sortBy(SelectInterface $query, EntityTypeInterface $entity_type, string $sort_by) : void {

  // Define a lower limit for popular content so that content with a large
  // amount of comments/votes is not popular forever.
  // Sorry cool kids, your time's up.
  $popularity_time_start = strtotime('-90 days');

  // Provide some values that are often used in the query.
  $entity_type_id = $entity_type
    ->id();
  $entity_id_key = $entity_type
    ->getKey('id');
  switch ($sort_by) {

    // Creates a join to select the number of comments for a given entity
    // in a recent timeframe and use that for sorting.
    case 'most_commented':
      if ($entity_type_id === 'group') {
        $query
          ->leftJoin('post__field_recipient_group', 'pfrg', "base_table.{$entity_id_key} = pfrg.field_recipient_group_target_id");
        $query
          ->leftJoin('group_content_field_data', 'gfd', "base_table.{$entity_id_key} = gfd.gid AND gfd.type LIKE '%-group_node-%'");
        $query
          ->leftJoin('comment_field_data', 'cfd', "(base_table.{$entity_id_key} = cfd.entity_id AND cfd.entity_type=:entity_type) OR (pfrg.entity_id = cfd.entity_id AND cfd.entity_type='post') OR (gfd.entity_id = cfd.entity_id AND cfd.entity_type='node')", [
          'entity_type' => $entity_type_id,
        ]);
      }
      else {
        $query
          ->leftJoin('comment_field_data', 'cfd', "base_table.{$entity_id_key} = cfd.entity_id AND cfd.entity_type=:entity_type", [
          'entity_type' => $entity_type_id,
        ]);
      }
      $query
        ->addExpression('COUNT(cfd.cid)', 'comment_count');
      $query
        ->condition('cfd.status', 1, '=')
        ->condition('cfd.created', $popularity_time_start, '>')
        ->groupBy("base_table.{$entity_id_key}")
        ->orderBy('comment_count', 'DESC');
      break;

    // Creates a join to select the number of likes for a given entity in a
    // recent timeframe and use that for sorting.
    case 'most_liked':

      // For groups also check likes on posts in groups. This does not (yet)
      // take into account likes on comments on posts or likes on other group
      // content entities.
      if ($entity_type_id === 'group') {
        $query
          ->leftJoin('post__field_recipient_group', 'pfrg', "base_table.{$entity_id_key} = pfrg.field_recipient_group_target_id");
        $query
          ->leftJoin('group_content_field_data', 'gfd', "base_table.{$entity_id_key} = gfd.gid AND gfd.type LIKE '%-group_node-%'");
        $query
          ->leftJoin('votingapi_vote', 'vv', "(base_table.{$entity_id_key} = vv.entity_id AND vv.entity_type=:entity_type) OR (pfrg.entity_id = vv.entity_id AND vv.entity_type = 'post') OR (gfd.entity_id = vv.entity_id AND vv.entity_type = 'node')", [
          'entity_type' => $entity_type_id,
        ]);
      }
      else {
        $query
          ->leftJoin('votingapi_vote', 'vv', "base_table.{$entity_id_key} = vv.entity_id AND vv.entity_type=:entity_type", [
          'entity_type' => $entity_type_id,
        ]);
      }
      $query
        ->addExpression('COUNT(vv.id)', 'vote_count');

      // This assumes all votes are likes and all likes are equal. To
      // support downvoting or rating, the query should be altered.
      $query
        ->condition('vv.type', 'like')
        ->condition('vv.timestamp', $popularity_time_start, '>')
        ->groupBy("base_table.{$entity_id_key}")
        ->orderBy('vote_count', 'DESC');
      break;

    // Creates a join that pulls in all related entities, taking the highest
    // update time for all related entities as last interaction time and using
    // that as sort value.
    case 'last_interacted':
      if ($entity_type_id === 'group') {
        $query
          ->leftJoin('group_content_field_data', 'gfd', "base_table.{$entity_id_key} = gfd.gid");
        $query
          ->leftjoin('post__field_recipient_group', 'pst', "base_table.{$entity_id_key} = pst.field_recipient_group_target_id");
        $query
          ->leftjoin('post_field_data', 'pfd', 'pst.entity_id = pfd.id');
        $query
          ->leftjoin('comment_field_data', 'cfd', "pfd.id = cfd.entity_id AND cfd.entity_type = 'post'");
        $query
          ->leftJoin('votingapi_vote', 'vv', "pfd.id = vv.entity_id AND vv.entity_type = 'post'");
        $query
          ->leftjoin('node_field_data', 'nfd', 'gfd.entity_id = nfd.nid');
        $query
          ->addExpression('GREATEST(COALESCE(MAX(gfd.changed), 0),
            COALESCE(MAX(vv.timestamp), 0),
            COALESCE(MAX(cfd.changed), 0),
            COALESCE(MAX(nfd.changed), 0),
            COALESCE(MAX(pfd.changed), 0))', 'newest_timestamp');
        $query
          ->groupBy("base_table.{$entity_id_key}");
        $query
          ->orderBy('newest_timestamp', 'DESC');
      }
      elseif ($entity_type_id === 'node') {
        $query
          ->leftJoin('node_field_data', 'nfd', "base_table.{$entity_id_key} = nfd.nid");

        // Comment entity.
        $query
          ->leftjoin('comment_field_data', 'cfd', 'nfd.nid = cfd.entity_id');

        // Like node or comment related to node.
        $query
          ->leftjoin('votingapi_vote', 'vv', '(nfd.nid = vv.entity_id AND vv.entity_type = :entity_type_id) OR (cfd.cid = vv.entity_id)', [
          'entity_type_id' => $entity_type_id,
        ]);
        $query
          ->addExpression('GREATEST(COALESCE(MAX(vv.timestamp), 0),
          COALESCE(MAX(cfd.changed), 0),
          COALESCE(MAX(nfd.changed), 0))', 'newest_timestamp');
        $query
          ->groupBy("base_table.{$entity_id_key}");
        $query
          ->orderBy('newest_timestamp', 'DESC');
      }
      break;
    case 'event_date':
      $nfed_alias = $query
        ->leftJoin('node__field_event_date', 'nfed', "base_table.{$entity_id_key} = %alias.entity_id");
      $query
        ->orderBy("{$nfed_alias}.field_event_date_value", 'ASC');
      break;

    // Fall back by assuming the sorting option is a field.
    default:
      $query
        ->orderBy("base_table.{$sort_by}", 'DESC');
  }
}