public function UcRolesLicense::query in Commerce License 8.2

Return value

\Drupal\Core\Database\Query\SelectInterface

Overrides SqlBase::query

File

src/Plugin/migrate/source/d6/UcRolesLicense.php, line 29

Class

UcRolesLicense

Drupal 6 Ubercart roles expiration source.

Namespace

Drupal\commerce_license\Plugin\migrate\source\d6

Code

public function query() {
  $query = $this
    ->select('uc_roles_expirations', 'ure')
    ->fields('ure', [
    'reid',
    'uid',
    'rid',
    'expiration',
  ]);

  // Joining to {uc_roles_products} gets us the product node ID and the
  // duration configuration.
  // TODO: this join assumes there is only one product per role. If some
  // roles are sold by multiple products, this will break!
  $query
    ->innerJoin('uc_roles_products', 'urp', 'ure.rid = urp.rid');
  $query
    ->fields('urp', [
    'nid',
    'duration',
    'granularity',
  ]);

  // Get the orders that purchased this product.
  // Join to {uc_orders} via {uc_order_products}, getting first the order
  // line items that hold the product, and the the corresponding order.
  $query
    ->innerJoin('uc_order_products', 'uop', 'urp.nid = uop.nid');

  // This join also ensures that the orders are purchased by the users who
  // have a role granted.
  $query
    ->innerJoin('uc_orders', 'uo', 'uop.order_id = uo.order_id AND ure.uid = uo.uid');
  $query
    ->fields('uop', [
    'order_product_id',
  ]);
  $query
    ->fields('uo', [
    'created',
    'modified',
    'order_id',
  ]);

  // Use a groupwise mininum selfjoin to get only the earliest order by each
  // user for a role.
  // TODO: this assumes that later orders are renewals, and that there are no
  // gaps in a user's license ownership, e.g. user buys license, lets it
  // expire, buys another one.
  // TODO: this join should also have a condition on the product nid!
  $query
    ->leftJoin('uc_orders', 'uo_later', 'uo.uid = uo_later.uid AND uo.modified > uo_later.modified');
  $query
    ->isNull('uo_later.order_id');
  return $query;
}