TODO: Face Recognition Integration.

[matiasdelellis]

Since Memories v4.9.2 we have an integration with the Face Recognition as alpha. Now just open this issue to track pending tasks, and handle any user expectations.

When I proposed this integration, we try to share as much code and dialogs as possible, in order to have a consistent workflow in both applications. There were some features that both apps work differently that we couldn't make them compatible for this stage and we hope to add them soon.

TODO:

• Fix "Merge with different Person". Today it shows the dialog correctly, but fails miserably. 😅 In the meantime, I recommend you assign name directly 😉. The fix is easy, but requires a "small" rewrite of the dialog.
• Implement "Move to another person".
• Implement "Remove from person". Issue Face Recognition integration: Remove wrong persons from cluster #283

Required api:

• "Merge with different Person"

  • PUT: /index.php/apps/facerecognition/api/2.0/cluster/317878?name=Name
  • PUT: /index.php/apps/facerecognition/api/2.0/person/Penny?name=Not+Penny
  • The service already exists await dav.renamePeopleFaceRecognition(oldName/clusterId, nameOfOtherPerson); but we have to adapt the dialog.

• "Move to another person":

  • PUT: /index.php/apps/facerecognition/api/2.0/face/{faceId}/detach?name=New+Name
  • It requires the id of the face, so you know exactly who you are moving. At the moment we do not have this info in the queries.

• "Remove from person"

  • PUT: /index.php/apps/facerecognition/api/2.0/face/{faceId}/detach
  • It requires the id of the face, so you know exactly who you are moving. At the moment we do not have this info in the queries.

p.s: I'm speaking as Face Recognition developer, and just trying to help here. 😉

[qudiqudi]

Really looking forward to see this working! 😃
So far I'm getting `Request failed with status code 500

docker
PostgreSQL 14.6
nextcloud 25.0.2
face recognition 0.9.10
memories 4.9.2

Here's the complete error log:

OC\DB\Exceptions\DbalException: An exception occurred while executing a query: SQLSTATE[42803]: Grouping error: 7 ERROR: column "frp.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 28: ) SELECT "frp"."id", "frp"."user" as "user_id", "frp"."n... ^

    /var/www/html/lib/private/DB/ConnectionAdapter.php - line 69:

    OC\DB\Exceptions\DbalException::wrap(Doctrine\DBA ... {})

    /var/www/html/custom_apps/memories/lib/Db/TimelineQueryDays.php - line 328:

    OC\DB\ConnectionAdapter->executeQuery("WITH RECURS ... C", [ 1,[ 312],false,true], [ 2,101,5,2])

    /var/www/html/custom_apps/memories/lib/Db/TimelineQueryPeopleFaceRecognition.php - line 111:

    OCA\Memories\Db\TimelineQuery->executeQueryWithCTEs(OC\DB\QueryB ... {})

    /var/www/html/custom_apps/memories/lib/Controller/PeopleController.php - line 133:

    OCA\Memories\Db\TimelineQuery->getPeopleFaceRecognition(OCA\Memories\Db\TimelineRoot {}, 1)

    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 225:

    OCA\Memories\Controller\PeopleController->facerecognitionPeople()

    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 133:

    OC\AppFramework\Http\Dispatcher->executeController(OCA\Memories ... }}, "facerecognitionPeople")

    /var/www/html/lib/private/AppFramework/App.php - line 172:

    OC\AppFramework\Http\Dispatcher->dispatch(OCA\Memories ... }}, "facerecognitionPeople")

    /var/www/html/lib/private/Route/Router.php - line 298:

    OC\AppFramework\App::main("OCA\\Memori ... r", "facerecognitionPeople", OC\AppFramew ... {}, [ "memories. ... "])

    /var/www/html/lib/base.php - line 1047:

    OC\Route\Router->match("/apps/memor ... e")

    /var/www/html/index.php - line 36:

    OC::handleRequest()

