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

Question

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

Answer 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;