View Issue Details

IDProjectCategoryView StatusLast Update
0003797Composrcore_database_driverspublic2019-11-26 22:03
ReporterChris GrahamAssigned To 
Severityfeature 
Status non-assignedResolutionopen 
Product Version 
Fixed in Version 
Summary0003797: Cleanup queries so can_arbitrary_groupby/remove_duplicate_rows
DescriptionIn various places we use can_arbitrary_groupby and/or remove_duplicate_rows to strip out duplicated rows coming out of SQL queries.

This is because doing JOINs on tables will multiply up rows on the main table being queried, if the joined table has multiple matching records.
For example, if you are using a JOIN to check group access to a category, and a user is in multiple groups with access, you'll get as many rows back as there are groups with access.

We do it using JOINs because MySQL did not support subqueries until 4.1 in 2003. Webhosts are notorious for running old versions of MySQL, but at this point it would be shocking for a host to be running a version this old.

So we should alter the DB drives to remove the concept of can_arbitrary_groupby (which is a non-compliant MySQL hack), and assume subquery support is always there (I think this is already done in v11). Then instead of JOINs to check things like category membership or group access, we use IN or EXISTS clauses in the queries.

main_multi_content needs changes too. This currently doesn't use can_arbitrary_groupby/remove_duplicate_rows because of the complexity in this code. Instead it has it's own deduplication technique, and always reads records from offset 0, manually skipping records up until $start.
TagsRoadmap: v11
Attach Tags
Time estimation (hours)4
Sponsorship open

Relationships

related to 0003795 resolvedChris Graham Composr website (compo.sr) Category replication and panels 
related to 0003732 non-assigned Composr Smarter DB sorting for multi-lang sites 

Activities

Chris Graham

2019-11-17 16:39

administrator   ~0006155

Also look at the export_* tasks, and try and make sure they don't have to load all rows into memory before starting streaming out a spreadsheet.

Issue History

Date Modified Username Field Change
2019-04-03 19:57 Chris Graham New Issue
2019-04-03 19:58 Chris Graham Relationship added related to 0003795
2019-06-27 19:00 Chris Graham Tag Attached: Roadmap: v11
2019-11-17 16:39 Chris Graham Note Added: 0006155
2019-11-26 22:03 Chris Graham Relationship added related to 0003732