Best selling product Module Error

[samuel89]

Can someone look into the error shown in the following:

 

http://mightyutan.com.my/en/best-sales

 

I can't seem to find where the sql statement is generated.

 

Thanks

[CartExpert.net]

Hi.

 

The SQL query is created and executed in 'classes/ProductSale.php', function 'getBestSales'.

 

Regards.

Robin.

The CartExpert Team

[samuel89]

Thanks CartExpert. I found out it's probably the extra comma in the line (,1,2,3,4,5,6....

 

Is this a prestashop bug as I never changed the file. How do I fix it?

 

Thanks.

[CartExpert.net]

That's right, the problem is with the extra comma.

 

But according to the code that shouldn't be there, as that would mean that there has been an empty array key, which can't happen.

 

Post your getBestSales function.

 

Regards.

Robin.

The CartExpert Team

[samuel89]

	public static function getBestSales($id_lang, $page_number = 0, $nb_products = 10, $order_by = null, $order_way = null)
	{
		if ($page_number < 0) $page_number = 0;
		if ($nb_products < 1) $nb_products = 10;
		$final_order_by = $order_by;
		$order_table = ''; 		
		if (is_null($order_by) || $order_by == 'position' || $order_by == 'price') $order_by = 'sales';
		if ($order_by == 'date_add' || $order_by == 'date_upd')
			$order_table = 'product_shop'; 				
		if (is_null($order_way) || $order_by == 'sales') $order_way = 'DESC';
		$groups = FrontController::getCurrentCustomerGroups();
		$sql_groups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');
		$interval = Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20;

		//Subquery: get product ids in a separate query to (greatly!) improve performances and RAM usage
		$sql = 'SELECT cp.`id_product`
				FROM `'._DB_PREFIX_.'category_group` cg
				LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_category` = cg.`id_category`)
				WHERE cg.`id_group` '.$sql_groups;
		$products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
		$ids = array();
		foreach ($products as $product)
			$ids[$product['id_product']] = 1;
		$ids = array_keys($ids);
		sort($ids);
		$ids = count($ids) > 0 ? implode(',', $ids) : 'NULL';
		
		//Main query
		$sql = 'SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
					pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`,
					pl.`meta_keywords`, pl.`meta_title`, pl.`name`,
					m.`name` AS manufacturer_name, p.`id_manufacturer` as id_manufacturer,
					MAX(image_shop.`id_image`) id_image, il.`legend`,
					ps.`quantity` AS sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`,
					DATEDIFF(p.`date_add`, DATE_SUB(NOW(),
					INTERVAL '.$interval.' DAY)) > 0 AS new
				FROM `'._DB_PREFIX_.'product_sale` ps
				LEFT JOIN `'._DB_PREFIX_.'product` p ON ps.`id_product` = p.`id_product`
				'.Shop::addSqlAssociation('product', 'p', false).'
				LEFT JOIN `'._DB_PREFIX_.'product_lang` pl
					ON p.`id_product` = pl.`id_product`
					AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'
				LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product`)'.
				Shop::addSqlAssociation('image', 'i', false, 'image_shop.cover=1').'
				LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)$id_lang.')
				LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
				LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`)
					AND tr.`id_country` = '.(int)Context::getContext()->country->id.'
					AND tr.`id_state` = 0
				LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)
				'.Product::sqlStock('p').'
				WHERE product_shop.`active` = 1
					AND p.`visibility` != \'none\'
					AND p.`id_product` IN ('.$ids.')
				GROUP BY product_shop.id_product
				ORDER BY '.(!empty($order_table) ? '`'.pSQL($order_table).'`.' : '').'`'.pSQL($order_by).'` '.pSQL($order_way).'
				LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products;

		$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);

		if ($final_order_by == 'price')
			Tools::orderbyPrice($result, $order_way);
		if (!$result)
			return false;
		return Product::getProductsProperties($id_lang, $result);
	}

I tried printing $ids after sort($ids);

 

It shows that i have 237 keys although i only have 236 products. I suspect it might be the database. but i am unsure how to proceed as I checked ps_category_group and ps_category_product used by SQL but I can't seem to find anything wrong (but it might just be me not knowing what to look for)

 

Thanks!

[CartExpert.net]

$products must contain a row where id_product is empty.

 

Try to run the ps_category_group on phpmyadmin and check where is the row with empty id_product.

 

Regards.

Robin.

The CartExpert Team

[samuel89]

do u mean ps_category_product? As it's the only 1 with id_product. I sort id_product by ascension and the 1st values were

 

id_category id_product  position  

11                    1                   0

3                      1                   0

3                      2                    1

this is really weird

 

below is print_r($ids) after $ids=array_keys($ids);

 

 

 

Array ( [0] => [1] => 1 [2] => 2 [3] => 3 [4] => 4 [5] => 5 [6] => 6 [7] => 7 [8] => 8 [9] => 9 [10] => 10 [11] => 11 [12] => 12 [13] => 13 [14] => 14 [15] => 15 [16] => 16 [17] => 17 [18] => 18 [19] => 19 [20] => 20 [21] => 21 [22] => 22 [23] => 23 [24] => 24 [25] => 25 [26] => 26 [27] => 27 [28] => 28 [29] => 29 [30] => 30 [31] => 31 [32] => 32 [33] => 33 [34] => 34 [35] => 35 [36] => 36 [37] => 37 [38] => 38 [39] => 39 [40] => 40 [41] => 41 [42] => 42 [43] => 43 [44] => 44 [45] => 45 [46] => 46 [47] => 47 [48] => 48 [49] => 49 [50] => 50 [51] => 51 [52] => 52 [53] => 53 [54] => 54 [55] => 55 [56] => 56 [57] => 57 [58] => 58 [59] => 59 [60] => 60 [61] => 61 [62] => 62 [63] => 63 [64] => 64 [65] => 65 [66] => 66 [67] => 67 [68] => 68 [69] => 69 [70] => 70 [71] => 71 [72] => 72 [73] => 73 [74] => 74 [75] => 75 [76] => 76 [77] => 77 [78] => 78 [79] => 79 [80] => 80 [81] => 81 [82] => 82 [83] => 83 [84] => 84 [85] => 85 [86] => 86 [87] => 87 [88] => 88 [89] => 89 [90] => 90 [91] => 91 [92] => 92 [93] => 93 [94] => 94 [95] => 95 [96] => 96 [97] => 97 [98] => 98 [99] => 99 [100] => 100 [101] => 101 [102] => 102 [103] => 103 [104] => 104 [105] => 105 [106] => 106 [107] => 107 [108] => 108 [109] => 109 [110] => 110 [111] => 111 [112] => 112 [113] => 113 [114] => 114 [115] => 115 [116] => 116 [117] => 117 [118] => 118 [119] => 119 [120] => 120 [121] => 121 [122] => 122 [123] => 123 [124] => 124 [125] => 125 [126] => 126 [127] => 127 [128] => 128 [129] => 129 [130] => 130 [131] => 131 [132] => 132 [133] => 133 [134] => 134 [135] => 135 [136] => 136 [137] => 137 [138] => 138 [139] => 139 [140] => 140 [141] => 141 [142] => 142 [143] => 143 [144] => 144 [145] => 145 [146] => 146 [147] => 147 [148] => 148 [149] => 149 [150] => 150 [151] => 151 [152] => 152 [153] => 153 [154] => 154 [155] => 155 [156] => 156 [157] => 157 [158] => 158 [159] => 159 [160] => 160 [161] => 161 [162] => 162 [163] => 163 [164] => 164 [165] => 165 [166] => 166 [167] => 167 [168] => 168 [169] => 169 [170] => 170 [171] => 171 [172] => 172 [173] => 173 [174] => 174 [175] => 175 [176] => 176 [177] => 177 [178] => 178 [179] => 179 [180] => 180 [181] => 181 [182] => 182 [183] => 183 [184] => 184 [185] => 185 [186] => 186 [187] => 187 [188] => 188 [189] => 189 [190] => 190 [191] => 191 [192] => 192 [193] => 193 [194] => 194 [195] => 195 [196] => 196 [197] => 197 [198] => 198 [199] => 199 [200] => 200 [201] => 201 [202] => 202 [203] => 203 [204] => 204 [205] => 205 [206] => 206 [207] => 207 [208] => 208 [209] => 209 [210] => 210 [211] => 211 [212] => 212 [213] => 213 [214] => 214 [215] => 215 [216] => 216 [217] => 217 [218] => 218 [219] => 219 [220] => 220 [221] => 221 [222] => 222 [223] => 223 [224] => 224 [225] => 225 [226] => 226 [227] => 227 [228] => 228 [229] => 229 [230] => 230 [231] => 231 [232] => 232 [233] => 233 [234] => 234 [235] => 235 [236] => 236 )

Edited December 27, 2013 by samuel89 (see edit history)

[samuel89]

ok..so i fixed the sql error for the top seller module by looking at the similar function getBestSalesLight in ProductSale.php.

 

apparently the getBestSale function is missing the red line

WHERE cg.`id_group` '.$sql_groups.' AND cp.`id_product` IS NOT NULL';

 

 

 

HOWEVER, I still dont know how a null id_product got in as I dont see it in SQL. Can some1 please enlighten me? Does it gotta do with the CSV Import as I always import products using it.

 

Thanks.

[Artizzz]

ok..so i fixed the sql error for the top seller module by looking at the similar function getBestSalesLight in ProductSale.php.

 

apparently the getBestSale function is missing the red line

 

WHERE cg.`id_group` '.$sql_groups.' AND cp.`id_product` IS NOT NULL';

 

 

 

HOWEVER, I still dont know how a null id_product got in as I dont see it in SQL. Can some1 please enlighten me? Does it gotta do with the CSV Import as I always import products using it.

 

Thanks.

Thanks, this helped me to fix this problem on version 1.5.6.1 (after upgrading from 1.5.2).

[vansrv7]

I have the same problem with topbestseller module. It worked correctly up to yesterday, now it stopped working (no modification made to the core, just configured lof footer module).

 

I have a Db error showing an extra comma:

JOIN ps_stock_available stock
            ON (stock.id_product = p.id_product AND stock.id_shop_group = 1 AND stock.id_shop = 0 )
                WHERE product_shop.`active` = 1
                    AND p.`visibility` != 'none'
                    AND p.`id_product` IN (,8,9,11,12,13,14,....continues.....)
                GROUP BY product_shop.id_product
                ORDER BY `sales` DESC
                LIMIT 0, 12