View Issue Details

Jump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0006895OXID eShop (all versions)4.01. Database handlingpublic2018-09-03 11:102019-07-30 21:42
Reportersimon.runer 
PrioritynormalSeveritytweakReproducibilityalways
Status acknowledgedResolutionopen 
Product Version6.0.3 
Target VersionFixed in Version 
Summary0006895: Column OXTYPE missing in oxobject2group and misleading MySql comment
DescriptionThe comment on table oxobject2group says (https://github.com/OXID-eSales/oxideshop_ce/blob/master/source/Setup/Sql/database_schema.sql)
"Shows many-to-many relationship between users and groups" which is incorrect. There are stored also relationships between oxpayments and oxgroups and probably others.
As in other tables there should by a OXTYPE column to identify the related table.

TagsDatabase
ThemeNot defined
BrowserNot defined
PHP VersionNot defined
MySQL VersionNot defined

Activities

QA

2018-09-03 11:53

administrator   ~0012604

Also misleading is the comment for the attribute oxobjectid, which reads "User id".

[sp]

simon.runer

2019-07-30 21:42

reporter   ~0012951

In case it helps, with this query you can get the table with OXTYPE. You might need to add tables from modules which added entries to oxobject2group.
Any chance OXTYPE will be added to oxobject2group?

SELECT o2g.OXID, o2g.OXSHOPID, o2g.OXOBJECTID, o2g.OXGROUPSID, o2g.OXTIMESTAMP,
   CASE
       WHEN u.OXID IS NOT NULL THEN 'oxuser'
       WHEN p.OXID IS NOT NULL THEN 'oxpayments'
       WHEN di.OXID IS NOT NULL THEN 'oxdiscount'
       WHEN de.OXID IS NOT NULL THEN 'oxdelivery'
       WHEN des.OXID IS NOT NULL THEN 'oxdeliveryset'
       WHEN v.OXID IS NOT NULL THEN 'oxvouchers'
       WHEN vs.OXID IS NOT NULL THEN 'oxvoucherseries'
       WHEN n.OXID IS NOT NULL THEN 'oxnews'
       WHEN nl.OXID IS NOT NULL THEN 'oxnewsletter'
       WHEN sp.OXID IS NOT NULL THEN 'smx_promotions'
       ELSE ''
   END AS OXTYPE
FROM oxobject2group AS o2g
LEFT JOIN oxuser AS u ON o2g.OXOBJECTID = u.OXID
LEFT JOIN oxpayments AS p ON o2g.OXOBJECTID = p.OXID
LEFT JOIN oxdiscount AS di ON o2g.OXOBJECTID = di.OXID
LEFT JOIN oxdelivery AS de ON o2g.OXOBJECTID = de.OXID
LEFT JOIN oxdeliveryset AS des ON o2g.OXOBJECTID = des.OXID
LEFT JOIN oxvouchers AS v ON o2g.OXOBJECTID = v.OXID
LEFT JOIN oxvoucherseries AS vs ON o2g.OXOBJECTID = vs.OXID;
LEFT JOIN oxnews AS n ON o2g.OXOBJECTID = n.OXID
LEFT JOIN oxnewsletter AS nl ON o2g.OXOBJECTID = nl.OXID;