Post by Joe1234 » Sat Aug 20, 2022 7:41 am

I'm trying to retrieve the product IDs that match my attribute query. Clearly what I'm trying wont work due to the structure of the attribute table, but it's just to show what I'm trying to achieve.

Code: Select all

SELECT product_id FROM database_product_attribute WHERE ( (attribute_id = '34' AND text LIKE 'Excellent') AND (attribute_id = '35' AND text LIKE 'Boys') AND (attribute_id = '37' AND text LIKE 'Red') )
The only way I can think to resolve this is to instead of selecting the product_id, use "*" instead and go through the resulting array and find product_id that show up 3 times or how many attributes I search for. I'd prefer to keep it all in the query instead of cycling through an array after the fact because it would make what I have to do next easier.
Any help on how to do this query?

v3.0.3.9 php 8.1
I'm here for a reason, if your response is contact a/the developer, just don't reply.


Active Member

Posts

Joined
Sat Jan 01, 2022 5:47 am

Post by thekrotek » Sat Aug 20, 2022 3:53 pm

Wrong operand between groups in brackets, should be OR

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by Joe1234 » Sat Aug 20, 2022 7:44 pm

That would leave me in the same situation of needing to cycle through the resulting array to find the ID that appear 3X because it looks at each statement and returns if true instead of all of them as one.

v3.0.3.9 php 8.1
I'm here for a reason, if your response is contact a/the developer, just don't reply.


Active Member

Posts

Joined
Sat Jan 01, 2022 5:47 am

Post by thekrotek » Sat Aug 20, 2022 8:24 pm

It will return product IDs matching ANY of the 3 conditions. Currently it tries to find products matching ALL 3 conditions.

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by Joe1234 » Sat Aug 20, 2022 10:26 pm

But that is what I'm trying to achieve, retrieve the ID of products matching all, but I cant because I don't know how to do it with the table structured the way it is.

v3.0.3.9 php 8.1
I'm here for a reason, if your response is contact a/the developer, just don't reply.


Active Member

Posts

Joined
Sat Jan 01, 2022 5:47 am

Post by thekrotek » Sat Aug 20, 2022 11:00 pm

OK, I've finally got the idea. Might be tricky to do with one query.

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by paulfeakins » Mon Aug 22, 2022 10:20 pm

Might not be the most efficient query but something like this would do it all in SQL rather than PHP:

Code: Select all

SELECT product_id 
FROM oc_product_attribute
WHERE product_id IN 
(SELECT product_id FROM oc_product_attribute WHERE attribute_id = 1 AND text = '{value 1}')
AND product_id IN 
(SELECT product_id FROM oc_product_attribute WHERE attribute_id = 2 AND text = '{value 2}')
AND product_id IN 
(SELECT product_id FROM oc_product_attribute WHERE attribute_id = 3 AND text = '{value 3}')

UK OpenCart Hosting | OpenCart Audits | OpenCart Support - please email info@antropy.co.uk


User avatar
Guru Member
Online

Posts

Joined
Mon Aug 22, 2011 11:01 pm
Location - London Gatwick, United Kingdom

Post by Joe1234 » Tue Aug 23, 2022 2:02 am

OMG, do my eyes deceive me, am I actually seeing assistance from Paul in the form of code without paying for it :o :o :o j/k lol.

Thank you for that, but after hours of googling I figured that one out and tried it already, but it didn't work for me for two reasons.
1/ For whatever reason, it actually doesn't work properly. It doesn't return all of the items. Ex with a particular query I know I have 32 items that match in the store from count and through filtering on the front end, but this returns about 12 of them. I can't explain it.
2/ Query time on this most times is decent, but sometimes it takes longer than it should. I don't know if it is because of my server or what, but I couldn't be bothered to flesh it out.
I'm sure there is a twist in it that we didn't take into account that would have gotten me exactly what I want, but either way even though I'd prefer this, I did something else.

I ended up giving up on keeping it all in one query and unfortunately foregoing one other thing I wanted to do in the query and instead doing the following, but it leaves me with a different issue (I'm still fleshing this out as far as what I want in to query but so far this gives me all the info I need).

Code: Select all

$array1 = [];
foreach ($data['data_search']['attribute'] as $data_search) {

	$data['current_product_id'] = $this->request->get['product_id'];
	
	$sqlquery = $this->db->query("SELECT text FROM " . DB_PREFIX . "product_attribute WHERE attribute_id = '" . $data_search['id'] . "' AND product_id = '" . $data['current_product_id'] . "'")
	$result = $sqlquery->rows;

	$sql = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (p.product_id = pa.product_id) WHERE pa.attribute_id = '" . $data_search['id'] . "' AND pa.text " . $data_search['logic'] . " '" . $result[0]['text'] . "' AND p.status = 1");
	$array1[] = $sql->rows;

}


if (count($data['condition']['attribute']) == 1) {

$result = array_map('current', $array1[0]);

} elseif (count($data['condition']['attribute']) == 2) {

$a0 = array_map('current', $array1[0]);
$a1 = array_map('current', $array1[1]);

$result = array_intersect($a0,$a1);

} elseif (count($data['condition']['attribute']) == 3) {

$a0 = array_map('current', $array1[0]);
$a1 = array_map('current', $array1[1]);
$a2 = array_map('current', $array1[2]);

$result = array_intersect($a0,$a1,$a2);

} elseif (count($data['condition']['attribute']) == 4) {

$a0 = array_map('current', $array1[0]);
$a1 = array_map('current', $array1[1]);
$a2 = array_map('current', $array1[2]);
$a3 = array_map('current', $array1[3]);

$result = array_intersect($a0,$a1,$a2,$a3);

}

shuffle($result);

$result = array_slice($result, 0, $data['limit']);
The problem is the array_intersect forces me to do that elseif run to take into account arrays that may or may not be there (which I may have to expand). Is there a better way to do that because I may expand the attribute search and I don't want a huge block of elseif?

v3.0.3.9 php 8.1
I'm here for a reason, if your response is contact a/the developer, just don't reply.


Active Member

Posts

Joined
Sat Jan 01, 2022 5:47 am

Post by paulfeakins » Tue Aug 23, 2022 7:33 pm

Joe1234 wrote:
Tue Aug 23, 2022 2:02 am
OMG, do my eyes deceive me, am I actually seeing assistance from Paul in the form of code without paying for it :o :o :o j/k lol.
Unbelievable but true ;) :laugh:

Joe1234 wrote:
Tue Aug 23, 2022 2:02 am
Thank you for that, but after hours of googling I figured that one out and tried it already, but it didn't work for me for two reasons.
1/ For whatever reason, it actually doesn't work properly. It doesn't return all of the items. Ex with a particular query I know I have 32 items that match in the store from count and through filtering on the front end, but this returns about 12 of them. I can't explain it.
It really should work as above, it must be that the text isn't matching exactly or similar - you could try running the sub-queries on their own.

Joe1234 wrote:
Tue Aug 23, 2022 2:02 am
2/ Query time on this most times is decent, but sometimes it takes longer than it should. I don't know if it is because of my server or what, but I couldn't be bothered to flesh it out.
I'm sure there is a twist in it that we didn't take into account that would have gotten me exactly what I want, but either way even though I'd prefer this, I did something else.
You could time both methods and see which is faster?

UK OpenCart Hosting | OpenCart Audits | OpenCart Support - please email info@antropy.co.uk


User avatar
Guru Member
Online

Posts

Joined
Mon Aug 22, 2011 11:01 pm
Location - London Gatwick, United Kingdom
Who is online

Users browsing this forum: No registered users and 263 guests