Sql Expert Help Sought

[rotaour]

I implemented a second order_total field into the orders list query in my admin/orders.php file. This was to show what shipping method a customer chose so that our fullfillment dept. could expedite orders with faster than ground shipping.

 

 

You can see "ot2.title as order_shipping," and "and ot2.class = 'ot_shipping'" in the query below.

 

$orders_query_raw = "select distinct o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total, ot2.title as order_shipping, st.stores_name from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) left join " . TABLE_ORDERS_TOTAL . " ot2 on (o.orders_id = ot2.orders_id), " . TABLE_STORES . " st, " . TABLE_ORDERS_STATUS . " s " . ($admin_allowed_stores[0] == '*' ? " " : " inner join " . TABLE_ORDERS_PRODUCTS . " op on (o.orders_id = op.orders_id) ") . "where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' and ot2.class = 'ot_shipping' and o.orders_stores_id = st.stores_id " . ($admin_allowed_stores[0] == '*' ? " " : "  and ( (o.orders_stores_id in(" . implode(',' , $admin_allowed_stores) . ")) OR (op.products_distributors_id = '" . tep_get_distributor_id($login_id) . "') ) ") . "group by o.orders_id order by orders_id DESC";

 

This all works fine, but I am now implementing virtual products. Since there is no shipping entry in the orders_total table on orders with only virtual products the orders are not coming up in the orders listings because ot2.class is not equal to "ot_shipping"

 

I've tried changing the query to

$orders_query_raw = "select distinct o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total, ot2.title as order_shipping, st.stores_name from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) left join " . TABLE_ORDERS_TOTAL . " ot2 on (o.orders_id = ot2.orders_id), " . TABLE_STORES . " st, " . TABLE_ORDERS_STATUS . " s " . ($admin_allowed_stores[0] == '*' ? " " : " inner join " . TABLE_ORDERS_PRODUCTS . " op on (o.orders_id = op.orders_id) ") . "where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' and ((ot2.class = 'ot_shipping') OR (ot2.class = '')) and o.orders_stores_id = st.stores_id " . ($admin_allowed_stores[0] == '*' ? " " : "  and ( (o.orders_stores_id in(" . implode(',' , $admin_allowed_stores) . ")) OR (op.products_distributors_id = '" . tep_get_distributor_id($login_id) . "') ) ") . "group by o.orders_id order by orders_id DESC";

and tried changing the empty single quotes in the OR statement to NULL to no avail.

 

Is it possible to check if ot2.class is set and if so it must be equal to 'ot_shipping', if it is not set to ignore the field altogether and still select orders without a ot_shipping row in the orders_total table?

TIA

[esearing]

What happens if you eliminate the ot_shipping test in the where clause?

