"Similar By Terms: Similarity" outputs invalid values like "1300%", "480%"

this patch corrects the count - it adds DISTINCT - but i don't know if that is MySQL specific or not. i also wonder if it is just masking some other issue, as i am not sure why i was getting duplicate node entries in the first place.

i am running 6.x-2.2. ah, i built it in the views directory, that's probably the problem. apply it specifically to similarterms/views/similarterms_handler_sort_similar.inc

i'll redo it and reupload soon.

I believe that adding DISTINCT will stop the sort from working at all. Having GROUPed multiple nids is kind of the whole trick to SBT2. If my understanding of SQL is correct (and it might not be), DISTINCT will effectively cancel out the GROUP function and all similar nodes will sort as 100% similar. It may appear that things are working, but you may end up with sortings which appear to be out of order.

For example:
100%
58%
78%
97%

I'll need to do some experimenting and research to see if my hunch is correct on this though.

But in short, my gut feeling is that adding DISTINCT is not the right fix here.

So much for my gut. The further I'm looking into this, the more DISTINCT looks like it might be the right way to go. Still need to do some more experimenting though.

i think you're right - i went off on the wrong track because my view's query was already doing a DISTINCT for other reasons.

but returning 200% is wrong, too. it may be that node IDs are the wrong things to be counting to begin with. the percentage math is comparing that count to $this->view->tids which is a count of the unique term IDs for the argument node(s). so maybe COUNT(DISTINCT term_node.tid) would be right. i have to do more testing to check that.

for the SQL, though, count(distinct field) runs within the grouping, just as count(field) does - it doesn't cancel it or override it, it just gives you a different result: within group A, there are X total values and Y distinct ones.

Subscribing, greetings, Martijn

I have the same issue, but not only is the percentage wrong (1000% give or take a hundred), only one similar term is displayed where there used to be several. In fact, there should be 5 similar terms showing for each node, but now there is only one, and it seems to be the same node for each node.

Since this is a production site, I'd be happy to downgrade, but don't know at what point this was last working correctly. Seems to me it was working fine a month ago.

Possible factors:
I am using the dev version of Views.
I recently upgraded to PHP 5.3

Suscribing, I have the same issue: only a single result, displaying similarities such as 1500%, 400%... !! Using the 6.x-2.x version.

EDIT: I'm also using views 3 . I tried downgrading to the versions 2.2, and 2.0-rc1, but couldn't get it to work.

I think this might simply come from an incompatibility with views 3... ?? An issue about that (http://drupal.org/node/1181566) was left unanswered.

I've found a temporary workaround: with views 3, enable the Group By option on the view, and create a field on nid grouped on SUM for instance. ("SUM(Node: Nid) Nid").
I then get all results and the right percentages.

I've found a temporary workaround: with views 3, enable the Group By option on the view, and create a field on nid grouped on SUM for instance. ("SUM(Node: Nid) Nid").
I then get all results and the right percentages.

Thanks fourmi4x, you're a genious! I have no idea why it works, but I've had this cool feature disabled for 6 months now and was happy to turn it back on.

> I think this might simply come from an incompatibility with views 3

Sounds right to me. Aggregate queries are completely different on Views 3.

Can anyone give examples of the node and term combinations that are producing the error? Can you whittle it down to a result set of maybe just one subject node and one result node so we can figure it out?

The multiple rows come from the join to {term_node} with a WHERE clause on multiple term tids, but there are never more tids than on the subject node -- and hence the number of duplicate rows will never be larger than that. Or at least in the cases I am considering!

> probably you'd get similar results with a relationship an any other multi-value field

Oh yes, that makes sense.

Suppose we have our subject node with terms X and Y, and we have these nodes to list:

A: X
B: Y
C: X, Y

The rows from the query without the grouping will be:
A
B
C
C

C appears twice because it has both terms and the join to term_node intentionally causes that. The COUNT then gives us:
A: 1
B: 1
C: 2

Percent match is calculated by taking the proportion of the terms on the initial node, and there were two (though see #1401164: documentation should explain similarity is not reflexive).

So we get:
A: 50%
B: 50%
C: 100%

Now if your query has other joins that cause say node A to get more rows based on something completely different, then yes, you're going to get totally wacky percentages. Say node A produces 6 rows in total. 6/2 is 3, so it will show 300%.

I'm afraid the only fix is probably 'don't do that', or 'make sure your relationship doesn't multiply rows'.

There might be some mileage in adding the satisfying term to the query results and then doing COUNT DISTINCT on it maybe? But it's MySQL-specific AFAICT and it's going to cause all sorts of pain come Views 3.

> this is only a workaround to mimic the behaviour of the DISTINCT operator from Views 2

This isn't about the DISTINCT operator that affects the whole query (ie, the 'SELECT DISTINCT ...').
This is a different beast.

Here's an illustration of the problem.

Suppose this is our result set *without* any aggregation (well, pseudo-result set; I'm using letters rather than numbers to make it easier to read).
We have two subject terms, X and Y, and three nodes in the result. The columns are nodes, terms, and an extra column from the join (the only important thing in that column is that there are different values).

+------+------+----------+
| node | term | extra |
+------+------+----------+
| A    | X    |        1 |
| A    | X    |        2 |
| A    | Y    |        3 |
| B    | X    |        4 |
| B    | Y    |        5 |
| C    | X    |        6 |
+------+------+----------+

- Node C produces only one row because it has only one term.
- Node B is correctly producing two rows because it has both terms.
- Node A has *three* rows because the extra join is giving us three different values for A (hmm or would that be 6? I'm using dummy data here rather than try to replicate the circumstances in my database). This is causing the result for node A to have *three* rows even though it only has *two* terms.

Here's the result set with aggregation:

+------+------+-------+-------------+----------------------+
| node | term | extra | COUNT(term) | COUNT(DISTINCT term) |
+------+------+-------+-------------+----------------------+
| A    | X    |     1 |           3 |                    2 |
| B    | X    |     4 |           2 |                    2 |
| C    | X    |     6 |           1 |                    1 |
+------+------+-------+-------------+----------------------+

The COUNT(term) column, which is what the module currently uses, is giving us the wrong answer for node A: similarity rating is 3, when there are only two terms!

The COUNT(DISTINCT term) column is counting how many distinct values for the term there are for this current aggregated row. In other words, 'How many different terms are listed for node A?'. This gives 2, which is the answer we want.

What this raises:

- what are the performance implications of switching from COUNT to COUNT(DISTINCT)? I'm sure it's slower. If it's significantly slower, we should probably keep the COUNT, and have an option in the argument handler to use COUNT(DISTINCT) instead.
- it's still not going to work in Views 3. This patch might help, but it needs testing and feedback: #1362524: add GROUP_CONCAT aggregate function; and move aggregate function helpers to a query extender?.
- it *looks like* this would work on Postgres: http://www.postgresql.org/docs/8.2/static/sql-expressions.html but what Postgres says it does and what it actually does... eh :/

This is still the case in the current 8.x version. Comparing to a second project where SbT delivers quite satisfying results while in my current project we have very nested hierarchical vocabularies, I reckon this might be related to a miscalculation of (counted in) term children.

If this is still an issue in the 8.x branch, create a new issue.