Filter on product in acymailing sql error

  • Posts: 97
  • Thank you received: 0
  • Hikashop Business
7 years 1 month ago #263739

-- url of the page with the problem -- : geertswarenhuis.nl
-- HikaShop version -- : 3.0.0
-- Joomla version -- : 3.6.5
-- PHP version -- : 5
-- Browser(s) name and version -- : chrome
-- Error-message(debug-mod must be tuned on) -- : An error has occurred.
1054 Unknown column 'a.product_parent_id' in 'on clause' SQL=SELECT DISTINCT b.user_email FROM j17_hikashop_order_product AS a LEFT JOIN j17_hikashop_order AS c ON a.order_id = c.order_id LEFT JOIN j17_hikashop_user AS b on c.order_user_id = b.user_id LEFT JOIN j17_hikashop_product_category AS hkpc ON (a.product_id = hkpc.product_id) OR (a.product_parent_id > 0 AND a.product_parent_id = hkpc.product_id) WHERE c.order_status IN ('confirmed','shipped') AND hkpc.category_id = 67

Hi,

I get this error if I try to do the action in the screenshot. Make a list based on whom bought a product from a specific product category

An error has occurred.
1054 Unknown column 'a.product_parent_id' in 'on clause' SQL=SELECT DISTINCT b.user_email FROM j17_hikashop_order_product AS a LEFT JOIN j17_hikashop_order AS c ON a.order_id = c.order_id LEFT JOIN j17_hikashop_user AS b on c.order_user_id = b.user_id LEFT JOIN j17_hikashop_product_category AS hkpc ON (a.product_id = hkpc.product_id) OR (a.product_parent_id > 0 AND a.product_parent_id = hkpc.product_id) WHERE c.order_status IN ('confirmed','shipped') AND hkpc.category_id = 67


Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 81513
  • Thank you received: 13068
  • MODERATOR
7 years 1 month ago #263743

Hi,

Change the code:
$myquery .= ' LEFT JOIN #__hikashop_product_category AS hkpc ON (a.product_id = hkpc.product_id) OR (a.product_parent_id > 0 AND a.product_parent_id = hkpc.product_id) ';

to:
$myquery .= ' LEFT JOIN #__hikashop_product as hkp ON a.product_id = hkp.product_id LEFT JOIN #__hikashop_product_category AS hkpc ON (a.product_id = hkpc.product_id) OR (hkp.product_parent_id > 0 AND hkp.product_parent_id = hkpc.product_id) ';

in the file plugins/acymailing/hikashop/hikashop.php and that should fix the problem.

Please Log in or Create an account to join the conversation.

  • Posts: 97
  • Thank you received: 0
  • Hikashop Business
7 years 1 month ago #263920

Hi Nicolas,

thanks; solved the error, but the selection numbers of customers doesn't match the exact numbers of total customers.

We have 3114 customers and with the first filter I got 2622



When we filter on customers who at least bought one product from any category we get 82, but we have a lot more orders :-)


What I want is to filter the following;
From all orders, filter those who bought a product of 'Marjolein Bastin Sketch of Nature' category.
But it is way more than 4.
This one:


Could it be that productcategories changed in the years and are 'lost'? Or that it doesn't show all the categories?
How can we fix this?

Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 81513
  • Thank you received: 13068
  • MODERATOR
7 years 1 month ago #263946

Hi,

I think that your results are like that because the guest users are not synchronized with AcyMailing on your website.
Run that query in your PHPMyAdmin and that should help:
INSERT IGNORE INTO `#__acymailing_subscriber` (`email`,`userid`,`created`) SELECT `user_email`, `user_cms_id`,`user_created` FROM `#__hikashop_user`
(where you replace #__ with the table prefix of your website)

Please Log in or Create an account to join the conversation.

  • Posts: 97
  • Thank you received: 0
  • Hikashop Business
7 years 1 month ago #264008

Hi Nicolas,

That query added 14 people to acymailing, so that is not the 'problem'.

When I do this:



I got 89 hikashop users, but we have many. We have 3100 orders...

We want to use the combination of hikshop acymailing to send targeted mails to customers who bought product form specific categories, so we can upsell more.
Like to hear from you.

Attachments:

Please Log in or Create an account to join the conversation.

  • Posts: 81513
  • Thank you received: 13068
  • MODERATOR
7 years 1 month ago #264011

Hi,

Then I don't know. On my end I have a credible number of users found on my test website and the MySQL used looks correct too.
We would need a backend access and a FTP access in order to look deeper into that. An access to the PHPMyAdmin would be great too in order to look at the raw data and test the MySQL queries.
Please provide that along with a link to this thread via our contact form and we can have a look:
www.hikashop.com/support/contact-us.html

Please Log in or Create an account to join the conversation.

  • Posts: 81513
  • Thank you received: 13068
  • MODERATOR
7 years 1 month ago #264379

Hi,

Thanks for the accesses.
The issue doesn't come from a bug in HikaShop or the integration plugin with AcyMailing as far as I can see.
The problem is with your order statuses.
If you look at them, you can see this: monosnap.com/file/SxbpbpzIUhIKiR1GVmFFrSMfdTOdvm
This means that they aren't configured at all. This should never happen.
Also, if I use the order status filter on the orders listing in your backend, there is only one order with the status "confirmed" found. That's because all the others had the status translation set in them.
And that was because "bevestidg" was selected instead of "confirmed" in the "verified status" setting of your payment method (I don't know how that's possible?).
I've changed the verified status to "confirmed" in your payment method so future confirmed orders should have a correct order status.
What you can do is also update the order_status column of the table hikashop_order with the correct english namekey of the order statuses and not their translation. You can do that with either a mass action (a filter on order_status and an action on that same column) or directly in your PHPMyAdmin.
Also, I would recommend to properly configure your order statuses listing, with something like we have by default:
monosnap.com/file/PSWdVBWNSGGrU7CE0CXPTrOTQQEiW9
The AcyMailing filters should then produce much more realistic values.

Please Log in or Create an account to join the conversation.

  • Posts: 97
  • Thank you received: 0
  • Hikashop Business
7 years 1 month ago #264529

Thanks for the response, that helped a lot!

I think i know where the 'error' or misinterpretation on our side is coming from.

The expectation on our side is that when filtering customers who bought from any or a selected category we would also see/receive the customer who's orders are shipped. Currently we only see the customers who's order is confirmed.

Is this fixable?

Kind regards

Please Log in or Create an account to join the conversation.

  • Posts: 81513
  • Thank you received: 13068
  • MODERATOR
7 years 1 month ago #264535

Hi,

With the "hikashop orders" filter, you can select the status you want for your filter.
And for the "hikashop customers" filter, it uses the order statuses that are enabled in the "invoice" column of your menu System>Order statuses.
This however requires that you enable some statuses there, which wasn't the case, and that the order_status column in the hikashop_order table contains the namekey of these order statuses ( confirmed or shipped for example), and not their translation.
So as long as your store is properly configured and that the statuses of your orders in the database have the correct values, it should work like you want.

Please Log in or Create an account to join the conversation.

Time to create page: 0.090 seconds
Powered by Kunena Forum