src/Model/Contact/Relation.php

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