3 * @copyright Copyright (C) 2010-2023, the Friendica project
5 * @license GNU AGPL version 3 or any later version
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.
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.
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/>.
22 namespace Friendica\Model\Contact;
25 use Friendica\Core\Logger;
26 use Friendica\Core\Protocol;
27 use Friendica\Database\Database;
28 use Friendica\Database\DBA;
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;
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.
48 * No discovery of followers/followings
50 const DISCOVERY_NONE = 0;
52 * Discover followers/followings of local contacts
54 const DISCOVERY_LOCAL = 1;
56 * Discover followers/followings of local contacts and contacts that visibly interacted on the system
58 const DISCOVERY_INTERACTOR = 2;
60 * Discover followers/followings of all contacts
62 const DISCOVERY_ALL = 3;
64 public static function store(int $target, int $actor, string $interaction_date)
66 if ($actor == $target) {
70 DBA::insert('contact-relation', ['last-interaction' => $interaction_date, 'cid' => $target, 'relation-cid' => $actor], Database::INSERT_UPDATE);
74 * Fetch the followers of a given user
76 * @param integer $uid User ID
79 public static function discoverByUser(int $uid)
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]);
87 $followers = self::getContacts($uid, [Contact::FOLLOWER, Contact::FRIEND]);
88 $followings = self::getContacts($uid, [Contact::SHARING, Contact::FRIEND]);
90 self::updateFollowersFollowings($contact, $followers, $followings);
94 * Fetches the followers of a given profile and adds them
96 * @param string $url URL of a profile
99 public static function discoverByUrl(string $url)
101 $contact = Contact::getByURL($url);
102 if (empty($contact)) {
103 Logger::info('Contact not found', ['url' => $url]);
107 if (!self::isDiscoverable($url, $contact)) {
108 Logger::info('Contact is not discoverable', ['url' => $url]);
112 $uid = User::getIdForURL($url);
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);
121 if (!empty($apcontact['followers']) && is_string($apcontact['followers'])) {
122 $followers = ActivityPub::fetchItems($apcontact['followers']);
127 if (!empty($apcontact['following']) && is_string($apcontact['following'])) {
128 $followings = ActivityPub::fetchItems($apcontact['following']);
133 Logger::warning('Contact seems to be local but could not be found here', ['url' => $url]);
138 self::updateFollowersFollowings($contact, $followers, $followings);
142 * Update followers and followings for the given contact
144 * @param array $contact
145 * @param array $followers
146 * @param array $followings
149 private static function updateFollowersFollowings(array $contact, array $followers, array $followings)
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']]);
157 $target = $contact['id'];
158 $url = $contact['url'];
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]);
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'];
173 $contacts = array_unique($contacts);
175 $follower_counter = 0;
176 $following_counter = 0;
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);
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++;
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]);
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]);
207 * Fetch contact url list from the given local user
209 * @param integer $uid
211 * @return array contact list
213 private static function getContacts(int $uid, array $rel): array
216 $profile = Profile::getByUID($uid);
217 if (!empty($profile['hide-friends'])) {
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'];
235 DBA::close($contacts);
241 * Tests if a given contact url is discoverable
243 * @param string $url Contact url
244 * @param array $contact Contact array
245 * @return boolean True if contact is discoverable
247 public static function isDiscoverable(string $url, array $contact = []): bool
249 $contact_discovery = DI::config()->get('system', 'contact_discovery');
251 if (Contact::isLocal($url)) {
255 if ($contact_discovery == self::DISCOVERY_NONE) {
259 if (empty($contact)) {
260 $contact = Contact::getByURL($url, false);
263 if (empty($contact)) {
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']]);
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]);
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]);
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']]);
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']]);
304 * Check if the cached suggestion is outdated
306 * @param integer $uid
309 static public function areSuggestionsOutdated(int $uid): bool
311 return DI::pConfig()->get($uid, 'suggestion', 'last_update') + 3600 < time();
315 * Update contact suggestions for a given user
317 * @param integer $uid
320 static public function updateCachedSuggestions(int $uid)
322 if (!self::areSuggestionsOutdated($uid)) {
326 DBA::delete('account-suggestion', ['uid' => $uid, 'ignore' => false]);
328 foreach (self::getSuggestions($uid) as $contact) {
329 DBA::insert('account-suggestion', ['uri-id' => $contact['uri-id'], 'uid' => $uid, 'level' => 1], Database::INSERT_IGNORE);
332 DI::pConfig()->set($uid, 'suggestion', 'last_update', time());
336 * Returns a cached array of suggested contacts for given user id
338 * @param int $uid User id
339 * @param int $start optional, default 0
340 * @param int $limit optional, default 80
343 static public function getCachedSuggestions(int $uid, int $start = 0, int $limit = 80): array
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);
349 if (!empty($cached)) {
352 return self::getSuggestions($uid, $start, $limit);
357 * Returns an array of suggested contacts for given user id
359 * @param int $uid User id
360 * @param int $start optional, default 0
361 * @param int $limit optional, default 80
364 static public function getSuggestions(int $uid, int $start = 0, int $limit = 80): array
370 $cid = Contact::getPublicIdByUserId($uid);
371 $totallimit = $start + $limit;
374 Logger::info('Collecting suggestions', ['uid' => $uid, 'cid' => $cid, 'start' => $start, 'limit' => $limit]);
376 $diaspora = DI::config()->get('system', 'diaspora_enabled') ? Protocol::DIASPORA : Protocol::ACTIVITYPUB;
377 $ostatus = !DI::config()->get('system', 'ostatus_disabled') ? Protocol::OSTATUS : Protocol::ACTIVITYPUB;
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` = ?)",
389 $uid, Contact::FRIEND, Contact::SHARING,
390 Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus, $uid
392 'order' => ['last-item' => true],
393 'limit' => $totallimit,
397 while ($contact = DBA::fetch($results)) {
398 $contacts[$contact['id']] = $contact;
401 DBA::close($results);
403 Logger::info('Contacts of contacts who are followed by the given user', ['uid' => $uid, 'cid' => $cid, 'count' => count($contacts)]);
405 if (count($contacts) >= $totallimit) {
406 return array_slice($contacts, $start, $limit);
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]
423 while ($contact = DBA::fetch($results)) {
424 $contacts[$contact['id']] = $contact;
426 DBA::close($results);
428 Logger::info('Contacts of contacts who are following the given user', ['uid' => $uid, 'cid' => $cid, 'count' => count($contacts)]);
430 if (count($contacts) >= $totallimit) {
431 return array_slice($contacts, $start, $limit);
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]
444 while ($contact = DBA::fetch($results)) {
445 $contacts[$contact['id']] = $contact;
447 DBA::close($results);
449 Logger::info('Followers that are not followed by the given user', ['uid' => $uid, 'cid' => $cid, 'count' => count($contacts)]);
451 if (count($contacts) >= $totallimit) {
452 return array_slice($contacts, $start, $limit);
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]
465 while ($contact = DBA::fetch($results)) {
466 $contacts[$contact['id']] = $contact;
468 DBA::close($results);
470 Logger::info('Any contact', ['uid' => $uid, 'cid' => $cid, 'count' => count($contacts)]);
472 return array_slice($contacts, $start, $limit);
476 * Counts all the known follows of the provided public contact
478 * @param int $cid Public contact id
479 * @param array $condition Additional condition on the contact table
483 public static function countFollows(int $cid, array $condition = []): int
485 $condition = DBA::mergeConditions($condition, [
486 '`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)',
490 return DI::dba()->count('contact', $condition);
494 * Returns a paginated list of contacts that are followed the provided public contact.
496 * @param int $cid Public contact id
497 * @param array $condition Additional condition on the contact table
500 * @param bool $shuffle
504 public static function listFollows(int $cid, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
506 $condition = DBA::mergeConditions($condition,
507 ['`id` IN (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ? AND `follows`)',
511 return DI::dba()->selectToArray('contact', [], $condition,
512 ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
517 * Counts all the known followers of the provided public contact
519 * @param int $cid Public contact id
520 * @param array $condition Additional condition on the contact table
524 public static function countFollowers(int $cid, array $condition = [])
526 $condition = DBA::mergeConditions($condition,
527 ['`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)',
531 return DI::dba()->count('contact', $condition);
535 * Returns a paginated list of contacts that follow the provided public contact.
537 * @param int $cid Public contact id
538 * @param array $condition Additional condition on the contact table
541 * @param bool $shuffle
545 public static function listFollowers(int $cid, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
547 $condition = DBA::mergeConditions($condition,
548 ['`id` IN (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ? AND `follows`)', $cid]
551 return DI::dba()->selectToArray('contact', [], $condition,
552 ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
557 * Counts the number of contacts that are known mutuals with the provided public contact.
559 * @param int $cid Public contact id
560 * @param array $condition Additional condition array on the contact table
564 public static function countMutuals(int $cid, array $condition = [])
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`)',
572 return DI::dba()->count('contact', $condition);
576 * Returns a paginated list of contacts that are known mutuals with the provided public contact.
578 * @param int $cid Public contact id
579 * @param array $condition Additional condition on the contact table
582 * @param bool $shuffle
586 public static function listMutuals(int $cid, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
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`)',
594 return DI::dba()->selectToArray('contact', [], $condition,
595 ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
601 * Counts the number of contacts with any relationship with the provided public contact.
603 * @param int $cid Public contact id
604 * @param array $condition Additional condition array on the contact table
608 public static function countAll(int $cid, array $condition = [])
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`))',
616 return DI::dba()->count('contact', $condition);
620 * Returns a paginated list of contacts with any relationship with the provided public contact.
622 * @param int $cid Public contact id
623 * @param array $condition Additional condition on the contact table
626 * @param bool $shuffle
630 public static function listAll(int $cid, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
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`))',
638 return DI::dba()->selectToArray('contact', [], $condition,
639 ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
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.
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
653 public static function countCommon(int $sourceId, int $targetId, array $condition = [])
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]
661 return DI::dba()->count('contact', $condition);
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.
668 * @param int $sourceId Public contact id
669 * @param int $targetId Public contact id
670 * @param array $condition Additional condition on the contact table
673 * @param bool $shuffle
674 * @return array|bool Array on success, false on failure
677 public static function listCommon(int $sourceId, int $targetId, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
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]
685 return DI::dba()->selectToArray('contact', [], $condition,
686 ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
691 * Counts the number of contacts that are followed by both provided public contacts.
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
699 public static function countCommonFollows(int $sourceId, int $targetId, array $condition = []): int
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]
707 return DI::dba()->count('contact', $condition);
711 * Returns a paginated list of contacts that are followed by both provided public contacts.
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
718 * @param bool $shuffle
719 * @return array|bool Array on success, false on failure
722 public static function listCommonFollows(int $sourceId, int $targetId, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
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]
730 return DI::dba()->selectToArray('contact', [], $condition,
731 ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
736 * Counts the number of contacts that follow both provided public contacts.
738 * @param int $sourceId Public contact id
739 * @param int $targetId Public contact id
740 * @param array $condition Additional condition on the contact table
744 public static function countCommonFollowers(int $sourceId, int $targetId, array $condition = []): int
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]
752 return DI::dba()->count('contact', $condition);
756 * Returns a paginated list of contacts that follow both provided public contacts.
758 * @param int $sourceId Public contact id
759 * @param int $targetId Public contact id
760 * @param array $condition Additional condition on the contact table
763 * @param bool $shuffle
764 * @return array|bool Array on success, false on failure
767 public static function listCommonFollowers(int $sourceId, int $targetId, array $condition = [], int $count = 30, int $offset = 0, bool $shuffle = false)
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]
775 return DI::dba()->selectToArray('contact', [], $condition,
776 ['limit' => [$offset, $count], 'order' => [$shuffle ? 'RAND()' : 'name']]
781 * Calculate the interaction scores for the given user
783 * @param integer $uid
786 public static function calculateInteractionScore(int $uid)
788 $days = DI::config()->get('channel', 'interaction_score_days');
789 $contact_id = Contact::getPublicIdByUserId($uid);
791 Logger::debug('Calculation - start', ['uid' => $uid, 'cid' => $contact_id, 'days' => $days]);
793 $follow = Verb::getID(Activity::FOLLOW);
794 $view = Verb::getID(Activity::VIEW);
795 $read = Verb::getID(Activity::READ);
797 DBA::update('contact-relation', ['score' => 0, 'relation-score' => 0, 'thread-score' => 0, 'relation-thread-score' => 0], ['relation-cid' => $contact_id]);
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);
802 Logger::debug('Calculate relation-score', ['uid' => $uid, 'total' => $total['activity']]);
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']]);
811 DBA::close($interactions);
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);
816 Logger::debug('Calculate relation-thread-score', ['uid' => $uid, 'total' => $total['activity']]);
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']]);
825 DBA::close($interactions);
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);
830 Logger::debug('Calculate score', ['uid' => $uid, 'total' => $total['activity']]);
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']]);
838 DBA::close($interactions);
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);
843 Logger::debug('Calculate thread-score', ['uid' => $uid, 'total' => $total['activity']]);
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']]);
851 DBA::close($interactions);
852 Logger::debug('Calculation - end', ['uid' => $uid]);