OMG, do my eyes deceive me, am I actually seeing assistance from Paul in the form of code without paying for it
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?