Dublicate user_id

  • Posts: 54
  • Thank you received: 0
7 years 1 week ago #282439

I notice that there are several dublicated user_id presumably because the site was imported from Virtuemart this doesn't cause me a problem normally however I'm using this code for some custom accounting software

SELECT
jos_hikashop_order.order_created,
jos_hikashop_user.user_sage_id,
jos_hikashop_user.user_id,
jos_hikashop_user.user_email,
jos_hikashop_order.order_number,
jos_hikashop_order.order_id,
jos_hikashop_order.order_full_price,
jos_hikashop_order.order_status,
CONCAT(address_firstname,' ',address_lastname) AS `name`,
jos_hikashop_payment.payment_name,
jos_hikashop_orderstatus.grp
FROM
jos_hikashop_order
INNER JOIN jos_hikashop_user ON jos_hikashop_user.user_id = jos_hikashop_order.order_user_id
INNER JOIN jos_hikashop_payment ON jos_hikashop_payment.payment_type = jos_hikashop_order.order_payment_method
INNER JOIN jos_hikashop_orderstatus ON jos_hikashop_orderstatus.orderstatus_name = jos_hikashop_order.order_status
INNER JOIN jos_hikashop_address ON jos_hikashop_address.address_id = jos_hikashop_order.order_shipping_address_id

I sometimes see the incorrect user - how does HIKASHOP query look so that the dublicate user_id in the address table don't matter. I relaise that that one user may change his address - but the user ids point to different users altogether

Last edit: 7 years 1 week ago by eileensemporium. Reason: typo city

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

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
7 years 1 week ago #282441

Hi,

It's not possible to have different user_id for different users in HikaShop.
The reason is simple: the user_id column of the hikashop_user table is the primary key and thus all its entries have to be unique. That is enforced directly at the database level.
So unless you've modified the database structure, which you should do, it's not possible.

Now regarding the data consistency between the addresses, the users and the orders, it's possible there is a mismatch if you've imported from VM, especially with old versions of HikaShop where there weren't enough failsafes for strange cases of VM data structure.
But that shouldn't impact your results of that query for new orders of your HikaShop shop since the user_id from hikashop_user has to be the user who purchased the order and whose id is in order_user_id and the addreses have to be the addresses selected during the checkout.

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

  • Posts: 54
  • Thank you received: 0
7 years 1 week ago #282541

Thanks the problem seems to be if old VM users re-order.
This is a case where I have a duplicated user (708)

SELECT
jos_hikashop_order.order_user_id,
jos_hikashop_order.order_id,
jos_hikashop_address.address_lastname
FROM
jos_hikashop_order
INNER JOIN jos_hikashop_address ON jos_hikashop_address.address_id = jos_hikashop_order.order_shipping_address_id
WHERE jos_hikashop_order.order_user_id = 708
order_user_id order_id address_lastname
708 9333 Moorhouse
708 36066 Partners
708 36067 Moorhouse

So we have three orders all pointing to user_id 708 which is incorrrect

How does Hikashop deal with this 'cause it works ok

user_id column of the hikashop_user is ok there are not any duplicates

SELECT
jos_hikashop_user.user_id,
jos_hikashop_user.user_cms_id
FROM
jos_hikashop_user
WHERE user_id = 708
Correctly give one line/user

Last edit: 7 years 1 week ago by eileensemporium.

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

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
7 years 1 week ago #282568

Hi,

So then, supposing that the address is attached to the correct user, it's easy to fix that. Run that MySQL query:
UPDATE
jos_hikashop_order,
jos_hikashop_address
SET jos_hikashop_order.order_user_id = jos_hikashop_address.user_id
jos_hikashop_order
WHERE jos_hikashop_address.address_id = jos_hikashop_order.order_shipping_address_id

The query will take the user_id from the shipping address of the order to update it in the order so that it is consistent.
Now if the user_id is also wrong in the address, then I don't see a solution to fix that automatically since you can't know which order/address is for which user. You just know that orders with different addresses are assigned to the same user.

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

  • Posts: 54
  • Thank you received: 0
7 years 1 week ago #282573

Thanks, but how does Hikashop cope with the the duplicates, what query does it use to build the order page up e.g.

/administrator/index.php?option=com_hikashop&ctrl=order&task=edit&cid[]=36171

As the data is always correct in the Hikashop page above ie correct user against the order and I can see my extra user fields too

The user id is not always correct in the address table for instance I have 2 user id 708 (and many others) in the address table

Last edit: 7 years 1 week ago by eileensemporium.

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

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
7 years 1 week ago #282577

Hi,

HikaShop uses the order_user_id there to load the user data of the order. So if it's correct on that page the id should also be correct in the database.

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

Time to create page: 0.064 seconds
Powered by Kunena Forum