0

I have custom entity "product". It's base table is "product" and it's primary/id key is "entity_id". I have created all Views definitions for this entity.

Additionally I have another table called "product_stats" with following structure: entry_id | event | quantity | product_id | timestamp | uid | path | ip

where product_stats.product_id = product.entity_id

What I want to achieve: I want to be able to display

  • a) number of entries of particular event
  • b) sum the "quantity" of particular event for all my entities.

I have created Views definition for this table and I am able to use relationship but since I don't know how to tell Views to use GROUP BY I ended up with duplicates and I have no idea how to do b)(SUM() the quantity of the events per entity) at all.


Here is the table structure Table structure

Here is my Views definition:

  $data['product_stats']['table']['base'] = array(
    'field' => 'entry_id',
    'title' => t('Product statistics'),
    'help' => t('Table containing various logs related to products.'),
    'weight' => -10,
  );
  $data['product_stats']['table']['entity type'] = 'product';
  $data['product_stats']['table']['group']  = t('Product statistics');
  $data['product_stats']['table']['join'] = array(
    'product' => array(
      'left_field' => 'entity_id',
      'field' => 'product_id',
    ),
  );
  $data['product_stats']['product_id'] = array(
    'title' => t('Product ID'),
    'help' => t('The product ID the entry belongs to.'),
    'field' => array(
      'id' => 'standard',
    ),
    'argument' => array(
      'id' => 'numeric',
    ),
    'filter' => array(
      'id' => 'numeric',
    ),
    'sort' => array(
      'id' => 'standard',
    ),
    'relationship' => array(
      'id' => 'standard',
      'base' => 'product',
      'base field' => 'entity_id', // this is 'field' in doc.
      'title' => t('Product statistics'),
      'label' => t('Product statistics')
    ),
  );
  $data['product_stats']['event'] = array(
    'title' => t('Event'),
    'help' => t('The event type.'),
    'field' => array(
      'id' => 'standard',
    ),
    'sort' => array(
      'id' => 'standard',
    ),
    'filter' => array(
      'id' => 'string',
    ),
    'argument' => array(
      'id' => 'string',
    ),
  );
  $data['product_stats']['quantity'] = array(
    'title' => t('Quantity'),
    'help' => t('Quantity of products influenced during this event.'),
    'field' => array(
      'id' => 'standard',
    ),
    'sort' => array(
      'id' => 'numeric',
    ),
    'filter' => array(
      'id' => 'numeric',
    ),
    'argument' => array(
      'id' => 'standard',
    ),
  );
  $data['product_stats']['timestamp'] = array(
    'title' => t('Timestamp'),
    'help' => t('The UNIX timestamp when event occurred.'),
    'field' => array(
      'id' => 'date',
    ),
    'sort' => array(
      'id' => 'date'
    ),
    'filter' => array(
      'id' => 'date',
    ),
  );
  $data['product_stats']['uid'] = array(
    'title' => t('Author uid'),
    'help' => t('The active user during the event.'),
    'relationship' => array(
      'title' => t('Event author'),
      'help' => t('Relate the event to the user who triggered it.'),
      'id' => 'standard',
      'base' => 'users',
      'field' => 'uid',
      'label' => t('author'),
    ),
    'filter' => array(
      'id' => 'user_name',
    ),
    'argument' => array(
      'id' => 'numeric',
    ),
    'field' => array(
      'id' => 'user',
    ),
  );
  $data['product_stats']['path'] = array(
    'title' => t('Path'),
    'help' => t('The system path where the event occurred.'),
    'field' => array(
      'id' => 'standard',
    ),
    'sort' => array(
      'id' => 'standard',
    ),
    'filter' => array(
      'id' => 'string',
    ),
    'argument' => array(
      'id' => 'string',
    ),
  );

I presume that to display data only for selected event I would have to create a Views Field plugin where I would choose the event but I don't know how would I apply it to the field anyway.


In the end I think this can be done only by creating a Views Field plugin where user would specify the event(or not) and if the field should COUNT or SUM the results. But that is beyond my knowledge right now.

0

Click on the advanced link on the right side of the views configuration. There you find 'aggregation'. Enable this.

Once this is done you should add all fields you either want to aggregate or groupby.

  • I've tried that but ended up with weird results. So I removed the aggregation and even the relationship and ended up with information about how many entries there are per product which is good but without option to filter which event to count. So that just partially resolves my a) problem. – user21641 Apr 12 '14 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy