Problém s SQL dotazem (Prestashop)

2. 3. 2015 10:54:05

Dobrý den,

klient používá nějakou starší verzi Prestashopu, a z ničeho nic se mu přestaly ukládat objednávky - nevidí je v administraci. Koukal jsem ale, že do databáze se v pohodě uloží.

Koukal jsem na dotaz, který ty data vybírá, a je to tahle šílená věc:

SELECT SQL_CALC_FOUND_ROWS a.*, a.id_order AS id_pdf, CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`, osl.`name` AS `osname`, os.`color`, IF((SELECT COUNT(so.id_order) FROM `ps_orders` so WHERE so.id_customer = a.id_customer AND so.valid = 1) > 1, 0, 1) as new, (SELECT COUNT(od.`id_order`) FROM `ps_order_detail` od WHERE od.`id_order` = a.`id_order` GROUP BY `id_order`) AS product_number FROM `ps_orders` a LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`) LEFT JOIN `ps_order_history` oh ON (oh.`id_order` = a.`id_order`) LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = oh.`id_order_state`) LEFT JOIN `ps_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 3) WHERE 1 AND oh.`id_order_history` = (SELECT MAX(`id_order_history`) FROM `ps_order_history` moh WHERE moh.`id_order` = a.`id_order` GROUP BY moh.`id_order`) ORDER BY `date_add` DESC LIMIT 0,4000

Problém je v tom, že ikdyž jsem nakonci upravil limit z 0,20 na 0,4000 tak se zobrazí poslední objednávka s pořadovým číslem 3497, ale těch záznamů je tam ještě asi o 10 více.. Netušíte někdo, v čem může být problém? Připadá mi to, jakoby da databáze neuměla vrátit více záznamů - protože ikdyž ten dotaz spustím z phpmyadmin, tak se ani tam nezobrazí ty poslední objednávky.

S prestou vůbec nepracuji. Třeba se najde někdo, kdo se s podobným problémem setkal.

2. 3. 2015 10:54:05

https://webtrh.cz/diskuse/problem-s-sql-dotazem-prestashop/#reply1095239

Martin Symerský

(2 hodnocení)

2. 3. 2015 10:58:42

dokážeš zpřístupnit tu databázi?

2. 3. 2015 10:58:42

https://webtrh.cz/diskuse/problem-s-sql-dotazem-prestashop/#reply1095238

Jiří Křenek

(15 hodnocení)

2. 3. 2015 11:02:05

Mohu zpřístupnit strukturu:

CREATE TABLE `ps_orders` (
`id_order` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_carrier` int(10) unsigned NOT NULL,
`id_lang` int(10) unsigned NOT NULL,
`id_customer` int(10) unsigned NOT NULL,
`id_cart` int(10) unsigned NOT NULL,
`id_currency` int(10) unsigned NOT NULL,
`id_address_delivery` int(10) unsigned NOT NULL,
`id_address_invoice` int(10) unsigned NOT NULL,
`secure_key` varchar(32) NOT NULL DEFAULT '-1',
`payment` varchar(255) NOT NULL,
`module` varchar(255) DEFAULT NULL,
`recyclable` tinyint(1) unsigned NOT NULL DEFAULT '0',
`gift` tinyint(1) unsigned NOT NULL DEFAULT '0',
`gift_message` text,
`shipping_number` varchar(32) DEFAULT NULL,
`total_discounts` decimal(10,2) NOT NULL DEFAULT '0.00',
`total_paid` decimal(10,2) NOT NULL DEFAULT '0.00',
`total_paid_real` decimal(10,2) NOT NULL DEFAULT '0.00',
`total_products` decimal(10,2) NOT NULL DEFAULT '0.00',
`total_shipping` decimal(10,2) NOT NULL DEFAULT '0.00',
`total_wrapping` decimal(10,2) NOT NULL DEFAULT '0.00',
`invoice_number` int(10) unsigned NOT NULL DEFAULT '0',
`delivery_number` int(10) unsigned NOT NULL DEFAULT '0',
`invoice_date` datetime NOT NULL,
`delivery_date` datetime NOT NULL,
`valid` int(1) unsigned NOT NULL DEFAULT '0',
`date_add` datetime NOT NULL,
`date_upd` datetime NOT NULL,
PRIMARY KEY (`id_order`),
KEY `id_customer` (`id_customer`),
KEY `id_cart` (`id_cart`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `ps_order_detail` (
`id_order_detail` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_order` int(10) unsigned NOT NULL,
`product_id` int(10) unsigned NOT NULL,
`product_attribute_id` int(10) unsigned DEFAULT NULL,
`product_name` varchar(255) NOT NULL,
`product_quantity` int(10) unsigned NOT NULL DEFAULT '0',
`product_quantity_in_stock` int(10) unsigned NOT NULL DEFAULT '0',
`product_quantity_refunded` int(10) unsigned NOT NULL DEFAULT '0',
`product_quantity_return` int(10) unsigned NOT NULL DEFAULT '0',
`product_quantity_reinjected` int(10) unsigned NOT NULL DEFAULT '0',
`product_price` decimal(13,6) NOT NULL DEFAULT '0.000000',
`product_quantity_discount` decimal(13,6) NOT NULL DEFAULT '0.000000',
`product_ean13` varchar(13) DEFAULT NULL,
`product_reference` varchar(32) DEFAULT NULL,
`product_supplier_reference` varchar(32) DEFAULT NULL,
`product_weight` float NOT NULL,
`tax_name` varchar(16) NOT NULL,
`tax_rate` decimal(10,2) NOT NULL DEFAULT '0.00',
`ecotax` decimal(10,2) NOT NULL DEFAULT '0.00',
`download_hash` varchar(255) DEFAULT NULL,
`download_nb` int(10) unsigned DEFAULT '0',
`download_deadline` datetime DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id_order_detail`),
KEY `order_detail_order` (`id_order`),
KEY `product_id` (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `ps_order_discount` (
`id_order_discount` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_order` int(10) unsigned NOT NULL,
`id_discount` int(10) unsigned NOT NULL,
`name` varchar(32) NOT NULL,
`value` decimal(10,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`id_order_discount`),
KEY `order_discount_order` (`id_order`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `ps_order_history` (
`id_order_history` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_employee` int(10) unsigned NOT NULL,
`id_order` int(10) unsigned NOT NULL,
`id_order_state` int(10) unsigned NOT NULL,
`date_add` datetime NOT NULL,
PRIMARY KEY (`id_order_history`),
KEY `order_history_order` (`id_order`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `ps_order_message` (
`id_order_message` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date_add` datetime NOT NULL,
PRIMARY KEY (`id_order_message`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `ps_order_message_lang` (
`id_order_message` int(10) unsigned NOT NULL,
`id_lang` int(10) unsigned NOT NULL,
`name` varchar(128) NOT NULL,
`message` text NOT NULL,
PRIMARY KEY (`id_order_message`,`id_lang`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `ps_order_return` (
`id_order_return` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_customer` int(10) unsigned NOT NULL,
`id_order` int(10) unsigned NOT NULL,
`state` tinyint(1) unsigned NOT NULL DEFAULT '1',
`question` text NOT NULL,
`date_add` datetime NOT NULL,
`date_upd` datetime NOT NULL,
PRIMARY KEY (`id_order_return`),
KEY `order_return_customer` (`id_customer`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `ps_order_return_detail` (
`id_order_return` int(10) unsigned NOT NULL,
`id_order_detail` int(10) unsigned NOT NULL,
`id_customization` int(10) NOT NULL DEFAULT '0',
`product_quantity` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id_order_return`,`id_order_detail`,`id_customization`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `ps_order_return_state` (
`id_order_return_state` int(10) unsigned NOT NULL AUTO_INCREMENT,
`color` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id_order_return_state`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `ps_order_return_state_lang` (
`id_order_return_state` int(10) unsigned NOT NULL,
`id_lang` int(10) unsigned NOT NULL,
`name` varchar(64) NOT NULL,
UNIQUE KEY `order_state_lang_index` (`id_order_return_state`,`id_lang`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `ps_order_slip` (
`id_order_slip` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_customer` int(10) unsigned NOT NULL,
`id_order` int(10) unsigned NOT NULL,
`shipping_cost` tinyint(3) unsigned NOT NULL DEFAULT '0',
`date_add` datetime NOT NULL,
`date_upd` datetime NOT NULL,
PRIMARY KEY (`id_order_slip`),
KEY `order_slip_customer` (`id_customer`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `ps_order_slip_detail` (
`id_order_slip` int(10) unsigned NOT NULL,
`id_order_detail` int(10) unsigned NOT NULL,
`product_quantity` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id_order_slip`,`id_order_detail`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `ps_order_state` (
`id_order_state` int(10) unsigned NOT NULL AUTO_INCREMENT,
`invoice` tinyint(1) unsigned DEFAULT '0',
`send_email` tinyint(1) unsigned NOT NULL DEFAULT '0',
`color` varchar(32) DEFAULT NULL,
`unremovable` tinyint(1) unsigned NOT NULL,
`hidden` tinyint(1) unsigned NOT NULL DEFAULT '0',
`logable` tinyint(1) NOT NULL DEFAULT '0',
`delivery` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id_order_state`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `ps_order_state_lang` (
`id_order_state` int(10) unsigned NOT NULL,
`id_lang` int(10) unsigned NOT NULL,
`name` varchar(64) NOT NULL,
`template` varchar(64) NOT NULL,
UNIQUE KEY `order_state_lang_index` (`id_order_state`,`id_lang`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2. 3. 2015 11:02:05

https://webtrh.cz/diskuse/problem-s-sql-dotazem-prestashop/#reply1095237

David Pohludek

(3 hodnocení)

2. 3. 2015 11:02:19

Jestli se neukazuji vsechny zaznamy, muze to byt tim, ze tech 10 nesplnuje vsechny conditions ktere jsou zadany ... ale takhle na slepo je tezke neco rict ...

2. 3. 2015 11:02:19

https://webtrh.cz/diskuse/problem-s-sql-dotazem-prestashop/#reply1095236

Martin Symerský

(2 hodnocení)

2. 3. 2015 11:04:59

Jsou potřeba data. Pak se během chvilky dá říct v čem je problém. Struktura nestačí.

2. 3. 2015 11:04:59

https://webtrh.cz/diskuse/problem-s-sql-dotazem-prestashop/#reply1095235

Pro odpověď se přihlašte.

Přihlásit