Modify default SQL in Admin Orders

[sini]

Hi,

 

 I would like to change default SQL in Admin Orders like add NOT LIKE codition -> I want show all orders without orders from customer "P. Guest". So i try change line 75 in /controllers/admin/AdminOrderContoller.php like this:

 

LEFT JOIN `'._DB_PREFIX_.'order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = '.(int)$this->context->language->id.') WHERE 1 HAVING `customer` NOT LIKE '%P. Guest%'';

 

But it gives me error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 1 ORDER BY a.`id_order` DESC LIMIT 0, 50' at line 21

 

Could you help me how to change default SQL?

 

Thank you in advance and sorry for my English

[sini]

No one dealt with a change the default SQL in orders in admin?

[tuk66]

The query is created dynamically according to the fields filled in the list. I'm not sure you can easy improve the query. Try to limit customers in this code:

 

$this->_join = '
      LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer` AND c.`id_customer` NOT LIKE '%P. Guest%')

[sini]

Thanks for reply tuk66, i try it but it gives me error "Table 'c56kopiluwakdb2.ps_ps_address' doesn't exist". I really don't understand why adding this condition change table name, but it does.

 

if the filter has a checkbox that turns negation search it would solve everything, but unfortunately doesn't ...

[tuk66]

It looks like doubled prefix. Probably my fault. Try

 

$this->_join = '
      LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer` AND c.`id_customer` NOT LIKE \'%P. Guest%\')

[sini]

Yes, error is gone :-) but nothing happend. If i change name P. Guest to customer id 4485, in order view i see same orders only in orders from P. Guest aren't names :-)