Personalization question

  • Posts: 410
  • Thank you received: 15
5 years 5 months ago #298524

Hi, is it possible to retrieve information from the database as to which users have purchased a product. I want to retrieve their user_cms_id and other data.

Last edit: 5 years 5 months ago by neo191987.

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

  • Posts: 12953
  • Thank you received: 1778
5 years 5 months ago #298527

Hello,

Sure, the solution will be to directly use an SQL request on your code to directly retrieve the information you need.

Kind regards,
Mohamed Thelji.

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

  • Posts: 410
  • Thank you received: 15
5 years 5 months ago #298552

Thank you. Can there any guidelines to orient what to do?

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

  • Posts: 12953
  • Thank you received: 1778
5 years 5 months ago #298569

Hello,

Using that kind of SQL code will probably do the job :

SELECT DISTINCT o.order_user_id, u.user_cms_id FROM #__hikashop_order AS o
LEFT JOIN #__hikashop_order_product AS op ON o.order_id = op.order_id
LEFT JOIN #__hikashop_user AS u ON o.order_user_id = u.user_id
WHERE op.product_id = YOUR_PRODUCT_ID AND (o.order_status = 'confirmed' OR o.order_status = 'shipped')

You'll just have to replace YOUR_PRODUCT_ID by the product ID of the product you're interested in.

Kind regards,
Mohamed Thelji.

The following user(s) said Thank You: neo191987

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

  • Posts: 410
  • Thank you received: 15
5 years 5 months ago #298610

I was able to show ID, but it only shows to one user who has logged in. I want to retrieve all IDs from users who have purchased this product.

<?php
$user = JFactory::getUser();
$userId = $user->get('id');
$db = JFactory::getDbo();
$query = "SELECT op.product_id FROM #__hikashop_user AS u LEFT JOIN #__hikashop_order AS o ON u.user_id=o.order_user_id LEFT JOIN #__hikashop_order_product AS op ON o.order_id=op.order_id WHERE u.user_cms_id=".(int)$userId." AND op.product_id = product_id AND (o.order_status = 'confirmed' OR o.order_status = 'shipped')";
$db->setQuery($query);
$product_ids = $db->loadResult();

$user_id = hikashop_loadUser();
$db = JFactory::getDbo();
$query = 'SELECT `order_id` FROM ' .  hikashop_table('order') . ' WHERE `order_user_id` = '. $user_id .' AND `order_type` = \'sale\' ORDER BY `order_created` DESC';
$db->setQuery($query);
$order_ids = $db->loadColumn();

$orders = array();
$product_ids = array();

foreach($order_ids as $order_id) {
  $orderClass = hikashop_get('class.order');
  $orders[$order_id] = $orderClass->loadFullOrder($order_id, true);
  foreach($orders[$order_id]->products as $product) {
    $product_ids[] = (int)$product->product_id;
  }
}
foreach ($orders as $key => $order) {
  $userClass = hikashop_get('class.user');
  $hika_user = $userClass->get($order->order_user_id);
  $id = $hika_user->user_cms_id;
  $user = ES::user($id);
  var_dump($id);
}
?>

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

  • Posts: 4491
  • Thank you received: 609
  • MODERATOR
5 years 5 months ago #298619

Hello,

First, if I look your sql request you only get the product id, so I don't really understand why you don't use Mohmed request in his previous post ?
Second step, important to note for what you want achieved, if I understand you well you want have all user cms id, or let's say it differently joomla user id, but customers in guest mode haven't any joomla user id...

The Mohamed solution will give what you requested but just not for guest customer.
If that fit your needs you have here the solution, if you want more, I suggest that you precise your need and the use context.

regards

Last edit: 5 years 5 months ago by Philip.
The following user(s) said Thank You: neo191987

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

  • Posts: 410
  • Thank you received: 15
5 years 5 months ago #298649

Thank you very much. Everything is working properly. The only thing I'm wrong with is loadResult() which it should be loadColumn()

This is the code I made

<?php
$product_id = (int)$this->element->product_id;
$db = JFactory::getDbo();
$query = "SELECT DISTINCT o.order_user_id, u.user_cms_id FROM #__hikashop_order AS o
LEFT JOIN #__hikashop_order_product AS op ON o.order_id = op.order_id
LEFT JOIN #__hikashop_user AS u ON o.order_user_id = u.user_id
WHERE op.product_id = '.$product_id.' AND (o.order_status = 'confirmed' OR o.order_status = 'shipped')";
$db->setQuery($query);
$users_ids = $db->loadColumn();
foreach ($users_ids as $users) {
	$userClass = hikashop_get('class.user');
	$users_id = $userClass->get($users);
	$id = $users_id->user_cms_id;
	$user = ES::user($id);
	var_dump($user->name);
}
?>

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

  • Posts: 410
  • Thank you received: 15
5 years 5 months ago #298721

Hi, this code works only for products, but I will mainly use variants. Is there any solution because I only allow one ID.

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

  • Posts: 81379
  • Thank you received: 13037
  • MODERATOR
5 years 5 months ago #298732

Hi,

This code will work for variants too. You just need to give the product_id of the variant in $this->element->product_id.
If however, you want to give a product_id of a product so that it takes into account all its variants, then you need to modify the query like that:

$query = "SELECT DISTINCT o.order_user_id, u.user_cms_id FROM #__hikashop_order AS o
LEFT JOIN #__hikashop_order_product AS op ON o.order_id = op.order_id
LEFT JOIN #__hikashop_product AS p ON op.product_id = p.product_id
LEFT JOIN #__hikashop_user AS u ON o.order_user_id = u.user_id
WHERE (p.product_parent_id = '.$product_id.' OR p.product_id = '.$product_id.' ) AND (o.order_status = 'confirmed' OR o.order_status = 'shipped')";

The following user(s) said Thank You: neo191987

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

  • Posts: 410
  • Thank you received: 15
5 years 5 months ago #298742

Thank you. I found it yesterday after a thorough examination of the code. It's really not complicated if you find the right code. :)

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

Time to create page: 0.083 seconds
Powered by Kunena Forum