View Issue Details

IDProjectCategoryView StatusLast Update
0005805OXID eShop (all versions)2.6. Administer orderspublic2016-06-03 16:27
Reporterhendrikfreytag 
PriorityhighSeveritymajorReproducibilityalways
Status acknowledgedResolutionopen 
Product Version4.8.8 / 5.1.8 
Target VersionFixed in Version 
Summary0005805: Can't show all orders with activated paypal module
DescriptionA customer has a problem with showing all orders with activated paypal module. It is impossible to show all orders with filter set to "all", because it is to slow.
Steps To ReproduceActivate PayPal.
Create a really big amount of orders.
Show all orders in backend.
Additional InformationThey have analyzed the problem:

The problem is the join created in _buildSelectString in class oePayPalOrder_List. But it is only needed if there is a filter on "Shop payment status". If there is no filter you can leave out the join.

They suggested a solution for _buildSelectString in class oePayPalOrder_List:

Index: modules/oe/oepaypal/controllers/admin/oepaypalorder_list.php
===================================================================
--- modules/oe/oepaypal/controllers/admin/oepaypalorder_list.php (revision 13126)
+++ modules/oe/oepaypal/controllers/admin/oepaypalorder_list.php (working copy)

     {
         $sSql = parent::_buildSelectString( $oListObject );
 
- $sPaymentTable = getViewName( "oxpayments" );
+ $sPaymentStatus = oxRegistry::getConfig()->getRequestParameter( "paypalpaymentstatus" );
+ $oPaymentStatusList = new oePayPalOrderPaymentStatusList();
 
- $sQ = ", `oepaypal_order`.`oepaypal_paymentstatus`, `payments`.`oxdesc` as `paymentname` from `oxorder`
- LEFT JOIN `oepaypal_order` ON `oepaypal_order`.`oepaypal_orderid` = `oxorder`.`oxid`
- LEFT JOIN `" . $sPaymentTable . "` AS `payments` on `payments`.oxid=oxorder.oxpaymenttype ";
+ if ( $sPaymentStatus && $sPaymentStatus != '-1' && in_array( $sPaymentStatus, $oPaymentStatusList->getArray() ) ) {
+ $sPaymentTable = getViewName( "oxpayments" );
 
- $sSql = str_replace( 'from oxorder', $sQ, $sSql);
+ $sQ = ", `oepaypal_order`.`oepaypal_paymentstatus`, `payments`.`oxdesc` as `paymentname` from `oxorder`
+ LEFT JOIN `oepaypal_order` ON `oepaypal_order`.`oepaypal_orderid` = `oxorder`.`oxid`
+ LEFT JOIN `" . $sPaymentTable . "` AS `payments` on `payments`.oxid=oxorder.oxpaymenttype ";
 
+ $sSql = str_replace( 'from oxorder', $sQ, $sSql);
+ }
+
         return $sSql;
     }
TagsPerformance
ThemeAzure
BrowserAll
PHP VersionNot defined
MySQL VersionNot defined

Relationships

has duplicate 0006406 closed module PayPal Backend orders can not be viewed because of performance issues 

Activities

Linas Kukulskis

2014-07-17 10:24

reporter   ~0010014

From first look it should be enough add index on `oxorder`.`oxpaymenttype`.

Bergfreunde

2014-07-24 16:07

reporter   ~0010033

Folgender Patch muss noch an Upstream nachgereicht werden:
--- modules/oe/oepaypal/controllers/admin/oepaypalorder_list.php (revision 13321)
+++ modules/oe/oepaypal/controllers/admin/oepaypalorder_list.php (revision 13322)
@@ -76,6 +76,13 @@
             LEFT JOIN `" . $sPaymentTable . "` AS `payments` on `payments`.oxid=oxorder.oxpaymenttype
";
 
             $sSql = str_replace( 'from oxorder', $sQ, $sSql);
+ } else {
+ $sPaymentTable = getViewName( "oxpayments" );
+
+ $sQ = ", `payments`.`oxdesc` as `paymentname` from `oxorder`
+ LEFT JOIN `" . $sPaymentTable . "` AS `payments` on `payments`.oxid=oxorder.oxpaymenttype
";
+
+ $sSql = str_replace( 'from oxorder', $sQ, $sSql);
         }
 
         return $sSql;

saulius.stasiukaitis

2014-09-11 16:06

reporter   ~0010151

This is shop problem.
order_list::_buildSelectString also joins by oxorder table oxpaymenttype field.
Also oxUserPayment::getPaymentByPaymentType selects by oxorder table oxpaymenttype field.
Will need to add missing index for oxorder table oxpaymenttype field.

saulius.stasiukaitis

2014-09-11 16:13

reporter   ~0010152

With PayPal order list has payment as first field. This field would be empty in suggested fix. Better workaround would be to add missing index.
Currently we postpone this bug fix due to RC code freeze.

Bergfreunde

2014-09-17 15:19

reporter   ~0010169

Adding the index doesn't solve the problem. Checking the orders in shop-admin still takes a lot of time when no payment-filter is set. the index just improves the performance when using the payment-filter.

Our engineer made some performance-tests, by showing all orders and using payment-filter "paypal":
with index but without our patch: 52 seconds
with our patch but without index: 8 seconds
with our patch and with index: 2 seconds

Best Regards,
Benni

QA

2016-06-03 16:27

administrator   ~0011623

There is additional information in 0006406.