]> git.mxchange.org Git - friendica.git/blob - src/Model/Contact/Relation.php
94b5f63ffd423e9ec7424a68ad21acca2532a17b
[friendica.git] / 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_discovery == self::DISCOVERY_NONE) {
252                         return false;
253                 }
254
255                 if (empty($contact)) {
256                         $contact = Contact::getByURL($url, false);
257                 }
258
259                 if (empty($contact)) {
260                         return false;
261                 }
262
263                 if ($contact['last-discovery'] > DateTimeFormat::utc('now - 1 month')) {
264                         Logger::info('No discovery - Last was less than a month ago.', ['id' => $contact['id'], 'url' => $url, 'discovery' => $contact['last-discovery']]);
265                         return false;
266                 }
267
268                 if ($contact_discovery != self::DISCOVERY_ALL) {
269                         $local = DBA::exists('contact', ["`nurl` = ? AND `uid` != ?", Strings::normaliseLink($url), 0]);
270                         if (($contact_discovery == self::DISCOVERY_LOCAL) && !$local) {
271                                 Logger::info('No discovery - This contact is not followed/following locally.', ['id' => $contact['id'], 'url' => $url]);
272                                 return false;
273                         }
274
275                         if ($contact_discovery == self::DISCOVERY_INTERACTOR) {
276                                 $interactor = DBA::exists('contact-relation', ["`relation-cid` = ? AND `last-interaction` > ?", $contact['id'], DBA::NULL_DATETIME]);
277                                 if (!$local && !$interactor) {
278                                         Logger::info('No discovery - This contact is not interacting locally.', ['id' => $contact['id'], 'url' => $url]);
279                                         return false;
280                                 }
281                         }
282                 } elseif ($contact['created'] > DateTimeFormat::utc('now - 1 day')) {
283                         // Newly created contacts are not discovered to avoid DDoS attacks
284                         Logger::info('No discovery - Contact record is less than a day old.', ['id' => $contact['id'], 'url' => $url, 'discovery' => $contact['created']]);
285                         return false;
286                 }
287
288                 if (!in_array($contact['network'], [Protocol::ACTIVITYPUB, Protocol::DFRN, Protocol::OSTATUS])) {
289                         $apcontact = APContact::getByURL($url, false);
290                         if (empty($apcontact)) {
291                                 Logger::info('No discovery - The contact does not seem to speak ActivityPub.', ['id' => $contact['id'], 'url' => $url, 'network' => $contact['network']]);
292                                 return false;
293                         }
294                 }
295
296                 return true;
297         }
298
299         /**
300          * Check if the cached suggestion is outdated
301          *
302          * @param integer $uid
303          * @return boolean
304          */
305         static public function areSuggestionsOutdated(int $uid): bool
306         {
307                 return DI::pConfig()->get($uid, 'suggestion', 'last_update') + 3600 < time();
308         }
309
310         /**
311          * Update contact suggestions for a given user
312          *
313          * @param integer $uid
314          * @return void
315          */
316         static public function updateCachedSuggestions(int $uid)
317         {
318                 if (!self::areSuggestionsOutdated($uid)) {
319                         return;
320                 }
321
322                 DBA::delete('account-suggestion', ['uid' => $uid, 'ignore' => false]);
323
324                 foreach (self::getSuggestions($uid) as $contact) {
325                         DBA::insert('account-suggestion', ['uri-id' => $contact['uri-id'], 'uid' => $uid, 'level' => 1], Database::INSERT_IGNORE);
326                 }
327
328                 DI::pConfig()->set($uid, 'suggestion', 'last_update', time());
329         }
330
331         /**
332          * Returns a cached array of suggested contacts for given user id
333          *
334          * @param int $uid   User id
335          * @param int $start optional, default 0
336          * @param int $limit optional, default 80
337          * @return array
338          */
339         static public function getCachedSuggestions(int $uid, int $start = 0, int $limit = 80): array
340         {
341                 $condition = ["`uid` = ? AND `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE NOT `ignore` AND `uid` = ?)", 0, $uid];
342                 $params = ['limit' => [$start, $limit]];
343                 $cached = DBA::selectToArray('contact', [], $condition, $params);
344
345                 if (!empty($cached)) {
346                         return $cached;
347                 } else {
348                         return self::getSuggestions($uid, $start, $limit);
349                 }
350         }
351
352         /**
353          * Returns an array of suggested contacts for given user id
354          *
355          * @param int $uid   User id
356          * @param int $start optional, default 0
357          * @param int $limit optional, default 80
358          * @return array
359          */
360         static public function getSuggestions(int $uid, int $start = 0, int $limit = 80): array
361         {
362                 if ($uid == 0) {
363                         return [];
364                 }
365
366                 $cid = Contact::getPublicIdByUserId($uid);
367                 $totallimit = $start + $limit;
368                 $contacts = [];
369
370                 Logger::info('Collecting suggestions', ['uid' => $uid, 'cid' => $cid, 'start' => $start, 'limit' => $limit]);
371
372                 $diaspora = DI::config()->get('system', 'diaspora_enabled') ? Protocol::DIASPORA : Protocol::ACTIVITYPUB;
373                 $ostatus = !DI::config()->get('system', 'ostatus_disabled') ? Protocol::OSTATUS : Protocol::ACTIVITYPUB;
374
375                 // The query returns contacts where contacts interacted with whom the given user follows.
376                 // Contacts who already are in the user's contact table are ignored.
377                 $results = DBA::select('contact', [], ["`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` IN
378                                 (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ?)
379                                         AND NOT `cid` IN (SELECT `id` FROM `contact` WHERE `uid` = ? AND `nurl` IN
380                                                 (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?))) AND `id` = `cid`)
381                         AND NOT `hidden` AND `network` IN (?, ?, ?, ?)
382                         AND NOT `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE `uri-id` = `contact`.`uri-id` AND `uid` = ?)",
383                         $cid,
384                         0,
385                         $uid, Contact::FRIEND, Contact::SHARING,
386                         Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus, $uid
387                         ], [
388                                 'order' => ['last-item' => true],
389                                 'limit' => $totallimit,
390                         ]
391                 );
392
393                 while ($contact = DBA::fetch($results)) {
394                         $contacts[$contact['id']] = $contact;
395                 }
396
397                 DBA::close($results);
398
399                 Logger::info('Contacts of contacts who are followed by the given user', ['uid' => $uid, 'cid' => $cid, 'count' => count($contacts)]);
400
401                 if (count($contacts) >= $totallimit) {
402                         return array_slice($contacts, $start, $limit);
403                 }
404
405                 // The query returns contacts where contacts interacted with whom also interacted with the given user.
406                 // Contacts who already are in the user's contact table are ignored.
407                 $results = DBA::select('contact', [],
408                         ["`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` IN
409                                 (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ?)
410                                         AND NOT `cid` IN (SELECT `id` FROM `contact` WHERE `uid` = ? AND `nurl` IN
411                                                 (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?))) AND `id` = `cid`)
412                         AND NOT `hidden` AND `network` IN (?, ?, ?, ?)
413                         AND NOT `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE `uri-id` = `contact`.`uri-id` AND `uid` = ?)",
414                         $cid, 0, $uid, Contact::FRIEND, Contact::SHARING,
415                         Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus, $uid],
416                         ['order' => ['last-item' => true], 'limit' => $totallimit]
417                 );
418
419                 while ($contact = DBA::fetch($results)) {
420                         $contacts[$contact['id']] = $contact;
421                 }
422                 DBA::close($results);
423
424                 Logger::info('Contacts of contacts who are following the given user', ['uid' => $uid, 'cid' => $cid, 'count' => count($contacts)]);
425
426                 if (count($contacts) >= $totallimit) {
427                         return array_slice($contacts, $start, $limit);
428                 }
429
430                 // The query returns contacts that follow the given user but aren't followed by that user.
431                 $results = DBA::select('contact', [],
432                         ["`nurl` IN (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` = ?)
433                         AND NOT `hidden` AND `uid` = ? AND `network` IN (?, ?, ?, ?)
434                         AND NOT `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE `uri-id` = `contact`.`uri-id` AND `uid` = ?)",
435                         $uid, Contact::FOLLOWER, 0,
436                         Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus, $uid],
437                         ['order' => ['last-item' => true], 'limit' => $totallimit]
438                 );
439
440                 while ($contact = DBA::fetch($results)) {
441                         $contacts[$contact['id']] = $contact;
442                 }
443                 DBA::close($results);
444
445                 Logger::info('Followers that are not followed by the given user', ['uid' => $uid, 'cid' => $cid, 'count' => count($contacts)]);
446
447                 if (count($contacts) >= $totallimit) {
448                         return array_slice($contacts, $start, $limit);
449                 }
450
451                 // The query returns any contact that isn't followed by that user.
452                 $results = DBA::select('contact', [],
453                         ["NOT `nurl` IN (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?) AND `nurl` = `nurl`)
454                         AND NOT `hidden` AND `uid` = ? AND `network` IN (?, ?, ?, ?)
455                         AND NOT `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE `uri-id` = `contact`.`uri-id` AND `uid` = ?)",
456                         $uid, Contact::FRIEND, Contact::SHARING, 0,
457                         Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus, $uid],
458                         ['order' => ['last-item' => true], 'limit' => $totallimit]
459                 );
460
461                 while ($contact = DBA::fetch($results)) {
462                         $contacts[$contact['id']] = $contact;
463                 }
464                 DBA::close($results);
465
466                 Logger::info('Any contact', ['uid' => $uid, 'cid' => $cid, 'count' => count($contacts)]);
467
468                 return array_slice($contacts, $start, $limit);
469         }
470
471         /**
472          * Counts all the known follows of the provided public contact
473          *
474          * @param int   $cid       Public contact id
475          * @param array $condition Additional condition on the contact table
476          * @return int
477          * @throws Exception
478          */
479         public static function countFollows(int $cid, array $condition = []): int
480         {
481                 $condition = DBA::mergeConditions($condition, [
482                         '`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)',
483                         $cid,
484                 ]);
485
486                 return DI::dba()->count('contact', $condition);
487         }
488
489         /**
490          * Returns a paginated list of contacts that are followed the provided public contact.
491          *
492          * @param int   $cid       Public contact id
493          * @param array $condition Additional condition on the contact table
494          * @param int   $count
495          * @param int   $offset
496          * @param bool  $shuffle
497          * @return array
498          * @throws Exception
499          */
500         public static function listFollows(int $cid, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
501         {
502                 $condition = DBA::mergeConditions($condition,
503                         ['`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)',
504                         $cid]
505                 );
506
507                 return DI::dba()->selectToArray('contact', [], $condition,
508                         ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
509                 );
510         }
511
512         /**
513          * Counts all the known followers of the provided public contact
514          *
515          * @param int   $cid       Public contact id
516          * @param array $condition Additional condition on the contact table
517          * @return int
518          * @throws Exception
519          */
520         public static function countFollowers(int $cid, array $condition = [])
521         {
522                 $condition = DBA::mergeConditions($condition,
523                         ['`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)',
524                         $cid]
525                 );
526
527                 return DI::dba()->count('contact', $condition);
528         }
529
530         /**
531          * Returns a paginated list of contacts that follow the provided public contact.
532          *
533          * @param int   $cid       Public contact id
534          * @param array $condition Additional condition on the contact table
535          * @param int   $count
536          * @param int   $offset
537          * @param bool  $shuffle
538          * @return array
539          * @throws Exception
540          */
541         public static function listFollowers(int $cid, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
542         {
543                 $condition = DBA::mergeConditions($condition,
544                         ['`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)', $cid]
545                 );
546
547                 return DI::dba()->selectToArray('contact', [], $condition,
548                         ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
549                 );
550         }
551
552         /**
553          * Counts the number of contacts that are known mutuals with the provided public contact.
554          *
555          * @param int   $cid       Public contact id
556          * @param array $condition Additional condition array on the contact table
557          * @return int
558          * @throws Exception
559          */
560         public static function countMutuals(int $cid, array $condition = [])
561         {
562                 $condition = DBA::mergeConditions($condition,
563                         ['`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`) 
564                         AND `id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)',
565                         $cid, $cid]
566                 );
567
568                 return DI::dba()->count('contact', $condition);
569         }
570
571         /**
572          * Returns a paginated list of contacts that are known mutuals with the provided public contact.
573          *
574          * @param int   $cid       Public contact id
575          * @param array $condition Additional condition on the contact table
576          * @param int   $count
577          * @param int   $offset
578          * @param bool  $shuffle
579          * @return array
580          * @throws Exception
581          */
582         public static function listMutuals(int $cid, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
583         {
584                 $condition = DBA::mergeConditions($condition,
585                         ['`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`) 
586                         AND `id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)',
587                         $cid, $cid]
588                 );
589
590                 return DI::dba()->selectToArray('contact', [], $condition,
591                         ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
592                 );
593         }
594
595
596         /**
597          * Counts the number of contacts with any relationship with the provided public contact.
598          *
599          * @param int   $cid       Public contact id
600          * @param array $condition Additional condition array on the contact table
601          * @return int
602          * @throws Exception
603          */
604         public static function countAll(int $cid, array $condition = [])
605         {
606                 $condition = DBA::mergeConditions($condition,
607                         ['(`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`) 
608                         OR `id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`))',
609                                 $cid, $cid]
610                 );
611
612                 return DI::dba()->count('contact', $condition);
613         }
614
615         /**
616          * Returns a paginated list of contacts with any relationship with the provided public contact.
617          *
618          * @param int   $cid       Public contact id
619          * @param array $condition Additional condition on the contact table
620          * @param int   $count
621          * @param int   $offset
622          * @param bool  $shuffle
623          * @return array
624          * @throws Exception
625          */
626         public static function listAll(int $cid, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
627         {
628                 $condition = DBA::mergeConditions($condition,
629                         ['(`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`) 
630                         OR `id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`))',
631                                 $cid, $cid]
632                 );
633
634                 return DI::dba()->selectToArray('contact', [], $condition,
635                         ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
636                 );
637         }
638
639         /**
640          * Counts the number of contacts that both provided public contacts have interacted with at least once.
641          * Interactions include follows and likes and comments on public posts.
642          *
643          * @param int   $sourceId  Public contact id
644          * @param int   $targetId  Public contact id
645          * @param array $condition Additional condition array on the contact table
646          * @return int
647          * @throws Exception
648          */
649         public static function countCommon(int $sourceId, int $targetId, array $condition = [])
650         {
651                 $condition = DBA::mergeConditions($condition,
652                         ['`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ?) 
653                         AND `id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ?)',
654                         $sourceId, $targetId]
655                 );
656
657                 return DI::dba()->count('contact', $condition);
658         }
659
660         /**
661          * Returns a paginated list of contacts that both provided public contacts have interacted with at least once.
662          * Interactions include follows and likes and comments on public posts.
663          *
664          * @param int   $sourceId  Public contact id
665          * @param int   $targetId  Public contact id
666          * @param array $condition Additional condition on the contact table
667          * @param int   $count
668          * @param int   $offset
669          * @param bool  $shuffle
670          * @return array|bool Array on success, false on failure
671          * @throws Exception
672          */
673         public static function listCommon(int $sourceId, int $targetId, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
674         {
675                 $condition = DBA::mergeConditions($condition,
676                         ["`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ?) 
677                         AND `id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ?)",
678                         $sourceId, $targetId]
679                 );
680
681                 return DI::dba()->selectToArray('contact', [], $condition,
682                         ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
683                 );
684         }
685
686         /**
687          * Counts the number of contacts that are followed by both provided public contacts.
688          *
689          * @param int   $sourceId  Public contact id
690          * @param int   $targetId  Public contact id
691          * @param array $condition Additional condition array on the contact table
692          * @return int
693          * @throws Exception
694          */
695         public static function countCommonFollows(int $sourceId, int $targetId, array $condition = []): int
696         {
697                 $condition = DBA::mergeConditions($condition,
698                         ['`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`) 
699                         AND `id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)',
700                         $sourceId, $targetId]
701                 );
702
703                 return DI::dba()->count('contact', $condition);
704         }
705
706         /**
707          * Returns a paginated list of contacts that are followed by both provided public contacts.
708          *
709          * @param int   $sourceId  Public contact id
710          * @param int   $targetId  Public contact id
711          * @param array $condition Additional condition array on the contact table
712          * @param int   $count
713          * @param int   $offset
714          * @param bool  $shuffle
715          * @return array|bool Array on success, false on failure
716          * @throws Exception
717          */
718         public static function listCommonFollows(int $sourceId, int $targetId, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
719         {
720                 $condition = DBA::mergeConditions($condition,
721                         ["`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`) 
722                         AND `id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)",
723                         $sourceId, $targetId]
724                 );
725
726                 return DI::dba()->selectToArray('contact', [], $condition,
727                         ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
728                 );
729         }
730
731         /**
732          * Counts the number of contacts that follow both provided public contacts.
733          *
734          * @param int   $sourceId  Public contact id
735          * @param int   $targetId  Public contact id
736          * @param array $condition Additional condition on the contact table
737          * @return int
738          * @throws Exception
739          */
740         public static function countCommonFollowers(int $sourceId, int $targetId, array $condition = []): int
741         {
742                 $condition = DBA::mergeConditions($condition,
743                         ["`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`) 
744                         AND `id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)",
745                         $sourceId, $targetId]
746                 );
747
748                 return DI::dba()->count('contact', $condition);
749         }
750
751         /**
752          * Returns a paginated list of contacts that follow both provided public contacts.
753          *
754          * @param int   $sourceId  Public contact id
755          * @param int   $targetId  Public contact id
756          * @param array $condition Additional condition on the contact table
757          * @param int   $count
758          * @param int   $offset
759          * @param bool  $shuffle
760          * @return array|bool Array on success, false on failure
761          * @throws Exception
762          */
763         public static function listCommonFollowers(int $sourceId, int $targetId, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
764         {
765                 $condition = DBA::mergeConditions($condition,
766                         ["`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`) 
767                         AND `id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)",
768                         $sourceId, $targetId]
769                 );
770
771                 return DI::dba()->selectToArray('contact', [], $condition,
772                         ['limit' => [$offset, $count],  'order' => [$shuffle ? 'RAND()' : 'name']]
773                 );
774         }
775
776         /**
777          * Calculate the interaction scores for the given user
778          *
779          * @param integer $uid
780          * @return void
781          */
782         public static function calculateInteractionScore(int $uid)
783         {
784                 $days = DI::config()->get('system', 'interaction_score_days');
785                 $contact_id = Contact::getPublicIdByUserId($uid);
786
787                 Logger::debug('Calculation - start', ['uid' => $uid, 'cid' => $contact_id, 'days' => $days]);
788
789                 $follow = Verb::getID(Activity::FOLLOW);
790                 $view = Verb::getID(Activity::VIEW);
791                 $read = Verb::getID(Activity::READ);
792
793                 DBA::update('contact-relation', ['score' => 0, 'relation-score' => 0, 'thread-score' => 0, 'relation-thread-score' => 0], ['cid' => $contact_id]);
794
795                 $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 (?, ?, ?)",
796                         $contact_id, DateTimeFormat::utc('now - ' . $days . ' day'), $uid, $contact_id, $follow, $view, $read);
797
798                 Logger::debug('Calculate score', ['uid' => $uid, 'total' => $total['activity']]);
799
800                 $interactions = DBA::p("SELECT `post`.`author-id`, 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 (?, ?, ?) GROUP BY `post`.`author-id`",
801                         $contact_id, DateTimeFormat::utc('now - ' . $days . ' day'), $uid, $contact_id, $follow, $view, $read);
802                 while ($interaction = DBA::fetch($interactions)) {
803                         $score = min((int)(($interaction['activity'] / $total['activity']) * 65535), 65535);
804                         DBA::update('contact-relation', ['score' => $score], ['cid' => $contact_id, 'relation-cid' => $interaction['author-id']]);
805                 }
806                 DBA::close($interactions);
807
808                 $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 (?, ?, ?)",
809                         $contact_id, DateTimeFormat::utc('now - ' . $days . ' day'), $uid, $contact_id, $follow, $view, $read);
810
811                 Logger::debug('Calculate thread-score', ['uid' => $uid, 'total' => $total['activity']]);
812
813                 $interactions = DBA::p("SELECT `post`.`author-id`, 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 (?, ?, ?) GROUP BY `post`.`author-id`",
814                         $contact_id, DateTimeFormat::utc('now - ' . $days . ' day'), $uid, $contact_id, $follow, $view, $read);
815                 while ($interaction = DBA::fetch($interactions)) {
816                         $score = min((int)(($interaction['activity'] / $total['activity']) * 65535), 65535);
817                         DBA::update('contact-relation', ['thread-score' => $score], ['cid' => $contact_id, 'relation-cid' => $interaction['author-id']]);
818                 }
819                 DBA::close($interactions);
820
821                 $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 (?, ?, ?)",
822                         $contact_id, DateTimeFormat::utc('now - ' . $days . ' day'), $uid, $contact_id, $follow, $view, $read);
823
824                 Logger::debug('Calculate relation-score', ['uid' => $uid, 'total' => $total['activity']]);
825
826                 $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`",
827                         $contact_id, DateTimeFormat::utc('now - ' . $days . ' day'), $uid, $contact_id, $follow, $view, $read);
828                 while ($interaction = DBA::fetch($interactions)) {
829                         $score = min((int)(($interaction['activity'] / $total['activity']) * 65535), 65535);
830                         DBA::update('contact-relation', ['relation-score' => $score], ['cid' => $contact_id, 'relation-cid' => $interaction['author-id']]);
831                 }
832                 DBA::close($interactions);
833
834                 $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 (?, ?, ?)",
835                         $contact_id, DateTimeFormat::utc('now - ' . $days . ' day'), $uid, $contact_id, $follow, $view, $read);
836
837                 Logger::debug('Calculate relation-thread-score', ['uid' => $uid, 'total' => $total['activity']]);
838
839                 $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`",
840                         $contact_id, DateTimeFormat::utc('now - ' . $days . ' day'), $uid, $contact_id, $follow, $view, $read);
841                 while ($interaction = DBA::fetch($interactions)) {
842                         $score = min((int)(($interaction['activity'] / $total['activity']) * 65535), 65535);
843                         DBA::update('contact-relation', ['relation-thread-score' => $score], ['cid' => $contact_id, 'relation-cid' => $interaction['author-id']]);
844                 }
845                 DBA::close($interactions);
846                 Logger::debug('Calculation - end', ['uid' => $uid]);
847         }
848 }