0

I've recently upgraded an ee2.5.3 site build up to ee2.11.9 (ready for further EE3.5x upgrade).

The site also has an existing CartThrob 2.5 installation upgraded to 2.7.1 along with an existing custom plugin called: pi.cc_marketplace.php.

When the plugin runs (ie. the plugin activates when a site user has products in the site basket etc) I'm getting the following database error (from the custom plugin code) ie.

Error Number: 1052 Column 'entry_date' in where clause is ambiguous

select sum(quantity) as quantity_available from ( select -coalesce(sum(quantity),0) as quantity from exp_channel_titles as order_titles join exp_cartthrob_order_items as order_items on sm.order_items.order_id = sm.order_titles.entry_id where status = "Processing" and channel_id = 6 and entry_date > unix_timestamp(date_sub(now(), interval 30 minute)) and sm.order_items.entry_id = 31500 union select field_id_369 as quantity from exp_channel_data where entry_id = 31500 ) as quantity

Filename: third_party/cc_marketplace/pi.cc_marketplace.php

Line Number: 93

For clarity I have provided the actual PHP class (with the problematic SQL query code) from the pi.cc_marketplace.php plugin as per below:

public function _get_quantity_available($entry_id){
        $order_id = $this->EE->cartthrob->cart->order("order_id");
        $order_id_sql = "";
        if (isset($order_id) && $order_id != ""){
            // In case the user goes back to the checkout process after leaving for the gateway
            $order_id_sql = " and order_titles.entry_id != $order_id";
        }
        $field_ids = $this->get_channel_field_ids();
        $sql = <<<EOF
    select sum(quantity) as quantity_available
    from (
        select
            -coalesce(sum(quantity),0) as quantity
        from exp_channel_titles as order_titles
        join exp_cartthrob_order_items as order_items on order_items.order_id = order_titles.entry_id
        where
        status = "Processing"
        and channel_id = 6
        and entry_date > unix_timestamp(date_sub(now(), interval 30 minute))
        and order_items.entry_id = $entry_id
        $order_id_sql
    union
        select
            field_id_${field_ids["cc_mp_product_quantity_available"]} as quantity
        from exp_channel_data
        where entry_id = $entry_id
    ) as quantity
EOF;
        $res = $this->EE->db->query($sql);
        $res = $res->row();
        return $res->quantity_available;
    }

I've seen some similar posts on here regarding the 'Error Number: 1052 Column 'xxx' in where clause is ambiguous' error but the example issues don't quite match the issue I have here.

Can anyone help with the above?

Many thanks in advance.

Regards

Karl

1

It is a little bit weird because only exp_channel_titles should have entry_date column, but try to change your SQL code this way (entry_date replace with order_titles.entry_date):

select sum(quantity) as quantity_available
from (
    select
        -coalesce(sum(quantity),0) as quantity
    from exp_channel_titles as order_titles
    join exp_cartthrob_order_items as order_items on order_items.order_id = order_titles.entry_id
    where
    status = "Processing"
    and channel_id = 6
    and order_titles.entry_date > unix_timestamp(date_sub(now(), interval 30 minute))
    and order_items.entry_id = $entry_id
    $order_id_sql
union
    select
        field_id_${field_ids["cc_mp_product_quantity_available"]} as quantity
    from exp_channel_data
    where entry_id = $entry_id
) as quantity

EOF;

| improve this answer | |
  • Hi Max, the order_title prefix (ie. order_titles.entry_date) did the trick! Funny enough the site in question above uses MX Mobile Device Detect! Plus I was literally about to watch your EEConf 2019 Speaker interview @ youtube.com/watch?v=pH0IDwH4gbs . Many thanks for the feedback fix and good luck with your slot at the show! :-) – Karl Bowers Sep 13 '19 at 21:21
  • Glad to hear it ) BTW new version Mobile Detect will be released this week. In case if this answer helps you - could you please mark it as the right answer (it helps to keep SE healthy)? Thanks! – Max Lazar Sep 16 '19 at 12:13
  • Hi Max, sorry yes I've marked the answer as accepted (sorry I totally missed this and thought I just neeed to click on the upvote etc). Regarding Mobile Detect, yes an upgrade on this will be required for this site so I'll keep a look out for this when it's released! Many thanks once again for helping me out on the above. Much appreciated :-) – Karl Bowers Sep 16 '19 at 14:09

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.