Personalization question

neo191987
Topic Author
Offline

8 years 3 days ago - 8 years 11 hours ago #298524 by neo191987

Personalization question was created by neo191987

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: 8 years 11 hours ago by neo191987.

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

Mohamed Thelji
Offline

8 years 2 days ago #298527 by Mohamed Thelji

Replied by Mohamed Thelji on topic Personalization question

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.

neo191987
Topic Author
Offline

8 years 2 days ago #298552 by neo191987

Replied by neo191987 on topic Personalization question

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

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

Mohamed Thelji
Offline

8 years 2 days ago #298569 by Mohamed Thelji

Replied by Mohamed Thelji on topic Personalization question

Hello,

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

Code:

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.

neo191987
Topic Author
Offline

8 years 1 day ago #298610 by neo191987

Replied by neo191987 on topic Personalization question

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.

Code:

<?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.

Philip
Offline

8 years 17 hours ago - 8 years 1 day ago #298619 by Philip

Replied by Philip on topic Personalization question

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: 8 years 1 day ago by Philip.

The following user(s) said Thank You: neo191987

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

neo191987
Topic Author
Offline

8 years 11 hours ago #298649 by neo191987

Replied by neo191987 on topic Personalization question

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

Code:

<?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.

neo191987
Topic Author
Offline

7 years 11 months ago #298721 by neo191987

Replied by neo191987 on topic Personalization question

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.

nicolas
Offline

7 years 11 months ago #298732 by nicolas

Replied by nicolas on topic Personalization question

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:

Code:

$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.

neo191987
Topic Author
Offline

7 years 11 months ago #298742 by neo191987

Replied by neo191987 on topic Personalization question

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.272 seconds