Representative Payment Method Views Relationship creates SQLSTATE[42S22] error

Hi,

I get the same error when creating reporting views for our store. We use the commerce payment transaction create date as our "order date".

Looks like several modules have had issues like these. My best guess is that there is something going on in commerce/modues/payment/includes/views/handlers/commerce_payment.views.inc

Maybe someone who understands this can tell what's going on. This hack at least got me some data in my particular query (it probably brakes everything else, but it might be an entry point for checking the documentation etc):

  $data['commerce_order']['payment_transaction_representative']['relationship'] = array(
    'title' => t('Representative payment transaction'),
    'label'  => t('Representative payment transaction'),
    'help' => t('Obtains a single representative payment transaction for each order, according to a chosen sort criterion.'),
    'handler' => 'views_handler_relationship_groupwise_max',
    'relationship field' => 'order_id',
    'outer field' => 'commerce_orderINNER.order_id',
    'argument table' => 'commerce_orderINNER',
    'argument field' =>  'order_id',
    'base'   => 'commerce_payment_transaction',
    'field'  => 'transaction_id',
  );

Best

Eric

So I took a quick gander at this. I wasn't able to make the view import work, nor was I able to ascertain exactly what you are doing from the description. This may be my own issue however :)

Could you perhaps create a view that will create the SQL error you mention that will work right out of the box in Commerce Kickstart 1.x with it's sample content, alogn with including any directions to reproduce there? I can then take a look at everything clearly and try and work on the problem for you :)

Unfortunately, the hack in #2 does not work for us. I have tried to find another solution but it is beyond me for the time being.

To reproduce the problem, simply add a "Representative payment transaction" relation to the default product report view that comes with the commerce report module : admin/commerce/reports/products

Thank you.

Tagging for http://contribkanban.com/#/board/commerce/7.x-1.x.

This is a bug in Views' views_handler_relationship_groupwise_max class.

Here is the related bug: #2295379: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.uid1' in 'where clause'

The error has cropped up again for me, when running Views 3.20 with Commerce 3.14. It's specifically related to the Representative Payment Method relationship. I am not sure if this is an issue with Views or Commerce Views Integration. The SQL error is exactly the same:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'commerce_order.order_id' in 'where clause'

I have reproduced the issue on two separate production sites.

Apologies to re-open and close this issue right away. From all I have discovered it appears to be a borked views handler for "Representative Node" relationships in Views 3.20. See the Related Issue in Views for more info.