Column 'quantity' in where clause is ambiguous

[OpenCart City]

Hello,

We are trying to create controller in admin side and created successfully. We have added below code to get the product fields:

$this->_select .= 'sa.quantity';

$this->_join = 'LEFT JOIN ' . _DB_PREFIX_ . 'stock_available sa ON (a.id_product = sa.id_product)';

$this->fields_list = array(
  'id_product' => array(
    'title' => $this->l('PrestaShop Product ID'),
  ),
  'quantity' => array(
      'title' => $this->l('Quantity')
  ),
);

But when we try to filter by quantity then getting the error "Column 'quantity' in where clause is ambiguous".

We know the reason of this error. The reason is quantity field is present in both product and  stock_available tables.

So please let us know the solution.

Thanks!

[PhpMadman]

If i remember correctly it is because your fields_list asks for quantity, and as you said, it matches both fields

You can either change it to a.quantity or sa.quantity

Or you can edit your select _select to 'sa.quantity AS qty' and change fields_list from quantity to qty. Assuming you want the quantity from stock_available

[OpenCart City]

Hello,

Thank you for your response. We have tried both the solutions but didn't worked for us.

1. When we change the quantity in $this->fields_list  as  a.quantity or sa.quantity, it is not displaying any quantity. We are getting only -- in this column.

2. When we add sa.quantity AS qty and change fields_list from quantity to qty it is giving the error "Unknown column 'qty' in 'where clause'".

So please check and provide some solution.

Thanks!

[OpenCart City]

Hello,

Is there any solution?

Thanks!

[OpenCart City]

Hello,

Is there anyone who can help us to solve the issue?

Thanks!

[Pete78]

On 12/30/2020 at 6:42 AM, OpenCart City said:

On 1/31/2021 at 7:43 AM, OpenCart City said:

Hello,

Is there anyone who can help us to solve the issue?

Thanks!

 

$this->_select .= 'sa.quantity as qty';



$this->fields_list = array(
...
  'qty' => array(
      'title' => $this->l('Quantity'),

      'filter_key' => 'a!quantity'
  ),

...

 

should help...