0

This question came out of discussion of a way to speed up cartthrob order/order items output.

Using only a php solution, is it possible to display all cartthrob order item info? The goal is very fast output with multiple orders and multiple items per order. What is the fastest method to use that would result in the least amount of queries or server load.

1

There are many ways to go about it, but I find that in many situations, bypassing EE tags in favor of exp:query is much faster for data output. However, in this case, I needed a pure php solution and as such, turned to leveraging the EE DB class.

Here, I'm going to walk through a quick example of using the DB class as it relates to cartthrob - specifically, the cartthrob order_items tag. I am using EE3 for this example. I am simply going to post the php necessary to get into the order_items info and leave the processing and display of that data up to you.

<?php
    // order channel is 38
    // fields - specify which fields you need or use *
    // order_items = get from cartthrob_order_items.extra
    // status is ready

    // fetch the orders
    $readyorders = ee()->db->select('t.entry_id, t.title, extra')
    ->from('channel_titles t')
    ->join('cartthrob_order_items ct', 't.entry_id = ct.order_id')
    ->where(array(
          't.channel_id' => 38,
          't.status' => 'ready'
    ))
    ->order_by('t.entry_id', 'asc', 't.entry_date','asc')
    ->get();

In the above code, I've set my order channel as channel 38, and I'll be pulling the title, entry_id, and the CT order_items item_options field(via its own table - the extra field with a join) - also, I am limiting my data to only orders that have a status of 'ready' and then ordering by entry_id and date.

Please note that if you need to use other fields that are in your order channel data, you'll probably need to add another join to pull the channel_data table in at the same time. In this case I am only concerned with order_items so I only need to pull that table in.

Cartthrob stores order_items options as serialized base64 encoded data in its own table (exp_cartthrob_order_items) inside a field called extra. Data is stored with an order_id field that contains the entry_id of the original order data which is why I used ct.order_id to match up in the query join. At any rate, once you pull the order_items field data, you'll need to unserialize and decode it.

Note: if you do not use item_options for your products, you can simply pull the title field from the cartthrob_order_items table. Furthermore, you will not need to use the unserialize or base64 decode functions as the item titles are stored as plain text.

// use foreach to work with the results - returning an array
foreach($readyorders->result_array()as $row){
    //deserialize and decode the CT order_items field
    $items = array(unserialize(base64_decode($row['extra'])));

    //now lets run through the order_items as an array
    foreach($items as $item){

    // inside this foreach loop, you can apply whatever logic you need to pick up specific data.
    // Each field in the order_items array is available as $item['my_field_name']

    //for example, use the entry_id from the $row array with a value from the $item array

    echo $row['entry_id'].'::'.$item['price'];

    }
 }

In my case, I have taken the above code and I use some logic to find all orders that have a delivery date (with a delivery_date field in the order_items) and I compare it with todays date. For orders that match, I add them to an update array. I then take my new update array and use db->update to auto change the statuses and make changes to the order info as needed. The nice thing about this is once the logic is setup, you can then fire off jobs with cron to auto update entries based on data that's in the the order_items fields.

I hope the above methods are easy to understand and helpful to others.

0

Interesting and thanks for posting this. I'm confused as it why you are using the extra column? I just grab the title column.

For my testing I used some Composer components and twig. It loads in seconds:

    $getorders = "SELECT
        ct.entry_id as entry_id,
        ct.title as order_id,
        cd.field_id_27 as order_total,
        cd.field_id_34 as order_location,
        cd.field_id_36 as order_cust_name,
        cd.field_id_37 as order_phone,
        cd.field_id_103 as order_mob,
        cd.field_id_60 as order_instructions, 
        FROM exp_channel_data cd 
        LEFT JOIN exp_channel_titles ct
        ON cd.entry_id=ct.entry_id
        where ct.status = 'xeroed'";

        //get the orders
        $orders = Capsule::select($getorders, array(1));

        //combine the order items with the orders
        foreach ($orders as $order) {
        $items = Capsule::select('select exp_cartthrob_order_items.title, exp_cartthrob_order_items.quantity from exp_cartthrob_order_items Where exp_cartthrob_order_items.order_id ='. $order->entry_id);
        $boo_orders [] = ['order_cust_name'=>$order->order_cust_name, 'order_id'=>$order->order_id, 'items'=> $items
        ];}

   // build twig
    echo $twig->render('boo.tpl', array(
    'orders' => $boo_orders
    ));
  • very nice job - and using composer components is a great way to go too. I am using the extra field because I needed to pull data from order_items item_options. In my case each item has its own ship date based on the time of the order (custom apparel). Otherwise, I could just pull the title as you mentioned. – pixldev Jul 6 '17 at 1:45
  • So what is with the 'extra' column? what's it there for? And are you in Japan? – Kokako Jul 6 '17 at 1:48
  • Extra contains the Items item_options. If you have items that use the item_options - for example - T-shirts with size (S, M, L, XL) and color (red, green, blue) - usually those options are set with an item_option:option_name tag and they are stored in the Extra field. I guess its like matrix or grid - but specific to CT items. Since my sites are fully customized products, I have to store what the customer chooses in this way and use on_the_fly products where I calculate pricing via other methods. Yes, I'm in Japan. – pixldev Jul 6 '17 at 1:58
  • oh i should have known! sorry to ask a private question about where you live but I saw your profile and saw .jp. I'm very interested in Japan in general and if you are from there your English is very good! – Kokako Jul 6 '17 at 2:16
  • Also as a follow up: I have another case where it was too much of a flight to get EE to do want was needed, and I used the same Composer components to build the functionality and loaded it in EE by turning on php for a template . This was using the Laravel DB component and twig and it works very well. If someone has legacy code that they need to run inside of EE, it's a very quick way to go. – Kokako Jul 6 '17 at 2:33

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.