To answer the question: "Which users are using this profile picture?", as exploration from https://we.phorge.it/T16074#22591, it seems necessary to be able to easily interrogate the database table file_attachment.

This question translates to these two questions:

1. "Which users have this profile picture attached?"
   • possible speculation: there is only 1 row that has attachmentMode=attach about that profile picture, and that row is the user using that file as profile picture
   • pitfall: technically multiple other objects MAY be referenced or attached... since there is a manual attach workflow, e.g. if you mention that profile picture in a task comment, the file becomes referenced there; or if you then click on "Attach file" from the task curtain, that file becomes attached... etc. but it seems there is not a viable workflow to have a profile picture attached to other users (not even if you mention another profile picture from your user description: it does not appear an "Attach file" curtain), so, the above speculation seems very true.
2. "Which of these users are really using that profile picture?"

So, this task is about this first question. That question translates to something like this:

SELECT * FROM file_attachment
  WHERE filePHID = <$file_phid>
    AND objectPHID LIKE 'PHID-USER-%'
    AND attachmentMode = 'attach';

For performance reasons is useful to look at the database table definition:

CREATE TABLE `file_attachment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `objectPHID` varbinary(64) NOT NULL,
  `filePHID` varbinary(64) NOT NULL,
  `attacherPHID` varbinary(64) DEFAULT NULL,
  `attachmentMode` varchar(32) NOT NULL,
  `dateCreated` int(10) unsigned NOT NULL,
  `dateModified` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_object` (`objectPHID`,`filePHID`),
  KEY `key_file` (`filePHID`)
) ENGINE=InnoDB

Note that MariaDB is NOT able to use multiple indexes as far as I know, so it would basically just use the key_file index, and will not try to use the index that covers objectPHID.

So, if a profile picture is mentioned in 1 billion objects (why?) this could be a performance issue. But in normal conditions, the query cardinality is just 1. So, it has probably no sense to add an index here like on both of these in this order:

(`filePHID`, `objectPHID`)

So, I think we can just:

• add an easy way from PHP to run WHERE objectPHID LIKE 'PHID-USER-%'
• add an easy way from PHP to run WHERE attachmentMode = 'attach'

So, the goal of this task is to be able to do something like this:

$file_phid = 'PHID-FILE-123asd';
$file_attachments = id(new PhabricatorFileAttachmentQuery())
  ->withFilePHIDs(array($file_phid))
  ->withObjectPHIDPrefix('PHID-USER-') // ← currently not available
  ->withAttachmentMode('attach')       // ← currently not available
  ->execute();

So the database does its job, and we don't have to manually filter these things from PHP.

E.g. so we don't have to manually exclude what has not that objectPHID user PHID prefix, and exclude what has not that attachmentMode=attachment.