<commit_before><?php

namespace Minds\Core\Feeds\Suggested;

use Minds\Core\Di\Di;

class Repository
{
    /** @var \PDO */
    protected $db;

    public function __construct($db = null)
    {
        $this->db = $db ?: Di::_()->get('Database\PDO');
    }

    /**
     * @param array $opts
     * @return array
     * @throws \Exception
     */
    public function getFeed(array $opts = [])
    {
        $opts = array_merge([
            'user_guid' => null,
            'offset' => 0,
            'limit' => 12,
            'rating' => 1,
            'hashtag' => null,
            'type' => null,
            'all' => false, // if true, it ignores user selected hashtags
        ], $opts);

        if (!$opts['user_guid']) {
            throw new \Exception('user_guid must be provided');
        }

        if (!$opts['type']) {
            throw new \Exception('type must be provided');
        }

        if ($opts['hashtag']) {
            $query = "SELECT DISTINCT suggested.guid as guid,
                        lastSynced, score
                      FROM suggested
                      JOIN entity_hashtags
                        ON suggested.guid = entity_hashtags.guid
                      WHERE entity_hashtags.hashtag = ?
                        AND type = ?
                        AND rating <= ?
                      ORDER BY lastSynced DESC, score DESC
                      LIMIT ? OFFSET ?";

            $opts = [
                $opts['hashtag'],
                $opts['type'],
                $opts['rating'],
                $opts['limit'],
                $opts['offset']
            ];
        } else {

            // ignore user selected hashtags if all is true
            if ($opts['all']) {
                $query = "SELECT DISTINCT suggested.guid as guid,
                    lastSynced, score
                  FROM suggested
                  WHERE type = ?
                    AND rating <= ?
                  ORDER BY lastSynced DESC, score DESC
                  LIMIT ? OFFSET ?";

                $opts = [
                    $opts['type'],
                    $opts['rating'],
                    $opts['limit'],
                    $opts['offset']
                ];
            } else {
                $query = "SELECT DISTINCT suggested.guid as guid,
                    lastSynced, score
                  FROM suggested
                  JOIN entity_hashtags
                    ON suggested.guid = entity_hashtags.guid
                  JOIN user_hashtags
                    ON entity_hashtags.hashtag = user_hashtags.hashtag
                  WHERE user_hashtags.guid = ?
                    AND type = ?
                    AND rating <= ?
                  ORDER BY lastSynced DESC, score DESC
                  LIMIT ? OFFSET ?";
<commit_msg>(chore): improve query performance by using user hashtag table vs suggested<commit_after><?php

namespace Minds\Core\Feeds\Suggested;

use Minds\Core\Di\Di;

class Repository
{
    /** @var \PDO */
    protected $db;

    public function __construct($db = null)
    {
        $this->db = $db ?: Di::_()->get('Database\PDO');
    }

    /**
     * @param array $opts
     * @return array
     * @throws \Exception
     */
    public function getFeed(array $opts = [])
    {
        $opts = array_merge([
            'user_guid' => null,
            'offset' => 0,
            'limit' => 12,
            'rating' => 1,
            'hashtag' => null,
            'type' => null,
            'all' => false, // if true, it ignores user selected hashtags
        ], $opts);

        if (!$opts['user_guid']) {
            throw new \Exception('user_guid must be provided');
        }

        if (!$opts['type']) {
            throw new \Exception('type must be provided');
        }

        if ($opts['hashtag']) {
            $query = "SELECT DISTINCT suggested.guid as guid,
                        lastSynced, score
                      FROM suggested
                      JOIN entity_hashtags
                        ON suggested.guid = entity_hashtags.guid
                      WHERE entity_hashtags.hashtag = ?
                        AND type = ?
                        AND rating <= ?
                      ORDER BY lastSynced DESC, score DESC
                      LIMIT ? OFFSET ?";

            $opts = [
                $opts['hashtag'],
                $opts['type'],
                $opts['rating'],
                $opts['limit'],
                $opts['offset']
            ];
        } else {

            // ignore user selected hashtags if all is true
            if ($opts['all']) {
                $query = "SELECT DISTINCT suggested.guid as guid,
                    lastSynced, score
                  FROM suggested
                  WHERE type = ?
                    AND rating <= ?
                  ORDER BY lastSynced DESC, score DESC
                  LIMIT ? OFFSET ?";

                $opts = [
                    $opts['type'],
                    $opts['rating'],
                    $opts['limit'],
                    $opts['offset']
                ];
            } else {
                $query = "SELECT DISTINCT suggested.guid as guid,
                    lastSynced, score
                  FROM user_hashtags
                  INNER JOIN entity_hashtags
                    ON user_hashtags.hashtag = entity_hashtags.hashtag
  	              INNER JOIN suggested
                    ON entity_hashtags.guid = suggested.guid
                  WHERE user_hashtags.guid = ?
                    AND type = ?
                    AND rating <= ?
                  ORDER BY lastSynced DESC, score DESC
                  LIMIT ? OFFSET ?";

{'fraction_non_alphanumeric': 0.10403098046118642, 'fraction_numerical': 0.0014082027812004929, 'mean_word_length': 1.099778270509978, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1767198', 'n_tokens_mistral': 1528, 'n_tokens_neox': 1399, 'n_words': 370}