function farm_group_members_query in farmOS 7

Build a query to find members of a specific group.

Parameters

int $group_id: The group's asset id to search for.

int $time: Unix timestamp limiter. Only logs before this time will be included. Defaults to the current time. Set to 0 to load the absolute last.

$done: Whether or not to only show logs that are marked as "done". TRUE will limit to logs that are done, and FALSE will limit to logs that are not done. If any other value is used, no filtering will be applied. Defaults to TRUE.

bool $archived: Whether or not to include archived member assets. Defaults to FALSE.

Return value

\SelectQuery Returns a SelectQuery object.

1 call to farm_group_members_query()

farm_group_members in modules/farm/farm_group/farm_group.module

Load all members of a group.

File

modules/farm/farm_group/farm_group.module, line 736

Code

function farm_group_members_query($group_id, $time = REQUEST_TIME, $done = TRUE, $archived = TRUE) {

  /**
   * @todo
   * Merge/abstract with farm_movement_area_assets_query().
   */

  /**
   * Please read the comments in farm_log_asset_query() to understand how this
   * works, and to be aware of the limitations and responsibilities we have in
   * this function with regard to sanitizing query inputs.
   */

  // Ensure $group_id is valid, because it will be used directly in the query
  // string. This is defensive code. See note about farm_log_query() above.
  if (!is_numeric($group_id) || $group_id < 0) {
    $group_id = db_escape_field($group_id);
  }

  // Use the farm_log_asset_query() helper function to start a subquery object.
  // Do not limit the results to a single row because by the very nature of
  // this we want to find all assets in the group, which may come from multiple
  // logs.
  $subquery = farm_log_asset_query(NULL, $time, $done, NULL, FALSE);

  // Add a query tag to identify where this came from.
  $subquery
    ->addTag('farm_group_members_query');

  // Join in the Membership field collection. Use an inner join to exclude logs
  // that do not have a membership field collection attached.
  $subquery
    ->innerJoin('field_data_field_farm_membership', 'ss_fdffm', "ss_fdffm.entity_type = 'log' AND ss_fdffm.entity_id = ss_log.id AND ss_fdffm.deleted = 0");

  // Add the asset ID field.
  $subquery
    ->addField('ss_fdffa', 'field_farm_asset_target_id');

  // Add an expression to extract the assets most recent membership log ID.
  $subquery
    ->addExpression("SUBSTRING_INDEX(GROUP_CONCAT(ss_log.id ORDER BY ss_log.timestamp DESC, ss_log.id DESC SEPARATOR ','), ',', 1)", 'ss_current_log_id');

  // Group by asset ID.
  $subquery
    ->groupBy('ss_fdffa.field_farm_asset_target_id');

  // Create a query that selects from the subquery.
  $query = db_select($subquery, 'ss_asset_current_log');

  // Join in the asset's current log.
  $query
    ->join('log', 'ss_current_log', 'ss_current_log.id = ss_asset_current_log.ss_current_log_id');

  // Join in the Membership field collection. Use an inner join to exclude logs
  // that do not have a membership field collection attached.
  $query
    ->innerJoin('field_data_field_farm_membership', 'ss_current_log_fdffm', "ss_current_log_fdffm.entity_type = 'log' AND ss_current_log_fdffm.entity_id = ss_current_log.id AND ss_current_log_fdffm.deleted = 0");

  // Join in the membership's "group" field, and filter to only include logs
  // that have a membership that references the specified group. Use an inner
  // join to exclude logs that do not have a group reference.
  $query
    ->innerJoin('field_data_field_farm_group', 'ss_current_log_fdffg', "ss_current_log_fdffg.entity_type = 'field_collection_item' AND ss_current_log_fdffg.bundle = 'field_farm_membership' AND ss_current_log_fdffg.entity_id = ss_current_log_fdffm.field_farm_membership_value AND ss_current_log_fdffg.deleted = 0");
  $query
    ->where('ss_current_log_fdffg.field_farm_group_target_id = ' . $group_id);

  // Exclude archived assets, if requested.
  if (empty($archived)) {
    $query
      ->join('farm_asset', 'ss_current_log_fa', "ss_asset_current_log.field_farm_asset_target_id = ss_current_log_fa.id");
    $query
      ->where('ss_current_log_fa.archived = 0');
  }

  // Add the asset ID field.
  $query
    ->addField('ss_asset_current_log', 'field_farm_asset_target_id', 'asset_id');

  // Return the query object.
  return $query;
}