and ((ot2.class = 'ot_shipping')

[rotaour]

What happens if you eliminate the ot_shipping test in the where clause?

and ((ot2.class = 'ot_shipping')

 

It pulls all the orders but breaks the shipping type. class='ot_shipping' must be specified to get the proper row from the DB.

[rotaour]

OK, so I changed my approach to this problem. I decided to change the process() function in /includes/modules/order_total/ot_shipping.php to insert a row in the orders_total table for orders with no shipping, or virtual shipping as I've called it.

 

	function process() {
  global $order, $currencies;

  if (MODULE_ORDER_TOTAL_SHIPPING_FREE_SHIPPING == 'true') {
	switch (MODULE_ORDER_TOTAL_SHIPPING_DESTINATION) {
	  case 'national':
		if ($order->delivery['country_id'] == STORE_COUNTRY) $pass = true; break;
	  case 'international':
		if ($order->delivery['country_id'] != STORE_COUNTRY) $pass = true; break;
	  case 'both':
		$pass = true; break;
	  default:
		$pass = false; break;
	}

	if ( ($pass == true) && ( ($order->info['total'] - $order->info['shipping_cost']) >= MODULE_ORDER_TOTAL_SHIPPING_FREE_SHIPPING_OVER) ) {
	  $order->info['shipping_method'] = $this->title;
	  $order->info['total'] -= $order->info['shipping_cost'];
	  $order->info['shipping_cost'] = 0;
	}
  }

  $module = substr($GLOBALS['shipping']['id'], 0, strpos($GLOBALS['shipping']['id'], '_'));

  if (tep_not_null($order->info['shipping_method'])) {
	if ($GLOBALS[$module]->tax_class > 0) {
	  $shipping_tax = tep_get_tax_rate($GLOBALS[$module]->tax_class, $order->delivery['country']['id'], $order->delivery['zone_id']);
	  $shipping_tax_description = tep_get_tax_description($GLOBALS[$module]->tax_class, $order->delivery['country']['id'], $order->delivery['zone_id']);

	  $order->info['tax'] += tep_calculate_tax($order->info['shipping_cost'], $shipping_tax);
	  $order->info['tax_groups']["$shipping_tax_description"] += tep_calculate_tax($order->info['shipping_cost'], $shipping_tax);
	  $order->info['total'] += tep_calculate_tax($order->info['shipping_cost'], $shipping_tax);

	  if (DISPLAY_PRICE_WITH_TAX == 'true') $order->info['shipping_cost'] += tep_calculate_tax($order->info['shipping_cost'], $shipping_tax);
	}

	$this->output[] = array('title' => $order->info['shipping_method'] . ':',
							'text' => $currencies->format($order->info['shipping_cost'], true, $order->info['currency'], $order->info['currency_value']),
							'value' => $order->info['shipping_cost']);
  } // MattMika - START - Add virtual shipping entry to orders_total table
  else {

	$this->output[] = array('title' => 'Shipping(Virtual)' . ':',
							'text' => '$0.00',
							'value' => '0.0000');
  } // MattMika - END - Add virtual shipping entry to orders_total table
}

 

This allows our fullfillment dept., at a quick glance, to see orders with virtual shipping and will tell them that there are vouchers to confirm payment on and release the amount for.

 

So based on this I dont need modify or split up the queries the OP was about and get all orders displayed in admin/orders.php

 

On a side note, I titled it Shipping(Virtual) because on admin/orders.php I strip the shipping type out of the parenthesis. For example.

 

United Parcel Service (XML) (1 pkg x 0 lbs total) (UPS Ground): becomes 'UPS Ground'

 

Shipping(Virtual) becomes 'Virtual'

[Guest]

you shouldn't do that. You could implement 2 queries for the admin\orders.php. The first to pull-in the order details and then the 2nd the order totals using the orders_id from the first query and ot_shipping as the class. If no rows are found you print the entry as N/A or something for the shipping column.

[rotaour]

you shouldn't do that.

 

Can you expand on that? Why should I not do that? I may very well be missing some danger, but I dont see what.

 

I'll think about scraping it and having two queries to build the orders_query results although I'm not sure exactly how to add the results of the second query to the $orders results and page split.

[Guest]

Can you expand on that? Why should I not do that? I may very well be missing some danger, but I dont see what.

 

I'll think about scraping it and having two queries to build the orders_query results although I'm not sure exactly how to add the results of the second query to the $orders results and page split.

 

No it won't affect anything. Your first query (you basically have it), will retrieve all orders based on a given status. That will go through the split, so basically the first query should be the original stock osc query.

 

The 2nd query will address the class of the order total table checking if it's ot_shipping. The 2nd query will be executed for each row returned by the 1st query, like

 

$check_query = tep_db_query(select * from orders_total where class='ot_shipping'" and orders_id=the_first_query_orders_id);

if( $shipping = tep_db_fetch_array($check_query) ) {

// Print the shipping details

} else {

// Print N/A

}

 

so you don't need to mess the order process part, adding useless rows to the dbase.

[rotaour]

No it won't affect anything. Your first query (you basically have it), will retrieve all orders based on a given status. That will go through the split, so basically the first query should be the original stock osc query.

 

The 2nd query will address the class of the order total table checking if it's ot_shipping. The 2nd query will be executed for each row returned by the 1st query, like

 

$check_query = tep_db_query(select * from orders_total where class='ot_shipping'" and orders_id=the_first_query_orders_id);

if( $shipping = tep_db_fetch_array($check_query) ) {

// Print the shipping details

} else {

// Print N/A

}

 

so you don't need to mess the order process part, adding useless rows to the dbase.

 

Ok, I see what you're saying.

 

However, adding that second query to be pulled each time an order result from $orders['array'] is displayed is going to add significant overhead to admin/orders.php. We have 33 stores(utilizing the same backend - Multi-Stores) with currently 12,302 shipped orders and several hundred of other status types. It seems unwise to use this method unless I can implement an if/else statement into the single query that says if ot_shipping is set display shipping method else display none. I should possibly archive older orders to reduce db size, but currently the system is running just fine on a dedicated server with a db approx 75mb.

 

On the other hand, adding one more row to table.orders_total for orders with only Sub-Total & Total entries is not a significant increase of inserts to table.orders_total, especially when you consider all orders(with shipping) have Sub-Total, Shipping, Total and possibly Discount Coupon or Gift Voucher entries.

 

However, I think I will change my addition to the code in inc/module/ot/ot_shipping.php though. Something like;

	  elseif (!tep_not_null($order->info['shipping_method'])) {

						if/foreach/something (code to check for virtual products only){ 
	$this->output[] = array('title' => 'Shipping(Virtual)' . ':',
							'text' => '$0.00',
							'value' => '0.0000');
					 }
  }

 

Thanks

[Guest]

However, adding that second query to be pulled each time an order result from $orders['array'] is displayed is going to add significant overhead to admin/orders.php. We have 33 stores(utilizing the same backend - Multi-Stores) with currently 12,302 shipped orders and several hundred of other status types. It seems unwise to use this method unless I can implement an if/else statement into the single query that says if ot_shipping is set display shipping method else display none. I should possibly archive older orders to reduce db size, but currently the system is running just fine on a dedicated server with a db approx 75mb.

I believe you don't understand the osC framework. The moment you're doing the whole lot with a single query, that moment you add the latency to your thousands of orders. The page splitter does the initial query so if each page displays 10 results the 2nd query is executed once for each row returned (ie for the 10 results). In contrast joining tables to identify the shipping details, adds the latency because the splitter in order to return the results for the count tries now all orders (the 12,302). The query that is passed to the splitter should be as simple as possible.

[Silverado05]

This is very interesting, is this a contribution or something you put together? I would like something like this as well for the Shipping Method used.

[rotaour]

I believe you don't understand the osC framework. The moment you're doing the whole lot with a single query, that moment you add the latency to your thousands of orders. The page splitter does the initial query so if each page displays 10 results the 2nd query is executed once for each row returned (ie for the 10 results). In contrast joining tables to identify the shipping details, adds the latency because the splitter in order to return the results for the count tries now all orders (the 12,302). The query that is passed to the splitter should be as simple as possible.

 

I understand SQL well enough to know that the more joins and selection criterion I use in a query the more overhead that query has. I'm not worried so much about the latency on this ONE page as it is currently being parsed in 1-3 secs, depending on the criterion of the search, with 85 users online.

 

My problem is with the inefficiency of running the SAME EXACT query 25 times to a page(our orders page displays 25 orders at a time) as this is contrary to everything I've read about decent coding(and sure this doesnt have to be followed explicitly, but when it can be it should be).

 

I implemented your solution just to test and there is no tangible difference in page parse time. The parse times vary the same amount depending on server load at any given time. The only real difference is my solution runs one query yours runs one query plus 25 for every page of orders viewed.

 

Different strokes for different folks I guess....

[Silverado05]

How did you get 25 orders to display on the page? I only get 10 but that is the one thing I have been trying to do is get more on one page.

 

I am very interested in what you are doing. I hope you do share how you came about putting all this together.

 

-Thanks

[Guest]

I implemented your solution just to test and there is no tangible difference in page parse time. The parse times vary the same amount depending on server load at any given time. The only real difference is my solution runs one query yours runs one query plus 25 for every page of orders viewed.

I am not sure where you read these things as I explain is best to use the 2 small indexed queries instead of a single complex one with joins that you mentioned. If you do the implementation properly you will see a huge difference.

 

Unless you want to add the extra row for the order_totals table. But then you end up with more rows in the same table.

 

So before claiming that you saw no difference you should post the php code that you used to do that.

[rotaour]

How did you get 25 orders to display on the page? I only get 10 but that is the one thing I have been trying to do is get more on one page.

 

I am very interested in what you are doing. I hope you do share how you came about putting all this together.

 

-Thanks

In the admin under Configuration-Maximum Values set 'Search Results' to 25. I started to write up directions on how to do this and was going to give directions on my method and Enigmas method and realized I need to write two sets of directions to be clear. I will try to do it soon and post here or a link to a contribution if I decide to make it one.

[Silverado05]

That would be great thank you!!!

 

 

Ah I tried the search results as you stated. It worked but I just realized this is for customers not orders. Look to display more the 10 orders on that page...

 

-Thanks though.

[rotaour]

I am not sure where you read these things as I explain is best to use the 2 small indexed queries instead of a single complex one with joins that you mentioned. If you do the implementation properly you will see a huge difference.

 

Unless you want to add the extra row for the order_totals table. But then you end up with more rows in the same table.

 

So before claiming that you saw no difference you should post the php code that you used to do that.

 

I double checked the code I implemented to use your recommended method and discovered I only pulled 'ot2.title as order_shipping,' from the queries and not 'left join " . TABLE_ORDERS_TOTAL . " ot2 on (o.orders_id = ot2.orders_id),' so it was the same query, thats why the parse times didnt differ, good call :).

 

I removed the join and did tests of 20 queries with each method.

 

My method:

Fastest: 2.420sec

Slowest: 4.809sec

Average: 3.17sec

 

Enigmas method:

Fastest: 1.400sec

Slowest: 2.640sec

Average: 1.59sec

 

Your method on average takes half the time mine does. So you're right, your method is faster. The main query is still joining 5 tables, but I guess joining the orders_total table twice was really slowing it down.

 

Thanks

[Guest]

I double checked the code I implemented to use your recommended method and discovered I only pulled 'ot2.title as order_shipping,' from the queries and not 'left join " . TABLE_ORDERS_TOTAL . " ot2 on (o.orders_id = ot2.orders_id),' so it was the same query, thats why the parse times didnt differ, good call :).

 

I removed the join and did tests of 20 queries with each method.

 

My method:

Fastest: 2.420sec

Slowest: 4.809sec

Average: 3.17sec

 

Enigmas method:

Fastest: 1.400sec

Slowest: 2.640sec

Average: 1.59sec

 

Your method on average takes half the time mine does. So you're right, your method is faster. The main query is still joining 5 tables, but I guess joining the orders_total table twice was really slowing it down.

 

Thanks

okay, regarding my recommendation I was doing exactly the same mistake many times with various queries (I was overlooking the join tables and was passing to the splitter complicated queries) It is very easy to overlook that as tables initially have very few rows so everything looks good. As time passes and more rows are added to these tables the join and join order of the tables becomes a major factor.

 

To get around (or at least to overcome the latency as much as possible) I utilize the advanced cache contribution.

http://www.oscommerce.com/community/contributions,2873

This can be deployed on the catalog and/or admin end and dramatically improves dbase performance as a cached query is typically a single row from the cache that can bring up all stored results. It is harder to utilize it on the admin end as you need to flush the cache when you insert/update a dbase entry and most scripts there do something like that.

There are other articles about it in this thread

http://www.oscommerce.com/forums/index.php?showtopic=119077

[rotaour]

If you wish to implement a shipping method field in the orders list table on admin/orders.php follow these directions:

 

In admin/includes/languages/english/orders.php AFTER

define('TABLE_HEADING_DATE_PURCHASED', 'Date Purchased');

ADD

define('TABLE_HEADING_SHIP_TYPE', 'Shipping');

 

In admin/orders.php AFTER

<td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_DATE_PURCHASED; ?></td>

ADD

<td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_SHIP_TYPE; ?></td>

 

AFTER;

<td class="dataTableContent" align="center"><?php echo tep_date_short($orders['date_purchased']); ?></td>

ADD

<?
	$orders_shipping_query_raw = "select title as order_shipping from " . TABLE_ORDERS_TOTAL . " where orders_id = " . $orders['orders_id'] . " and class = 'ot_shipping' ";	
	$orders_shipping_query = tep_db_query($orders_shipping_query_raw);
	while ($orders_shipping = tep_db_fetch_array($orders_shipping_query)) {
	$order_shipping=$orders_shipping['order_shipping'];
	  }
	  $start = strrpos($order_shipping, '(');
	$end = strrpos($order_shipping, ')');
	$span = ($end - $start);
	$order_shipping_method = (!empty($order_shipping) ? substr($order_shipping, ($start + 1), ($span - 1)) : "Virtual");
?>
			<td class="dataTableContent" align="center"><?php echo $order_shipping_method; ?></td>

 

If you prefer to have the entire shipping method name shown, ie. United Parcel Service (1 pkg x 0 lbs total) (UPS Ground): OR USPS (1 x 3lbs) (Express Mail):, remove the $start, $end, $span and $order_shipping_method lines and echo $order_shipping instead of $order_shipping_method in the table cell(This can make a mess with the presentation of the table though).

 

Thats it, pretty easy.

[Silverado05]

Sweet, thanks for sharing, worked like a charm! I added a column for Order Number on the table. What would I need to change in the php above to output the Order Number?

 

-Thanks

[rotaour]

Sweet, thanks for sharing, worked like a charm! I added a column for Order Number on the table. What would I need to change in the php above to output the Order Number?

 

-Thanks

 

NP. Just add the following in the appropriate place:

 

<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID', 'action')) . 'oID=' . $orders['orders_id'] . '&action=edit') . '">' . $orders['orders_id'] . '</a>'; ?></td>

 

You can click on the order number to edit it, a bit better than highlighting the order and clicking it again or clicking edit. I put mine between the CustomerID and the OrderTotal.

[Silverado05]

NP. Just add the following in the appropriate place:

 

<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID', 'action')) . 'oID=' . $orders['orders_id'] . '&action=edit') . '">' . $orders['orders_id'] . '</a>'; ?></td>

 

You can click on the order number to edit it, a bit better than highlighting the order and clicking it again or clicking edit. I put mine between the CustomerID and the OrderTotal.

 

 

Oh no, I have already added the header order number to table. That is what the code above does correct, or am I understanding it wrong? How would you go about displaying the order number itself in the column like you did in your screen shot from your first post?

 

 

-Thanks

[rotaour]

I finally got around to deprecating our printed gift certificates and implemented gift vouchers. I found a problem and have changed some code. The loop was using the previous records shipping_method in a list(ie. if a virtual ship_method wasnt first in the list it would take the ship_method of the previous order).

 

Here's the changes.

 

ADD:

$shipping_method_num=tep_db_num_rows($orders_shipping_query);

AFTER:

$orders_shipping_query = tep_db_query($orders_shipping_query_raw);

 

REPLACE:

		while ($orders_shipping = tep_db_fetch_array($orders_shipping_query)) {
	$order_shipping=$orders_shipping['order_shipping'];
	  }
	  $start = strrpos($order_shipping, '(');
	$end = strrpos($order_shipping, ')');
	$span = ($end - $start);
	$order_shipping_method = (!empty($order_shipping) ? substr($order_shipping, ($start + 1), ($span - 1)) : "Virtual");

 

WITH:

		  if ($shipping_method_num==0){
	  $order_shipping_method = "Virtual";
		  } else {
	$order_shipping_method_raw = tep_db_result($orders_shipping_query, 0, 0);
	$start = strrpos($order_shipping_method_raw, '(');
	$end = strrpos($order_shipping_method_raw, ')');
	$span = ($end - $start);
	$order_shipping_method = substr($order_shipping_method_raw, ($start + 1), ($span - 1));
	  }

 

Thats it...

[Silverado05]

Is this in relations to the CCGV(trad)? Could you elaborate on this change. Are you saying that if you only purchase a gift voucher it will not display virtual as shipping but the shipping of the last order?

 

If this is the case it doesn't do this on mine, at least not as of yet. What exactly is this code change suppose to do or fix?

 

-Thanks