#200 Autocomplete: two suggestions for the same account

Closed
opened 3 years ago by aab · 5 comments
aab commented 3 years ago

I guess it is not because of autocomplete, but i don't really know the origin of this; maybe the fix_duplicates script not removing duplicates of the same server where it is executed?

It works fine for remote accounts (EDIT: no, it doesn't, read below).

I guess it is not because of autocomplete, but i don't really know the origin of this; maybe the fix_duplicates script not removing duplicates of the same server where it is executed? It works fine for remote accounts (EDIT: no, it doesn't, read below).
XRevan86 commented 3 years ago
Collaborator

I've prepared a query that returns all remote profiles that have duplicates.

PostgreSQL:

SELECT ("profile"."nickname" || '@' || regexp_replace("profile"."profileurl", '^https?://([^/]+)/.*$', '\1')) COLLATE "C" AS "remote_profile",
    MAX("profile"."created") AS "most_recent",
    COUNT(*) AS "count"
  FROM "profile"
  LEFT JOIN "user" ON "profile"."id" = "user"."id"
  LEFT JOIN "user_group" ON "profile"."id" = "user_group"."profile_id"
  WHERE "profile"."nickname" <> ''
  AND "user"."id" IS NULL
  AND "user_group"."profile_id" IS NULL
  GROUP BY "remote_profile"
  HAVING COUNT(*) > 1
  ORDER BY "count" DESC;

MariaDB:

SELECT concat(`profile`.`nickname`, '@', regexp_substr(`profile`.`profileurl`, '(?:(?<=http://)|(?<=https://))([^/]+)')) AS `remote_profile`,
    MAX(`profile`.`created`) AS `most_recent`,
    COUNT(*) AS `count`
  FROM `profile`
  LEFT JOIN `user` ON `profile`.`id` = `user`.`id`
  LEFT JOIN `user_group` ON `profile`.`id` = `user_group`.`profile_id`
  WHERE `profile`.`nickname` <> ''
  AND `user`.`id` IS NULL
  AND `user_group`.`profile_id` IS NULL
  GROUP BY `remote_profile`
  HAVING COUNT(*) > 1
  ORDER BY `count` DESC;
I've prepared a query that returns all remote profiles that have duplicates. PostgreSQL: ```sql SELECT ("profile"."nickname" || '@' || regexp_replace("profile"."profileurl", '^https?://([^/]+)/.*$', '\1')) COLLATE "C" AS "remote_profile", MAX("profile"."created") AS "most_recent", COUNT(*) AS "count" FROM "profile" LEFT JOIN "user" ON "profile"."id" = "user"."id" LEFT JOIN "user_group" ON "profile"."id" = "user_group"."profile_id" WHERE "profile"."nickname" <> '' AND "user"."id" IS NULL AND "user_group"."profile_id" IS NULL GROUP BY "remote_profile" HAVING COUNT(*) > 1 ORDER BY "count" DESC; ``` MariaDB: ```sql SELECT concat(`profile`.`nickname`, '@', regexp_substr(`profile`.`profileurl`, '(?:(?<=http://)|(?<=https://))([^/]+)')) AS `remote_profile`, MAX(`profile`.`created`) AS `most_recent`, COUNT(*) AS `count` FROM `profile` LEFT JOIN `user` ON `profile`.`id` = `user`.`id` LEFT JOIN `user_group` ON `profile`.`id` = `user_group`.`profile_id` WHERE `profile`.`nickname` <> '' AND `user`.`id` IS NULL AND `user_group`.`profile_id` IS NULL GROUP BY `remote_profile` HAVING COUNT(*) > 1 ORDER BY `count` DESC; ```
aab commented 3 years ago
Poster

Using that query (thanks!) i've seen that executing some of OStatus scripts and fix_duplicates.php again improves the situation (from 73 to 29 repeated profiles) and the count (from 14 to 3). Anyway, it seems that if the remote node is down (or dead), there's no way to de-duplicate profiles:

FWIW: Unable to connect to tls://social.ingobernable.net:443. Error: Conexi?n rehusada

Using that query (thanks!) i've seen that executing some of OStatus scripts and fix_duplicates.php again improves the situation (from 73 to 29 repeated profiles) and the count (from 14 to 3). Anyway, it seems that if the remote node is down (or dead), there's no way to de-duplicate profiles: FWIW: Unable to connect to tls://social.ingobernable.net:443. Error: Conexi?n rehusada
aab commented 3 years ago
Poster

Ok, deleting from "profile" the ones already dead, and with some more cleaning, fix_duplicates.php returns nothing :)

I'm only seeing this now with local profiles, which kind of makes sense... or not? :)

Ok, deleting from "profile" the ones already dead, and with some more cleaning, fix_duplicates.php returns nothing :) I'm only seeing this now with local profiles, which kind of makes sense... or not? :)
Diogo Cordeiro commented 3 years ago
Owner

I'm only seeing this now with local profiles, which kind of makes sense... or not? :)

Duplicates?

> I'm only seeing this now with local profiles, which kind of makes sense... or not? :) Duplicates?
aab commented 3 years ago
Poster

I'm not seeing this behaviour anymore, i'd say it is fixed, even if XRevan86 query keeps giving some duplicate remote profiles.

I'm not seeing this behaviour anymore, i'd say it is fixed, even if XRevan86 query keeps giving some duplicate remote profiles.
Sign in to join this conversation.
No Milestone
No assignee
3 Participants
Loading...
Cancel
Save
There is no content yet.