Verursacht durch Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[42803]: Grouping error: 7 ERROR: column "frp.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 28: ) SELECT "frp"."id", "frp"."user" as "user_id", "frp"."n... ^

    /var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1780:

    Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert(Doctrine\DBA ... {}, Doctrine\DBAL\Query {})

    /var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1719:

    Doctrine\DBAL\Connection->handleDriverException(Doctrine\DBA ... {}, Doctrine\DBAL\Query {})

    /var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1067:

    Doctrine\DBAL\Connection->convertExceptionDuringQuery(Doctrine\DBA ... {}, "WITH RECURS ... C", [ 312,1,true], [ 1,2,2])

    /var/www/html/lib/private/DB/Connection.php - line 261:

    Doctrine\DBAL\Connection->executeQuery("WITH RECURS ... C", [ 312,1,true], [ 1,2,2], null)

    /var/www/html/lib/private/DB/ConnectionAdapter.php - line 66:

    OC\DB\Connection->executeQuery("WITH RECURS ... C", [ 1,[ 312],false,true], [ 2,101,5,2])

    /var/www/html/custom_apps/memories/lib/Db/TimelineQueryDays.php - line 328:

    OC\DB\ConnectionAdapter->executeQuery("WITH RECURS ... C", [ 1,[ 312],false,true], [ 2,101,5,2])

    /var/www/html/custom_apps/memories/lib/Db/TimelineQueryPeopleFaceRecognition.php - line 111:

    OCA\Memories\Db\TimelineQuery->executeQueryWithCTEs(OC\DB\QueryB ... {})

    /var/www/html/custom_apps/memories/lib/Controller/PeopleController.php - line 133:

    OCA\Memories\Db\TimelineQuery->getPeopleFaceRecognition(OCA\Memories\Db\TimelineRoot {}, 1)

    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 225:

    OCA\Memories\Controller\PeopleController->facerecognitionPeople()

    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 133:

    OC\AppFramework\Http\Dispatcher->executeController(OCA\Memories ... }}, "facerecognitionPeople")

    /var/www/html/lib/private/AppFramework/App.php - line 172:

    OC\AppFramework\Http\Dispatcher->dispatch(OCA\Memories ... }}, "facerecognitionPeople")

    /var/www/html/lib/private/Route/Router.php - line 298:

    OC\AppFramework\App::main("OCA\\Memori ... r", "facerecognitionPeople", OC\AppFramew ... {}, [ "memories. ... "])

    /var/www/html/lib/base.php - line 1047:

    OC\Route\Router->match("/apps/memor ... e")

    /var/www/html/index.php - line 36:

    OC::handleRequest()

Verursacht durch Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[42803]: Grouping error: 7 ERROR: column "frp.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 28: ) SELECT "frp"."id", "frp"."user" as "user_id", "frp"."n... ^

    /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php - line 94:

    Doctrine\DBAL\Driver\PDO\Exception::new(PDOException ... ]})

    /var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1057:

    Doctrine\DBAL\Driver\PDO\Statement->execute()

    /var/www/html/lib/private/DB/Connection.php - line 261:

    Doctrine\DBAL\Connection->executeQuery("WITH RECURS ... C", [ 312,1,true], [ 1,2,2], null)

    /var/www/html/lib/private/DB/ConnectionAdapter.php - line 66:

    OC\DB\Connection->executeQuery("WITH RECURS ... C", [ 1,[ 312],false,true], [ 2,101,5,2])

    /var/www/html/custom_apps/memories/lib/Db/TimelineQueryDays.php - line 328:

    OC\DB\ConnectionAdapter->executeQuery("WITH RECURS ... C", [ 1,[ 312],false,true], [ 2,101,5,2])

    /var/www/html/custom_apps/memories/lib/Db/TimelineQueryPeopleFaceRecognition.php - line 111:

    OCA\Memories\Db\TimelineQuery->executeQueryWithCTEs(OC\DB\QueryB ... {})

    /var/www/html/custom_apps/memories/lib/Controller/PeopleController.php - line 133:

    OCA\Memories\Db\TimelineQuery->getPeopleFaceRecognition(OCA\Memories\Db\TimelineRoot {}, 1)

    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 225:

    OCA\Memories\Controller\PeopleController->facerecognitionPeople()

    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 133:

    OC\AppFramework\Http\Dispatcher->executeController(OCA\Memories ... }}, "facerecognitionPeople")

    /var/www/html/lib/private/AppFramework/App.php - line 172:

    OC\AppFramework\Http\Dispatcher->dispatch(OCA\Memories ... }}, "facerecognitionPeople")

    /var/www/html/lib/private/Route/Router.php - line 298:

    OC\AppFramework\App::main("OCA\\Memori ... r", "facerecognitionPeople", OC\AppFramew ... {}, [ "memories. ... "])

    /var/www/html/lib/base.php - line 1047:

    OC\Route\Router->match("/apps/memor ... e")

    /var/www/html/index.php - line 36:

    OC::handleRequest()

