View Issue Details

IDProjectCategoryView StatusLast Update
0005382OXID eShop (all versions)1.01. Products (product, categories, manufacturer, promotions etc.)public2015-05-04 15:30
Reportermichael_keiluweit 
PriorityurgentSeveritycriticalReproducibilityalways
Status resolvedResolutionfixed 
Product Version4.7.6 / 5.0.6 
Target Version4.7.11 / 5.0.11Fixed in Version4.7.10 / 5.0.10 
Summary0005382: during sorting the article of a categorie with the timestamp, the shop builds a SQL query with an error
DescriptionAfter enabling the sorting of the articles in a category with the timestamp, the shop builds an sql query which has an error in it.
Steps To Reproduce1. switch $iDebug to 2 (otherwise you will not have the exception in the log).
2. goto admin.
3. Master Settings -> Core Settings -> Settings -> Products -> "Database Fields Product Lists can be sorted after".
4. add oxtimestamp.
5. goto frontend.
6. you need a list of products of a category.
7. sort after the timestamp.
8. have a look at the exception log.
Additional Information2013-08-30 12:54:12exception 'ADODB_Exception' with message 'mysql error: [1052: Column 'oxtimestamp' in order clause is ambiguous] in EXECUTE("SELECT `oxv_oxarticles_1_de`.`oxid`, `oxv_oxarticles_1_de`.`oxparentid`, `oxv_oxarticles_1_de`.`oxvarstock`, `oxv_oxarticles_1_de`.`oxshopid`, `oxv_oxarticles_1_de`.`oxstock`, `oxv_oxarticles_1_de`.`oxstockflag`, `oxv_oxarticles_1_de`.`oxvarcount`, `oxv_oxarticles_1_de`.`oxvarname`, `oxv_oxarticles_1_de`.`oxtitle`, `oxv_oxarticles_1_de`.`oxvarselect`, `oxv_oxarticles_1_de`.`oxthumb`, `oxv_oxarticles_1_de`.`oxtprice`, `oxv_oxarticles_1_de`.`oxvat`, `oxv_oxarticles_1_de`.`oxvarminprice`, `oxv_oxarticles_1_de`.`oxskipdiscounts`, `oxv_oxarticles_1_de`.`oxprice`, `oxv_oxarticles_1_de`.`oxvarmaxprice`, `oxv_oxarticles_1_de`.`oxunitquantity`, `oxv_oxarticles_1_de`.`oxweight` FROM oxv_oxobject2category_1 as oc left join oxv_oxarticles_1_de
                    ON oxv_oxarticles_1_de.oxid = oc.oxobjectid
                    WHERE ( oxv_oxarticles_1_de.oxactive = 1 and ( oxv_oxarticles_1_de.oxstockflag != 2 or ( oxv_oxarticles_1_de.oxstock + oxv_oxarticles_1_de.oxvarstock ) > 0 ) and IF( oxv_oxarticles_1_de.oxvarcount = 0, 1, ( select 1 from oxv_oxarticles_1_de as art where art.oxparentid=oxv_oxarticles_1_de.oxid and ( art.oxactive = 1 ) and ( art.oxstockflag != 2 or art.oxstock > 0 ) limit 1 ) ) ) and oxv_oxarticles_1_de.oxparentid = ''
                    and oc.oxcatnid = 'd86d90e4b441aa3f0004dcda5ba5bb38' ORDER BY oxtimestamp asc , oc.oxpos, oc.oxobjectid LIMIT 0, 10")
' in /mnt/hgfs/supportvm52/ee506/core/adodblite/adodb-exceptions.inc.php:84

Stack trace:
#0 /mnt/hgfs/supportvm52/ee506/core/adodblite/adodbSQL_drivers/mysql/mysql_driver.inc(369): adodb_throw('mysql', 'EXECUTE', 1052, 'Column 'oxtimes...', 'SELECT `oxv_oxa...', false, Object(object_ADOConnection))
#1 /mnt/hgfs/supportvm52/ee506/core/adodblite/adodb.inc.php(334): mysql_driver_ADOConnection->do_query('SELECT `oxv_oxa...', 0, 10, false)
#2 /mnt/hgfs/supportvm52/ee506/core/oxlegacydb.php(235): ADOConnection->SelectLimit('SELECT `oxv_oxa...', 10, 0, false)
#3 /mnt/hgfs/supportvm52/ee506/core/oxlist.php(382): oxLegacyDb->selectLimit('SELECT `oxv_oxa...', 10, 0)
#4 /mnt/hgfs/supportvm52/ee506/application/models/oxarticlelist.php(77): oxList->selectString('SELECT `oxv_oxa...')
#5 /mnt/hgfs/supportvm52/ee506/application/models/oxarticlelist.php(461): oxArticleList->selectString('SELECT `oxv_oxa...')
#6 /mnt/hgfs/supportvm52/ee506/application/controllers/alist.php(384): oxArticleList->loadCategoryArticles('d86d90e4b441aa3...', NULL)
#7 /mnt/hgfs/supportvm52/ee506/application/controllers/alist.php(800): aList->_loadArticles(Object(oxCategory))
#8 /mnt/hgfs/supportvm52/ee506/application/controllers/alist.php(227): aList->getArticleList()
#9 /mnt/hgfs/supportvm52/ee506/core/oxshopcontrol.php(588): aList->render()
#10 /mnt/hgfs/supportvm52/ee506/core/oxshopcontrol.php(427): oxShopControl->_render(Object(aList))
0000011 /mnt/hgfs/supportvm52/ee506/core/oxshopcontrol.php(147): oxShopControl->_process('alist', NULL, NULL, NULL)
0000012 /mnt/hgfs/supportvm52/ee506/core/oxid.php(32): oxShopControl->start()
0000013 /mnt/hgfs/supportvm52/ee506/index.php(19): Oxid::run()
0000014 /mnt/hgfs/supportvm52/ee506/oxseo.php(37): require('/mnt/hgfs/suppo...')
0000015 {main}
---------------------------------------------
TagsSorting
ThemeAzure
BrowserAll
PHP Versionany
MySQL Versionany

Activities

michael_keiluweit

2013-08-30 13:36

developer   ~0009012

Can this be fixed by an easy hack (module)?

saulius.stasiukaitis

2013-12-19 14:50

reporter   ~0009367

"Can this be fixed by an easy hack (module)?"
It should be. In this case by overloading oxArticleList.

This can be done by overloading method _getCategorySelect:

_getCategorySelect( $sFields, $sCatId, $aSessionFilter ) {
    $sFields = $sFields .', $sArticleTable.oxtimestamp as oxtimestamp';
    parent::_getCategorySelect( $sFields, $sCatId, $aSessionFilter );
}

At the end this query would look like:
$sSelect = "
SELECT $sFields, $sArticleTable.oxtimestamp
FROM $sO2CView as oc
left join $sArticleTable ON $sArticleTable.oxid = oc.oxobjectid
WHERE ".$this->getBaseObject()->getSqlActiveSnippet()."
   and $sArticleTable.oxparentid = ''
   and oc.oxcatnid = ".$oDb->quote($sCatId)." $sFilterSql
ORDER BY $sSorting oc.oxpos, oc.oxobjectid
";

saulius.stasiukaitis

2013-12-19 16:54

reporter   ~0009369

Same problems in:
oxArticleList::loadSearchIds
oxSearch::_getSearchSelect

martinwegele

2014-12-31 11:18

reporter   ~0010565

fixed in 5.1.2