src/Model/Contact/Relation.php

   1 <?php
   2 /**
   3  * @copyright Copyright (C) 2010-2022, the Friendica project
   4  *
   5  * @license GNU AGPL version 3 or any later version
   6  *
   7  * This program is free software: you can redistribute it and/or modify
   8  * it under the terms of the GNU Affero General Public License as
   9  * published by the Free Software Foundation, either version 3 of the
  10  * License, or (at your option) any later version.
  11  *
  12  * This program is distributed in the hope that it will be useful,
  13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15  * GNU Affero General Public License for more details.
  16  *
  17  * You should have received a copy of the GNU Affero General Public License
  18  * along with this program.  If not, see <https://www.gnu.org/licenses/>.
  19  *
  20  */
  21
  22 namespace Friendica\Model\Contact;
  23
  24 use Exception;
  25 use Friendica\Core\Logger;
  26 use Friendica\Core\Protocol;
  27 use Friendica\Database\Database;
  28 use Friendica\Database\DBA;
  29 use Friendica\DI;
  30 use Friendica\Model\APContact;
  31 use Friendica\Model\Contact;
  32 use Friendica\Model\Profile;
  33 use Friendica\Model\User;
  34 use Friendica\Protocol\ActivityPub;
  35 use Friendica\Util\DateTimeFormat;
  36 use Friendica\Util\Strings;
  37
  38 /**
  39  * This class provides relationship information based on the `contact-relation` table.
  40  * This table is directional (cid = source, relation-cid = target), references public contacts (with uid=0) and records both
  41  * follows and the last interaction (likes/comments) on public posts.
  42  */
  43 class Relation
  44 {
  45         /**
  46          * No discovery of followers/followings
  47          */
  48         const DISCOVERY_NONE = 0;
  49         /**
  50          * Discover followers/followings of local contacts
  51          */
  52         const DISCOVERY_LOCAL = 1;
  53         /**
  54          * Discover followers/followings of local contacts and contacts that visibly interacted on the system
  55          */
  56         const DISCOVERY_INTERACTOR = 2;
  57         /**
  58          * Discover followers/followings of all contacts
  59          */
  60         const DISCOVERY_ALL = 3;
  61
  62         public static function store(int $target, int $actor, string $interaction_date)
  63         {
  64                 if ($actor == $target) {
  65                         return;
  66                 }
  67
  68                 DBA::insert('contact-relation', ['last-interaction' => $interaction_date, 'cid' => $target, 'relation-cid' => $actor], Database::INSERT_UPDATE);
  69         }
  70
  71         /**
  72          * Fetches the followers of a given profile and adds them
  73          *
  74          * @param string $url URL of a profile
  75          * @return void
  76          */
  77         public static function discoverByUrl(string $url)
  78         {
  79                 $contact = Contact::getByURL($url);
  80                 if (empty($contact)) {
  81                         Logger::info('Contact not found', ['url' => $url]);
  82                         return;
  83                 }
  84
  85                 if (!self::isDiscoverable($url, $contact)) {
  86                         Logger::info('Contact is not discoverable', ['url' => $url]);
  87                         return;
  88                 }
  89
  90                 $uid = User::getIdForURL($url);
  91                 if (!empty($uid)) {
  92                         Logger::info('Fetch the followers/followings locally', ['url' => $url]);
  93                         $followers = self::getContacts($uid, [Contact::FOLLOWER, Contact::FRIEND]);
  94                         $followings = self::getContacts($uid, [Contact::SHARING, Contact::FRIEND]);
  95                 } elseif (!Contact::isLocal($url)) {
  96                         Logger::info('Fetch the followers/followings by polling the endpoints', ['url' => $url]);
  97                         $apcontact = APContact::getByURL($url, false);
  98
  99                         if (!empty($apcontact['followers']) && is_string($apcontact['followers'])) {
 100                                 $followers = ActivityPub::fetchItems($apcontact['followers']);
 101                         } else {
 102                                 $followers = [];
 103                         }
 104
 105                         if (!empty($apcontact['following']) && is_string($apcontact['following'])) {
 106                                 $followings = ActivityPub::fetchItems($apcontact['following']);
 107                         } else {
 108                                 $followings = [];
 109                         }
 110                 } else {
 111                         Logger::warning('Contact seems to be local but could not be found here', ['url' => $url]);
 112                         $followers = [];
 113                         $followings = [];
 114                 }
 115
 116                 if (empty($followers) && empty($followings)) {
 117                         Contact::update(['last-discovery' => DateTimeFormat::utcNow()], ['id' => $contact['id']]);
 118                         Logger::info('The contact does not offer discoverable data', ['id' => $contact['id'], 'url' => $url, 'network' => $contact['network']]);
 119                         return;
 120                 }
 121
 122                 $target = $contact['id'];
 123
 124                 if (!empty($followers)) {
 125                         // Clear the follower list, since it will be recreated in the next step
 126                         DBA::update('contact-relation', ['follows' => false], ['cid' => $target]);
 127                 }
 128
 129                 $contacts = [];
 130                 foreach (array_merge($followers, $followings) as $contact) {
 131                         if (is_string($contact)) {
 132                                 $contacts[] = $contact;
 133                         } elseif (!empty($contact['url']) && is_string($contact['url'])) {
 134                                 $contacts[] = $contact['url'];
 135                         }
 136                 }
 137                 $contacts = array_unique($contacts);
 138
 139                 $follower_counter = 0;
 140                 $following_counter = 0;
 141
 142                 Logger::info('Discover contacts', ['id' => $target, 'url' => $url, 'contacts' => count($contacts)]);
 143                 foreach ($contacts as $contact) {
 144                         $actor = Contact::getIdForURL($contact);
 145                         if (!empty($actor)) {
 146                                 if (in_array($contact, $followers)) {
 147                                         $fields = ['cid' => $target, 'relation-cid' => $actor, 'follows' => true, 'follow-updated' => DateTimeFormat::utcNow()];
 148                                         DBA::insert('contact-relation', $fields, Database::INSERT_UPDATE);
 149                                         $follower_counter++;
 150                                 }
 151
 152                                 if (in_array($contact, $followings)) {
 153                                         $fields = ['cid' => $actor, 'relation-cid' => $target, 'follows' => true, 'follow-updated' => DateTimeFormat::utcNow()];
 154                                         DBA::insert('contact-relation', $fields, Database::INSERT_UPDATE);
 155                                         $following_counter++;
 156                                 }
 157                         }
 158                 }
 159
 160                 if (!empty($followers)) {
 161                         // Delete all followers that aren't followers anymore (and aren't interacting)
 162                         DBA::delete('contact-relation', ['cid' => $target, 'follows' => false, 'last-interaction' => DBA::NULL_DATETIME]);
 163                 }
 164
 165                 Contact::update(['last-discovery' => DateTimeFormat::utcNow()], ['id' => $target]);
 166                 Logger::info('Contacts discovery finished', ['id' => $target, 'url' => $url, 'follower' => $follower_counter, 'following' => $following_counter]);
 167                 return;
 168         }
 169
 170         /**
 171          * Fetch contact url list from the given local user
 172          *
 173          * @param integer $uid
 174          * @param array $rel
 175          * @return array contact list
 176          */
 177         private static function getContacts(int $uid, array $rel): array
 178         {
 179                 $list = [];
 180                 $profile = Profile::getByUID($uid);
 181                 if (!empty($profile['hide-friends'])) {
 182                         return $list;
 183                 }
 184
 185                 $condition = [
 186                         'rel' => $rel,
 187                         'uid' => $uid,
 188                         'self' => false,
 189                         'deleted' => false,
 190                         'hidden' => false,
 191                         'archive' => false,
 192                         'pending' => false,
 193                 ];
 194                 $condition = DBA::mergeConditions($condition, ["`url` IN (SELECT `url` FROM `apcontact`)"]);
 195                 $contacts = DBA::select('contact', ['url'], $condition);
 196                 while ($contact = DBA::fetch($contacts)) {
 197                         $list[] = $contact['url'];
 198                 }
 199                 DBA::close($contacts);
 200
 201                 return $list;
 202         }
 203
 204         /**
 205          * Tests if a given contact url is discoverable
 206          *
 207          * @param string $url     Contact url
 208          * @param array  $contact Contact array
 209          * @return boolean True if contact is discoverable
 210          */
 211         public static function isDiscoverable(string $url, array $contact = []): bool
 212         {
 213                 $contact_discovery = DI::config()->get('system', 'contact_discovery');
 214
 215                 if ($contact_discovery == self::DISCOVERY_NONE) {
 216                         return false;
 217                 }
 218
 219                 if (empty($contact)) {
 220                         $contact = Contact::getByURL($url, false);
 221                 }
 222
 223                 if (empty($contact)) {
 224                         return false;
 225                 }
 226
 227                 if ($contact['last-discovery'] > DateTimeFormat::utc('now - 1 month')) {
 228                         Logger::info('No discovery - Last was less than a month ago.', ['id' => $contact['id'], 'url' => $url, 'discovery' => $contact['last-discovery']]);
 229                         return false;
 230                 }
 231
 232                 if ($contact_discovery != self::DISCOVERY_ALL) {
 233                         $local = DBA::exists('contact', ["`nurl` = ? AND `uid` != ?", Strings::normaliseLink($url), 0]);
 234                         if (($contact_discovery == self::DISCOVERY_LOCAL) && !$local) {
 235                                 Logger::info('No discovery - This contact is not followed/following locally.', ['id' => $contact['id'], 'url' => $url]);
 236                                 return false;
 237                         }
 238
 239                         if ($contact_discovery == self::DISCOVERY_INTERACTOR) {
 240                                 $interactor = DBA::exists('contact-relation', ["`relation-cid` = ? AND `last-interaction` > ?", $contact['id'], DBA::NULL_DATETIME]);
 241                                 if (!$local && !$interactor) {
 242                                         Logger::info('No discovery - This contact is not interacting locally.', ['id' => $contact['id'], 'url' => $url]);
 243                                         return false;
 244                                 }
 245                         }
 246                 } elseif ($contact['created'] > DateTimeFormat::utc('now - 1 day')) {
 247                         // Newly created contacts are not discovered to avoid DDoS attacks
 248                         Logger::info('No discovery - Contact record is less than a day old.', ['id' => $contact['id'], 'url' => $url, 'discovery' => $contact['created']]);
 249                         return false;
 250                 }
 251
 252                 if (!in_array($contact['network'], [Protocol::ACTIVITYPUB, Protocol::DFRN, Protocol::OSTATUS])) {
 253                         $apcontact = APContact::getByURL($url, false);
 254                         if (empty($apcontact)) {
 255                                 Logger::info('No discovery - The contact does not seem to speak ActivityPub.', ['id' => $contact['id'], 'url' => $url, 'network' => $contact['network']]);
 256                                 return false;
 257                         }
 258                 }
 259
 260                 return true;
 261         }
 262
 263         /**
 264          * Check if the cached suggestion is outdated
 265          *
 266          * @param integer $uid
 267          * @return boolean
 268          */
 269         static public function areSuggestionsOutdated(int $uid): bool
 270         {
 271                 return DI::pConfig()->get($uid, 'suggestion', 'last_update') + 3600 < time();
 272         }
 273
 274         /**
 275          * Update contact suggestions for a given user
 276          *
 277          * @param integer $uid
 278          * @return void
 279          */
 280         static public function updateCachedSuggestions(int $uid)
 281         {
 282                 if (!self::areSuggestionsOutdated($uid)) {
 283                         return;
 284                 }
 285
 286                 DBA::delete('account-suggestion', ['uid' => $uid, 'ignore' => false]);
 287
 288                 foreach (self::getSuggestions($uid) as $contact) {
 289                         DBA::insert('account-suggestion', ['uri-id' => $contact['uri-id'], 'uid' => $uid, 'level' => 1], Database::INSERT_IGNORE);
 290                 }
 291
 292                 DI::pConfig()->set($uid, 'suggestion', 'last_update', time());
 293         }
 294
 295         /**
 296          * Returns a cached array of suggested contacts for given user id
 297          *
 298          * @param int $uid   User id
 299          * @param int $start optional, default 0
 300          * @param int $limit optional, default 80
 301          * @return array
 302          */
 303         static public function getCachedSuggestions(int $uid, int $start = 0, int $limit = 80): array
 304         {
 305                 $condition = ["`uid` = ? AND `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE NOT `ignore` AND `uid` = ?)", 0, $uid];
 306                 $params = ['limit' => [$start, $limit]];
 307                 $cached = DBA::selectToArray('contact', [], $condition, $params);
 308
 309                 if (!empty($cached)) {
 310                         return $cached;
 311                 } else {
 312                         return self::getSuggestions($uid, $start, $limit);
 313                 }
 314         }
 315
 316         /**
 317          * Returns an array of suggested contacts for given user id
 318          *
 319          * @param int $uid   User id
 320          * @param int $start optional, default 0
 321          * @param int $limit optional, default 80
 322          * @return array
 323          */
 324         static public function getSuggestions(int $uid, int $start = 0, int $limit = 80): array
 325         {
 326                 if ($uid == 0) {
 327                         return [];
 328                 }
 329
 330                 $cid = Contact::getPublicIdByUserId($uid);
 331                 $totallimit = $start + $limit;
 332                 $contacts = [];
 333
 334                 Logger::info('Collecting suggestions', ['uid' => $uid, 'cid' => $cid, 'start' => $start, 'limit' => $limit]);
 335
 336                 $diaspora = DI::config()->get('system', 'diaspora_enabled') ? Protocol::DIASPORA : Protocol::ACTIVITYPUB;
 337                 $ostatus = !DI::config()->get('system', 'ostatus_disabled') ? Protocol::OSTATUS : Protocol::ACTIVITYPUB;
 338
 339                 // The query returns contacts where contacts interacted with whom the given user follows.
 340                 // Contacts who already are in the user's contact table are ignored.
 341                 $results = DBA::select('contact', [], ["`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` IN
 342                                 (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ?)
 343                                         AND NOT `cid` IN (SELECT `id` FROM `contact` WHERE `uid` = ? AND `nurl` IN
 344                                                 (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?))) AND `id` = `cid`)
 345                         AND NOT `hidden` AND `network` IN (?, ?, ?, ?)
 346                         AND NOT `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE `uri-id` = `contact`.`uri-id` AND `uid` = ?)",
 347                         $cid,
 348                         0,
 349                         $uid, Contact::FRIEND, Contact::SHARING,
 350                         Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus, $uid
 351                         ], [
 352                                 'order' => ['last-item' => true],
 353                                 'limit' => $totallimit,
 354                         ]
 355                 );
 356
 357                 while ($contact = DBA::fetch($results)) {
 358                         $contacts[$contact['id']] = $contact;
 359                 }
 360
 361                 DBA::close($results);
 362
 363                 Logger::info('Contacts of contacts who are followed by the given user', ['uid' => $uid, 'cid' => $cid, 'count' => count($contacts)]);
 364
 365                 if (count($contacts) >= $totallimit) {
 366                         return array_slice($contacts, $start, $limit);
 367                 }
 368
 369                 // The query returns contacts where contacts interacted with whom also interacted with the given user.
 370                 // Contacts who already are in the user's contact table are ignored.
 371                 $results = DBA::select('contact', [],
 372                         ["`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` IN
 373                                 (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ?)
 374                                         AND NOT `cid` IN (SELECT `id` FROM `contact` WHERE `uid` = ? AND `nurl` IN
 375                                                 (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?))) AND `id` = `cid`)
 376                         AND NOT `hidden` AND `network` IN (?, ?, ?, ?)
 377                         AND NOT `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE `uri-id` = `contact`.`uri-id` AND `uid` = ?)",
 378                         $cid, 0, $uid, Contact::FRIEND, Contact::SHARING,
 379                         Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus, $uid],
 380                         ['order' => ['last-item' => true], 'limit' => $totallimit]
 381                 );
 382
 383                 while ($contact = DBA::fetch($results)) {
 384                         $contacts[$contact['id']] = $contact;
 385                 }
 386                 DBA::close($results);
 387
 388                 Logger::info('Contacts of contacts who are following the given user', ['uid' => $uid, 'cid' => $cid, 'count' => count($contacts)]);
 389
 390                 if (count($contacts) >= $totallimit) {
 391                         return array_slice($contacts, $start, $limit);
 392                 }
 393
 394                 // The query returns contacts that follow the given user but aren't followed by that user.
 395                 $results = DBA::select('contact', [],
 396                         ["`nurl` IN (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` = ?)
 397                         AND NOT `hidden` AND `uid` = ? AND `network` IN (?, ?, ?, ?)
 398                         AND NOT `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE `uri-id` = `contact`.`uri-id` AND `uid` = ?)",
 399                         $uid, Contact::FOLLOWER, 0,
 400                         Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus, $uid],
 401                         ['order' => ['last-item' => true], 'limit' => $totallimit]
 402                 );
 403
 404                 while ($contact = DBA::fetch($results)) {
 405                         $contacts[$contact['id']] = $contact;
 406                 }
 407                 DBA::close($results);
 408
 409                 Logger::info('Followers that are not followed by the given user', ['uid' => $uid, 'cid' => $cid, 'count' => count($contacts)]);
 410
 411                 if (count($contacts) >= $totallimit) {
 412                         return array_slice($contacts, $start, $limit);
 413                 }
 414
 415                 // The query returns any contact that isn't followed by that user.
 416                 $results = DBA::select('contact', [],
 417                         ["NOT `nurl` IN (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?) AND `nurl` = `nurl`)
 418                         AND NOT `hidden` AND `uid` = ? AND `network` IN (?, ?, ?, ?)
 419                         AND NOT `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE `uri-id` = `contact`.`uri-id` AND `uid` = ?)",
 420                         $uid, Contact::FRIEND, Contact::SHARING, 0,
 421                         Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus, $uid],
 422                         ['order' => ['last-item' => true], 'limit' => $totallimit]
 423                 );
 424
 425                 while ($contact = DBA::fetch($results)) {
 426                         $contacts[$contact['id']] = $contact;
 427                 }
 428                 DBA::close($results);
 429
 430                 Logger::info('Any contact', ['uid' => $uid, 'cid' => $cid, 'count' => count($contacts)]);
 431
 432                 return array_slice($contacts, $start, $limit);
 433         }
 434
 435         /**
 436          * Counts all the known follows of the provided public contact
 437          *
 438          * @param int   $cid       Public contact id
 439          * @param array $condition Additional condition on the contact table
 440          * @return int
 441          * @throws Exception
 442          */
 443         public static function countFollows(int $cid, array $condition = []): int
 444         {
 445                 $condition = DBA::mergeConditions($condition, [
 446                         '`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)',
 447                         $cid,
 448                 ]);
 449
 450                 return DI::dba()->count('contact', $condition);
 451         }
 452
 453         /**
 454          * Returns a paginated list of contacts that are followed the provided public contact.
 455          *
 456          * @param int   $cid       Public contact id
 457          * @param array $condition Additional condition on the contact table
 458          * @param int   $count
 459          * @param int   $offset
 460          * @param bool  $shuffle
 461          * @return array
 462          * @throws Exception
 463          */
 464         public static function listFollows(int $cid, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
 465         {
 466                 $condition = DBA::mergeConditions($condition,
 467                         ['`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)',
 468                         $cid]
 469                 );
 470
 471                 return DI::dba()->selectToArray('contact', [], $condition,
 472                         ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
 473                 );
 474         }
 475
 476         /**
 477          * Counts all the known followers of the provided public contact
 478          *
 479          * @param int   $cid       Public contact id
 480          * @param array $condition Additional condition on the contact table
 481          * @return int
 482          * @throws Exception
 483          */
 484         public static function countFollowers(int $cid, array $condition = [])
 485         {
 486                 $condition = DBA::mergeConditions($condition,
 487                         ['`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)',
 488                         $cid]
 489                 );
 490
 491                 return DI::dba()->count('contact', $condition);
 492         }
 493
 494         /**
 495          * Returns a paginated list of contacts that follow the provided public contact.
 496          *
 497          * @param int   $cid       Public contact id
 498          * @param array $condition Additional condition on the contact table
 499          * @param int   $count
 500          * @param int   $offset
 501          * @param bool  $shuffle
 502          * @return array
 503          * @throws Exception
 504          */
 505         public static function listFollowers(int $cid, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
 506         {
 507                 $condition = DBA::mergeConditions($condition,
 508                         ['`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)', $cid]
 509                 );
 510
 511                 return DI::dba()->selectToArray('contact', [], $condition,
 512                         ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
 513                 );
 514         }
 515
 516         /**
 517          * Counts the number of contacts that are known mutuals with the provided public contact.
 518          *
 519          * @param int   $cid       Public contact id
 520          * @param array $condition Additional condition array on the contact table
 521          * @return int
 522          * @throws Exception
 523          */
 524         public static function countMutuals(int $cid, array $condition = [])
 525         {
 526                 $condition = DBA::mergeConditions($condition,
 527                         ['`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)
 528                         AND `id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)',
 529                         $cid, $cid]
 530                 );
 531
 532                 return DI::dba()->count('contact', $condition);
 533         }
 534
 535         /**
 536          * Returns a paginated list of contacts that are known mutuals with the provided public contact.
 537          *
 538          * @param int   $cid       Public contact id
 539          * @param array $condition Additional condition on the contact table
 540          * @param int   $count
 541          * @param int   $offset
 542          * @param bool  $shuffle
 543          * @return array
 544          * @throws Exception
 545          */
 546         public static function listMutuals(int $cid, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
 547         {
 548                 $condition = DBA::mergeConditions($condition,
 549                         ['`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)
 550                         AND `id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)',
 551                         $cid, $cid]
 552                 );
 553
 554                 return DI::dba()->selectToArray('contact', [], $condition,
 555                         ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
 556                 );
 557         }
 558
 559
 560         /**
 561          * Counts the number of contacts with any relationship with the provided public contact.
 562          *
 563          * @param int   $cid       Public contact id
 564          * @param array $condition Additional condition array on the contact table
 565          * @return int
 566          * @throws Exception
 567          */
 568         public static function countAll(int $cid, array $condition = [])
 569         {
 570                 $condition = DBA::mergeConditions($condition,
 571                         ['(`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)
 572                         OR `id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`))',
 573                                 $cid, $cid]
 574                 );
 575
 576                 return DI::dba()->count('contact', $condition);
 577         }
 578
 579         /**
 580          * Returns a paginated list of contacts with any relationship with the provided public contact.
 581          *
 582          * @param int   $cid       Public contact id
 583          * @param array $condition Additional condition on the contact table
 584          * @param int   $count
 585          * @param int   $offset
 586          * @param bool  $shuffle
 587          * @return array
 588          * @throws Exception
 589          */
 590         public static function listAll(int $cid, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
 591         {
 592                 $condition = DBA::mergeConditions($condition,
 593                         ['(`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)
 594                         OR `id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`))',
 595                                 $cid, $cid]
 596                 );
 597
 598                 return DI::dba()->selectToArray('contact', [], $condition,
 599                         ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
 600                 );
 601         }
 602
 603         /**
 604          * Counts the number of contacts that both provided public contacts have interacted with at least once.
 605          * Interactions include follows and likes and comments on public posts.
 606          *
 607          * @param int   $sourceId  Public contact id
 608          * @param int   $targetId  Public contact id
 609          * @param array $condition Additional condition array on the contact table
 610          * @return int
 611          * @throws Exception
 612          */
 613         public static function countCommon(int $sourceId, int $targetId, array $condition = [])
 614         {
 615                 $condition = DBA::mergeConditions($condition,
 616                         ['`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ?)
 617                         AND `id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ?)',
 618                         $sourceId, $targetId]
 619                 );
 620
 621                 return DI::dba()->count('contact', $condition);
 622         }
 623
 624         /**
 625          * Returns a paginated list of contacts that both provided public contacts have interacted with at least once.
 626          * Interactions include follows and likes and comments on public posts.
 627          *
 628          * @param int   $sourceId  Public contact id
 629          * @param int   $targetId  Public contact id
 630          * @param array $condition Additional condition on the contact table
 631          * @param int   $count
 632          * @param int   $offset
 633          * @param bool  $shuffle
 634          * @return array|bool Array on success, false on failure
 635          * @throws Exception
 636          */
 637         public static function listCommon(int $sourceId, int $targetId, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
 638         {
 639                 $condition = DBA::mergeConditions($condition,
 640                         ["`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ?)
 641                         AND `id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ?)",
 642                         $sourceId, $targetId]
 643                 );
 644
 645                 return DI::dba()->selectToArray('contact', [], $condition,
 646                         ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
 647                 );
 648         }
 649
 650         /**
 651          * Counts the number of contacts that are followed by both provided public contacts.
 652          *
 653          * @param int   $sourceId  Public contact id
 654          * @param int   $targetId  Public contact id
 655          * @param array $condition Additional condition array on the contact table
 656          * @return int
 657          * @throws Exception
 658          */
 659         public static function countCommonFollows(int $sourceId, int $targetId, array $condition = []): int
 660         {
 661                 $condition = DBA::mergeConditions($condition,
 662                         ['`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)
 663                         AND `id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)',
 664                         $sourceId, $targetId]
 665                 );
 666
 667                 return DI::dba()->count('contact', $condition);
 668         }
 669
 670         /**
 671          * Returns a paginated list of contacts that are followed by both provided public contacts.
 672          *
 673          * @param int   $sourceId  Public contact id
 674          * @param int   $targetId  Public contact id
 675          * @param array $condition Additional condition array on the contact table
 676          * @param int   $count
 677          * @param int   $offset
 678          * @param bool  $shuffle
 679          * @return array|bool Array on success, false on failure
 680          * @throws Exception
 681          */
 682         public static function listCommonFollows(int $sourceId, int $targetId, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
 683         {
 684                 $condition = DBA::mergeConditions($condition,
 685                         ["`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)
 686                         AND `id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)",
 687                         $sourceId, $targetId]
 688                 );
 689
 690                 return DI::dba()->selectToArray('contact', [], $condition,
 691                         ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
 692                 );
 693         }
 694
 695         /**
 696          * Counts the number of contacts that follow both provided public contacts.
 697          *
 698          * @param int   $sourceId  Public contact id
 699          * @param int   $targetId  Public contact id
 700          * @param array $condition Additional condition on the contact table
 701          * @return int
 702          * @throws Exception
 703          */
 704         public static function countCommonFollowers(int $sourceId, int $targetId, array $condition = []): int
 705         {
 706                 $condition = DBA::mergeConditions($condition,
 707                         ["`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)
 708                         AND `id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)",
 709                         $sourceId, $targetId]
 710                 );
 711
 712                 return DI::dba()->count('contact', $condition);
 713         }
 714
 715         /**
 716          * Returns a paginated list of contacts that follow both provided public contacts.
 717          *
 718          * @param int   $sourceId  Public contact id
 719          * @param int   $targetId  Public contact id
 720          * @param array $condition Additional condition on the contact table
 721          * @param int   $count
 722          * @param int   $offset
 723          * @param bool  $shuffle
 724          * @return array|bool Array on success, false on failure
 725          * @throws Exception
 726          */
 727         public static function listCommonFollowers(int $sourceId, int $targetId, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
 728         {
 729                 $condition = DBA::mergeConditions($condition,
 730                         ["`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)
 731                         AND `id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)",
 732                         $sourceId, $targetId]
 733                 );
 734
 735                 return DI::dba()->selectToArray('contact', [], $condition,
 736                         ['limit' => [$offset, $count],  'order' => [$shuffle ? 'RAND()' : 'name']]
 737                 );
 738         }
 739 }