Verursacht durch PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR: column "frp.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 28: ) SELECT "frp"."id", "frp"."user" as "user_id", "frp"."n... ^

    /var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php - line 92:

    PDOStatement->execute(null)

    /var/www/html/3rdparty/doctrine/dbal/src/Connection.php - line 1057:

    Doctrine\DBAL\Driver\PDO\Statement->execute()

    /var/www/html/lib/private/DB/Connection.php - line 261:

    Doctrine\DBAL\Connection->executeQuery("WITH RECURS ... C", [ 312,1,true], [ 1,2,2], null)

    /var/www/html/lib/private/DB/ConnectionAdapter.php - line 66:

    OC\DB\Connection->executeQuery("WITH RECURS ... C", [ 1,[ 312],false,true], [ 2,101,5,2])

    /var/www/html/custom_apps/memories/lib/Db/TimelineQueryDays.php - line 328:

    OC\DB\ConnectionAdapter->executeQuery("WITH RECURS ... C", [ 1,[ 312],false,true], [ 2,101,5,2])

    /var/www/html/custom_apps/memories/lib/Db/TimelineQueryPeopleFaceRecognition.php - line 111:

    OCA\Memories\Db\TimelineQuery->executeQueryWithCTEs(OC\DB\QueryB ... {})

    /var/www/html/custom_apps/memories/lib/Controller/PeopleController.php - line 133:

    OCA\Memories\Db\TimelineQuery->getPeopleFaceRecognition(OCA\Memories\Db\TimelineRoot {}, 1)

    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 225:

    OCA\Memories\Controller\PeopleController->facerecognitionPeople()

    /var/www/html/lib/private/AppFramework/Http/Dispatcher.php - line 133:

    OC\AppFramework\Http\Dispatcher->executeController(OCA\Memories ... }}, "facerecognitionPeople")

    /var/www/html/lib/private/AppFramework/App.php - line 172:

    OC\AppFramework\Http\Dispatcher->dispatch(OCA\Memories ... }}, "facerecognitionPeople")

    /var/www/html/lib/private/Route/Router.php - line 298:

    OC\AppFramework\App::main("OCA\\Memori ... r", "facerecognitionPeople", OC\AppFramew ... {}, [ "memories. ... "])

    /var/www/html/lib/base.php - line 1047:

    OC\Route\Router->match("/apps/memor ... e")

    /var/www/html/index.php - line 36:

    OC::handleRequest()

[matiasdelellis]

Hi @qudiqudi
Thanks for reporting it... The report is very similar to #35

It seems to be a limitation of PostgreSQL.. Well, far from being a limitation, just mysql is more flexible, and same query seems to be confusing for PostgreSQL and rejects it. 😅

I can't reproduce it, but please try this. It is the only inconsistency that I see with the naked eye.

diff --git a/lib/Db/TimelineQueryPeopleFaceRecognition.php b/lib/Db/TimelineQueryPeopleFaceRecognition.php
index 2676b87..086a801 100644
--- a/lib/Db/TimelineQueryPeopleFaceRecognition.php
+++ b/lib/Db/TimelineQueryPeopleFaceRecognition.php
@@ -104,7 +104,7 @@ trait TimelineQueryPeopleFaceRecognition
 
         // ORDER by number of faces in cluster
         $query->orderBy('count', 'DESC');
-        $query->addOrderBy('name', 'ASC');
+        $query->addOrderBy('frp.name', 'ASC');
         $query->addOrderBy('frp.id'); // tie-breaker
 
         // FETCH all faces

[qudiqudi]

Hi @matiasdelellis, thanks for replying.
I changed these line by hand and restarted the container, but the error stays.

-        $query->addOrderBy('name', 'ASC');
+        $query->addOrderBy('frp.name', 'ASC');

[qudiqudi]

This is still an issue. As per Face Recognition 0.9.11 and Memories 4.9.3.

[Rudd-O]

A fix has already made it in, and I have tested it. A link to a prerelease artifact here: https://cloud.rudd-o.com/s/seiEZdAyZnkJzm7