#200 Autocomplete: two suggestions for the same account

Open
opened 3 months ago by aab · 3 comments
aab commented 3 months 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 months 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 months 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 months 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? :)
Sign in to join this conversation.
No Milestone
No assignee
2 Participants
Loading...
Cancel
Save
There is no content yet.