Page 1 of 1

Search by Product Attributes Help

Posted: Sat May 19, 2012 7:59 pm
by aaron1988
Hi Opencart,

wondering if there is an easy solution in 1.5.1.3 and 1.5.2.1 on how to Search by Product Attributes please as this is something i am seeing alot and no1 seems to be integrating it hope some1 can help me.

Kind Regards,
Aaron

Re: Search by Product Attributes Help

Posted: Mon May 21, 2012 11:34 pm
by florinsith
Dont know if I get you correctly. The below code will return in search , products that have the atributes values like keyword.
Example: In a default opencart install, the demo 'Macbook' product has an atribute group called memory, atribute name: test1 and the atribute value: 8gb . So if you search for 8gb , Macbook will be returned, if you search for 16gb, 'HP LP3065' will be returned.

If thats what you are looking for, edit catalog/model/catalog/product/product.php and find the code:

Code: Select all

if (!$product_data) {
			$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)";
and replace it with:

Code: Select all

if (!$product_data) {
			$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id)";
Now find the code:

Code: Select all

public function getTotalProducts($data = array()) {
		$sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)";
and replace it with:

Code: Select all

public function getTotalProducts($data = array()) {
		$sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id)";
Now , twice , you will find the code:

Code: Select all

$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
and both times, replace it with:

Code: Select all

$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%' OR LCASE(pa.text) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";

Re: Search by Product Attributes Help

Posted: Tue May 22, 2012 5:58 pm
by aaron1988
Hi mate,

cheers i will try this now :)

EDIT: how hard would it be to implement this into the SmartSearch module? as i forgot to say i have installed this, and if enabled it wont work for what i need :)

Aaron

Re: Search by Product Attributes Help

Posted: Wed Jul 11, 2012 11:21 pm
by Johnathan
I've added the ability to search by product attributes in Smart Search now.

Re: Search by Product Attributes Help

Posted: Thu Jul 12, 2012 2:00 pm
by aaron1988
Hi Jonathan,

I have seen thankyou so much for the quick integration

Aaron

Re: Search by Product Attributes Help

Posted: Sun Dec 02, 2012 12:56 pm
by ryancalderon
This solution works perfectly.. thank you very much

Re: Search by Product Attributes Help

Posted: Thu May 08, 2014 7:59 pm
by designsinspired
florinsith wrote:Dont know if I get you correctly. The below code will return in search , products that have the atributes values like keyword.
Example: In a default opencart install, the demo 'Macbook' product has an atribute group called memory, atribute name: test1 and the atribute value: 8gb . So if you search for 8gb , Macbook will be returned, if you search for 16gb, 'HP LP3065' will be returned.

If thats what you are looking for, edit catalog/model/catalog/product/product.php and find the code:

Code: Select all

if (!$product_data) {
			$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)";
and replace it with:

Code: Select all

if (!$product_data) {
			$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id)";
Now find the code:

Code: Select all

public function getTotalProducts($data = array()) {
		$sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)";
and replace it with:

Code: Select all

public function getTotalProducts($data = array()) {
		$sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id)";
Now , twice , you will find the code:

Code: Select all

$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
and both times, replace it with:

Code: Select all

$implode[] = "LCASE(pd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%' OR LCASE(pa.text) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
Hello there

This is exactly what I am looking for, however it seems the code has changed in Opencart 1.5.6.2

Are you possibly able to advise if this is possible in this version of Opencart?

Thank you for your time.

Re: Search by Product Attributes Help

Posted: Thu Jun 26, 2014 10:59 pm
by awaix
can anyone suggest above mentioned method for Opencart 1.5.6? please
thanks.

Re: Search by Product Attributes Help

Posted: Sat Jun 28, 2014 8:58 pm
by florinsith
Same file, find:

Code: Select all

$sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
youll find it twice, each time replace it with:

Code: Select all

$sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
next find:

Code: Select all

if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
you will find it twice, each time add before it:

Code: Select all

$sql .= " OR LCASE(pa.text) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";

Re: Search by Product Attributes Help

Posted: Tue May 19, 2015 4:16 pm
by PaulSketo
Anyone for OC 2.0.0?? Please!!