diff options
author | mattab <matthieu.aubry@gmail.com> | 2016-04-11 03:05:28 +0300 |
---|---|---|
committer | mattab <matthieu.aubry@gmail.com> | 2016-04-11 03:05:28 +0300 |
commit | 0cdb6ef966754e654fbeb95ccc09e6a281f9802c (patch) | |
tree | fc4f7cbc1886939229908b86f15bc328e1d675cb /plugins | |
parent | 68c2b280548477e4a16afcd2e37ef85d9647b905 (diff) |
Always add outer limit when specified in case inner queries return more rows
Diffstat (limited to 'plugins')
-rw-r--r-- | plugins/Live/Model.php | 17 | ||||
-rw-r--r-- | plugins/Live/tests/Integration/ModelTest.php | 6 |
2 files changed, 17 insertions, 6 deletions
diff --git a/plugins/Live/Model.php b/plugins/Live/Model.php index 3f2312234a..80b0c42c9c 100644 --- a/plugins/Live/Model.php +++ b/plugins/Live/Model.php @@ -408,21 +408,28 @@ class Model $orderBy .= "visit_last_action_time " . $filterSortOrder; $orderByParent = "sub.visit_last_action_time " . $filterSortOrder; + // this $innerLimit is a workaround (see https://github.com/piwik/piwik/issues/9200#issuecomment-183641293) + $innerLimit = $limit; if (!$segment->isEmpty()) { - $limit = $limit * 10; + $innerLimit = $limit * 10; } - $subQuery = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy, $groupBy, $limit, $offset); + $innerQuery = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy, $groupBy, $innerLimit, $offset); - $bind = $subQuery['bind']; - // Group by idvisit so that a visitor converting 2 goals only appears once + $bind = $innerQuery['bind']; + // Group by idvisit so that a given visit appears only once, useful when for example: + // 1) when a visitor converts 2 goals + // 2) when an Action Segment is used, the inner query will return one row per action, but we want one row per visit $sql = " SELECT sub.* FROM ( - " . $subQuery['sql'] . " + " . $innerQuery['sql'] . " ) AS sub GROUP BY sub.idvisit ORDER BY $orderByParent "; + if($limit) { + $sql .= sprintf("LIMIT %d \n", $limit); + } return array($sql, $bind); } diff --git a/plugins/Live/tests/Integration/ModelTest.php b/plugins/Live/tests/Integration/ModelTest.php index 828d3865f0..3002b847ba 100644 --- a/plugins/Live/tests/Integration/ModelTest.php +++ b/plugins/Live/tests/Integration/ModelTest.php @@ -57,6 +57,7 @@ class ModelTest extends IntegrationTestCase ) AS sub GROUP BY sub.idvisit ORDER BY sub.visit_last_action_time DESC + LIMIT 100 '; $expectedBind = array( '1', @@ -97,6 +98,7 @@ class ModelTest extends IntegrationTestCase ) AS sub GROUP BY sub.idvisit ORDER BY sub.visit_last_action_time DESC + LIMIT 100 '; $expectedBind = array( '2', @@ -135,6 +137,7 @@ class ModelTest extends IntegrationTestCase ) AS sub GROUP BY sub.idvisit ORDER BY sub.visit_last_action_time DESC + LIMIT 100 '; $expectedBind = array( '1', @@ -175,12 +178,13 @@ class ModelTest extends IntegrationTestCase AND log_visit.visit_last_action_time <= ? ) AND ( log_link_visit_action.custom_var_k1 = ? ) ORDER BY idsite, visit_last_action_time DESC - LIMIT 10, 100 + LIMIT 10, 1000 ) AS log_inner ORDER BY idsite, visit_last_action_time DESC ) AS sub GROUP BY sub.idvisit ORDER BY sub.visit_last_action_time DESC + LIMIT 100 '; $expectedBind = array( '1', |