function farm_inventory_query in farmOS 7

Build a query to calculate an asset's inventory level.

Parameters

int|string $asset_id: The asset id to search for. This can either be a specific id, or a field alias string from another query (ie: 'mytable.assetid'). For an example of field alias string usage, see the Views field handler code in farm_inventory_handler_field_asset_inventory_value::query().

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.

Return value

\SelectQuery Returns a SelectQuery object.

2 calls to farm_inventory_query()

farm_inventory in modules/farm/farm_inventory/farm_inventory.module

Calculate an asset's inventory level.

farm_inventory_handler_field_asset_inventory_value::query in modules/farm/farm_inventory/views/handlers/farm_inventory_handler_field_asset_inventory_value.inc

Called to add the field to a query.

File

modules/farm/farm_inventory/farm_inventory.module, line 473

Code

function farm_inventory_query($asset_id, $time = REQUEST_TIME, $done = TRUE) {

  /**
   * Please read the comments in farm_log_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 $asset_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($asset_id) || $asset_id < 0) {
    $asset_id = db_escape_field($asset_id);
  }

  // Use the farm_log_asset_query() helper function to start a query object.
  $query = farm_log_query($time, $done);

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

  // Join in the Inventory field collection. Use an inner join to exclude logs
  // that do not have an inventory field collection attached.
  $query
    ->innerJoin('field_data_field_farm_inventory', 'ss_fdffi', "ss_fdffi.entity_type = 'log' AND ss_fdffi.entity_id = ss_log.id AND ss_fdffi.deleted = 0");

  // Join in the inventory adjustment asset and filter to only include
  // inventory adjustments that reference the specified asset. Use an inner
  // join to exclude logs that do not have an inventory asset reference.
  $query
    ->innerJoin('field_data_field_farm_inventory_asset', 'ss_fdffia', "ss_fdffia.entity_id = ss_fdffi.field_farm_inventory_value AND ss_fdffia.deleted = 0");
  $query
    ->where('ss_fdffia.field_farm_inventory_asset_target_id = ' . $asset_id);

  // Join in the inventory adjustment value. Use an inner join to exclude logs
  // that do not have an inventory adjustment value.
  $query
    ->innerJoin('field_data_field_farm_inventory_value', 'ss_fdffiv', "ss_fdffiv.entity_id = ss_fdffi.field_farm_inventory_value AND ss_fdffiv.deleted = 0");

  // Add an expression that calculates the SUM of all values.
  $query
    ->addExpression('SUM(ss_fdffiv.field_farm_inventory_value_numerator / ss_fdffiv.field_farm_inventory_value_denominator)', 'inventory');

  // Remove the order by fields that were added by default in farm_log_query().
  // Since we are only adding an expression, and no other fields, we can't have
  // any order by fields, otherwise proper databases (like PostgreSQL) require
  // the order by fields to also be included in an aggregate function or in a
  // group by clause.
  $order_by_fields =& $query
    ->getOrderBy();
  $order_by_fields = array();

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