How to list the serial's order number and attached product

  • Posts: 5
  • Thank you received: 1
2 years 8 months ago #340281

-- HikaSerial version -- : 4.0.1

We are using hikaserial to sell raffle tickets for different raffles that we will have (50+ raffles). In the listing and in the export, it only says the order number, no the product that the serial is attached to. How can I get that in an exportable list so that we can differentiate the serials according to the product raffle?
Thanks.

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

  • Posts: 26158
  • Thank you received: 4028
  • MODERATOR
2 years 8 months ago #340287

Hello,

The table "order_product" is not part of the serial export for performance reasons.
But we can see to add a setting in the export window in order to enable the inclusion of that database table, which will allow you to access some elements such as the "product_id" or "product_code / product_name".
Since it require the modification of a core file (a view.html.php), it's not something which can be done using just view overrides (like it's possible if you want to select more columns in the available tables during the export).

Regards,

Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.

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

  • Posts: 5
  • Thank you received: 1
2 years 8 months ago #340289

Thanks for the quick answer. If you modify the setting, please let me know. Would there be a way to export then the table that unites order_product with serails, and then we can do the join separately on a different system?

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

  • Posts: 26158
  • Thank you received: 4028
  • MODERATOR
2 years 8 months ago #340300

Hello,

The idea is to complete the export of serials with data coming from the "order_product" table, like you can have columns for the order id (or order number), it would allow you to have the order product name (or order product code).

Regards,

Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.

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

  • Posts: 5
  • Thank you received: 1
2 years 8 months ago #340302

It would be something similar to this...
SELECT `db_hikaserial_serial`.`serial_id`,`db_hikaserial_serial`.`serial_data`, `db_hikashop_product`.`product_name`, `db_hikaserial_serial`.`serial_order_product_id`, `db_hikashop_user`.`user_name`, `db_hikaserial_serial`.`serial_user_id` FROM `db_hikashop_product` , `db_hikaserial_serial`, `db_hikashop_order_product`,`db_hikashop_user` where `db_hikaserial_serial`.`serial_order_product_id` = `db_hikashop_order_product`.`order_product_id` and `db_hikashop_order_product`.`product_id` = `db_hikashop_product`.`product_id` and `db_hikaserial_serial`.`serial_user_id` = `db_hikashop_user`.`user_id` order by `db_hikashop_product`.`product_name`, `db_hikashop_user`.`user_name` ;

That would give me the info I need. I can now get it from phpadmin, but I think it's a feature hikaserial should have...

Thanks.

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

  • Posts: 26158
  • Thank you received: 4028
  • MODERATOR
2 years 8 months ago #340337

Hello,

We prefer using "INNER JOIN" (and "LEFT JOIN") for database performances.

SELECT *
FROM db_hikaserial_serial AS serial 
INNER JOIN db_hikaserial_pack AS pack ON serial.serial_pack_id = pack.pack_id
LEFT JOIN db_hikashop_user AS user ON serial.serial_user_id = user.user_id
LEFT JOIN db_hikashop_order AS shop_order ON serial.serial_order_id = shop_order.order_id
LEFT JOIN db_hikashop_order_product AS shop_order_product ON serial.serial_order_product_id = shop_order_product.order_product_id AND shop_order_product.order_id = shop_order.order_id

Regards,

Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.
Last edit: 2 years 8 months ago by Jerome.

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

Moderators: Obsidev
Time to create page: 0.066 seconds
Powered by Kunena Forum