XF 2.2 Finding duplicated thread titles

Anatoliy

Well-known member
What would be the way to find and list all threads with duplicated titles?

So far I have a repository that grabs all the threads and then in template it checks for duplicates. The good thing it checks only 20 x all threads, not all treads x all thread. The bad thing - it lists all the threads regardless if it has duplicates or not.

Code:
<xf:title>Duplicated Thread Titles</xf:title>

<xf:pagenav page="{$page}" perpage="{$perPage}" total="{$total}" link="duplicated-thread-titles" wrapperclass="block" />

<xf:foreach loop="$data" value="$dat">
    <div class="block" style="margin-bottom: 3px">
        <div class="block-container">
            <div style="padding:10px;">
                <a href="{{ link_type('public', 'threads', $dat) }}" target="new"><h3 style="margin:0">{{$dat.title}}</h3></a>
                                
                                    <xf:if contentcheck="true">
                            <xf:contentcheck>
                                <xf:foreach loop="$data" value="$otherThread">
                                    <xf:if is="$dat.thread_id != $otherThread.thread_id && $dat.title|to_lower == $otherThread.title|to_lower">

                                            <a href="{{ link_type('public', 'threads', $otherThread) }}" target="new" style="color:#b71c1c"><h3 style="margin:0">{{$otherThread.title}}</h3></a>
                                    </xf:if>
                                </xf:foreach>
                            </xf:contentcheck>
                    </xf:if>
                
            </div>
        </div>
    </div>
</xf:foreach>

<xf:pagenav page="{$page}" perpage="{$perPage}" total="{$total}" link="duplicated-thread-titles" wrapperclass="block" />

Probably there is a better way to find duplicates and to list duplicates only? Please advise.
 

briansol

Well-known member
don't perform backend work in the front end.

do matching with sql and return only the results you want to the front end template.
 

Anatoliy

Well-known member
Code:
SELECT title, COUNT(title) FROM xf_thread GROUP BY title HAVING COUNT(title) > 1 ORDER BY COUNT(title) DESC;
works just perfect in phpmyadmin. but how can I run this query from a controller? I didn't find anything about raw database queries in the dev docs.
 

Kirby

Well-known member
A controller usually isn't the best place to run queries directly (a more suitable place would be a repository), but nevertheless you can run direct queries in controllers:
PHP:
$db = $this->app->db();

$duplicateTitles = $db->query('
    SELECT title, COUNT(title)
    FROM xf_thread
    GROUP BY title
    HAVING COUNT(title) > 1
    ORDER BY COUNT(title) DESC
')->fetchAll();

Keep in mind that the result set might be large (thousands of entries) so you'd really want to use pagination.
 

briansol

Well-known member
you can run a groupby after the fetch.



get all the data with the fetch process using a barebones query (no grouping)

SELECT title, COUNT(title) FROM xf_thread

add a sort with the finder in the repository

$items = $finder->setDefaultOrder('title', 'DESC')

and then you can group:

$data = $items->groupBy('title');


Then, you can iterate and take out data with count > 1





class extension is not the right thing... that's to add your own code to a built in XF php class.
 

Anatoliy

Well-known member
I added to repository
Code:
    public function findDuplicatedTitles()
    {
        $db = $this->app->db();

        $finder = $db->query('
            SELECT title, COUNT(title)
            FROM xf_thread
            GROUP BY title
            HAVING COUNT(title) > 1
            ORDER BY COUNT(title) DESC
        ');

        return $finder;
    }

and to controller
Code:
        public function actionDuplicates()
        {
            $page = $this->filterPage();
            $perPage = 20;
 
           /** @var \AV\ThreadTitless\Repository\ThreadTitle $repo */
            $repo = $this->repository('AV\ThreadTitles:ThreadTitle');
            $finder = $repo->findDuplicatedTitles()
            ->limitByPage($page, $perPage);
 
            $viewParams = [
               'duplicates' => $finder->fetch(),
               'total' => $finder->total(),
               'page' => $page,
               'perPage' => $perPage
           ];
 
               return $this->view('AV\ThreadTitles:TitleController\Shorts', 'av_thread_titles_duplicates', $viewParams);          
        }

it's throwing an error

ErrorException: [E_WARNING] Undefined property: AV\ThreadTitles\Repository\ThreadTitle::$app in src\addons\AV\ThreadTitles\Repository\ThreadTitle.php at line 58
  1. XF::handlePhpError() in src\addons\AV\ThreadTitles\Repository\ThreadTitle.php at line 58
  2. AV\ThreadTitles\Repository\ThreadTitle->findDuplicatedTitles() in src\addons\AV\ThreadTitles\Admin\Controller\TitleController.php at line 60
  3. AV\ThreadTitles\Admin\Controller\TitleController->actionDuplicates() in src\XF\Mvc\Dispatcher.php at line 352
  4. XF\Mvc\Dispatcher->dispatchClass() in src\XF\Mvc\Dispatcher.php at line 259
  5. XF\Mvc\Dispatcher->dispatchFromMatch() in src\XF\Mvc\Dispatcher.php at line 115
  6. XF\Mvc\Dispatcher->dispatchLoop() in src\XF\Mvc\Dispatcher.php at line 57
  7. XF\Mvc\Dispatcher->run() in src\XF\App.php at line 2352
  8. XF\App->run() in src\XF.php at line 524
  9. XF::runApp() in admin.php at line 13
🤷‍♂️
 

Kirby

Well-known member
You asked for controller code:
but how can I run this query from a controller?
$this->app cant't be used in repositories; in repositories you should use $this->db() to access the db object.

If you need it somehwere else where it isn't available directly: \XF::db()
 
Last edited:

Anatoliy

Well-known member
You asked for controller code:
right, but you indicated that it should go to repository, so I want to do it the way it should be
$this->app cant't be used in repositories; in repositories you should use $this->db to access the db object.

If you need it somehwere else where it isn't available directly: \XF::db()
I changed repository to
Code:
    public function findDuplicatedTitles()
    {
        $db = $this->db();

        $finder = $this->$db->query('
            SELECT title, COUNT(title)
            FROM xf_thread
            GROUP BY title
            HAVING COUNT(title) > 1
            ORDER BY COUNT(title) DESC
        ');

        return $finder;
    }

now the error is

Error: Object of class XF\Db\Mysqli\Adapter could not be converted to string in src\addons\AV\ThreadTitles\Repository\ThreadTitle.php at line 60
  1. AV\ThreadTitles\Repository\ThreadTitle->findDuplicatedTitles() in src\addons\AV\ThreadTitles\Admin\Controller\TitleController.php at line 60
  2. AV\ThreadTitles\Admin\Controller\TitleController->actionDuplicates() in src\XF\Mvc\Dispatcher.php at line 352
  3. XF\Mvc\Dispatcher->dispatchClass() in src\XF\Mvc\Dispatcher.php at line 259
  4. XF\Mvc\Dispatcher->dispatchFromMatch() in src\XF\Mvc\Dispatcher.php at line 115
  5. XF\Mvc\Dispatcher->dispatchLoop() in src\XF\Mvc\Dispatcher.php at line 57
  6. XF\Mvc\Dispatcher->run() in src\XF\App.php at line 2352
  7. XF\App->run() in src\XF.php at line 524
  8. XF::runApp() in admin.php at line 13
line 60 is
$finder = $this->$db->query('
 

Kirby

Well-known member
right, but you indicated that it should go to repository
Erm, no.

I said that respoitories would be a more suitable place to run queries, I didn't say the code I posted should be used in repositories - in fact I think I made it pretty clear that this was controller code.

As posted before, in repositories the DB object should be accessed via $this->db(), not $this->db()->$db.
Also, method query() does not return a Finder instance - it does return a result set.

You can't GROUP BY in MySQL with Finder, you either have to go with raw queries - or group the result set in PHP.
 
Last edited:

Anatoliy

Well-known member
I'm not a wizard. I am just learning. But for the sake of those who love, I am capable of any miracles. (c)
:)

Moved to controller and it works! Thank you!
The last thing as you mentioned is pagination. I'm trying by analogy to add limitByPage

PHP:
        public function actionDuplicates()
        {
            $db = $this->app->db();
            $page = $this->filterPage();
            $perPage = 20;

            $duplicates = $db->query('
            SELECT title, COUNT(title)
            FROM xf_thread
            GROUP BY title
            HAVING COUNT(title) > 1
            ORDER BY COUNT(title) DESC
            ')->limitByPage($page, $perPage);

            $viewParams = [
               'duplicates' => $duplicates->fetch(),
               'duplicatesTotal' => $duplicates->total(),
               'page' => $page,
               'perPage' => $perPage
           ];
  
               return $this->view('AV\ThreadTitles:TitleController\Duplicates', 'av_thread_titles_duplicates', $viewParams);           
        }

but it underlines with red limitByPage, and showing an error

Error: Call to undefined method XF\Db\Mysqli\Statement::limitByPage() in src\addons\AV\ThreadTitles\Admin\Controller\TitleController.php at line 65
  1. AV\ThreadTitles\Admin\Controller\TitleController->actionDuplicates() in src\XF\Mvc\Dispatcher.php at line 352
  2. XF\Mvc\Dispatcher->dispatchClass() in src\XF\Mvc\Dispatcher.php at line 259
  3. XF\Mvc\Dispatcher->dispatchFromMatch() in src\XF\Mvc\Dispatcher.php at line 115
  4. XF\Mvc\Dispatcher->dispatchLoop() in src\XF\Mvc\Dispatcher.php at line 57
  5. XF\Mvc\Dispatcher->run() in src\XF\App.php at line 2352
  6. XF\App->run() in src\XF.php at line 524
  7. XF::runApp() in admin.php at line 13
can I define method by adding something like use Something\Something\Something; ?
or I have to build my own pagination method?
 

Anatoliy

Well-known member
you can run a groupby after the fetch.



get all the data with the fetch process using a barebones query (no grouping)

SELECT title, COUNT(title) FROM xf_thread

add a sort with the finder in the repository

$items = $finder->setDefaultOrder('title', 'DESC')

and then you can group:

$data = $items->groupBy('title');


Then, you can iterate and take out data with count > 1
I barely understand what you say, more like not understand than understand, but I'm trying to follow, and it fills like I'm very close. )

repository
Code:
    public function findDuplicatedTitles()
    {
        $finder = $this->finder('\XF:Thread');
        $items = $finder->setDefaultOrder('title', 'DESC');

        return $items;
    }

controller
Code:
        public function actionDuplicates()
        {
            $db = $this->app->db();
            $page = $this->filterPage();
            $perPage = 20;

            $duplicates = $db->query('
            SELECT title, COUNT(title)
            FROM xf_thread')->fetchAll();

                    /** @var \AV\ThreadTitless\Repository\ThreadTitle $repo */
         $repo = $this->repository('AV\ThreadTitles:ThreadTitle');
         $items = $repo->findDuplicatedTitles()
         ->limitByPage($page, $perPage);

            $data = $items->groupBy('title');

            $viewParams = [
               'items' => $items,
               'duplicatesTotal' => $items->total(),
               'page' => $page,
               'perPage' => $perPage
           ];
  
               return $this->view('AV\ThreadTitles:TitleController\Duplicates', 'av_thread_titles_duplicates', $viewParams);           
        }

error

Error: Call to undefined method XF\Finder\Thread::groupBy() in src\addons\AV\ThreadTitles\Admin\Controller\TitleController.php at line 68
  1. AV\ThreadTitles\Admin\Controller\TitleController->actionDuplicates() in src\XF\Mvc\Dispatcher.php at line 352
  2. XF\Mvc\Dispatcher->dispatchClass() in src\XF\Mvc\Dispatcher.php at line 259
  3. XF\Mvc\Dispatcher->dispatchFromMatch() in src\XF\Mvc\Dispatcher.php at line 115
  4. XF\Mvc\Dispatcher->dispatchLoop() in src\XF\Mvc\Dispatcher.php at line 57
  5. XF\Mvc\Dispatcher->run() in src\XF\App.php at line 2352
  6. XF\App->run() in src\XF.php at line 524
  7. XF::runApp() in admin.php at line 13
 

Anatoliy

Well-known member
So after trying all possible combinations I ended up with the one that works. No code in repository, in controller
Code:
        public function actionDuplicates()
        {
            $db = $this->app->db();

            $duplicates = $db->query('
            SELECT title, COUNT(title)
            FROM xf_thread
            GROUP BY title
            HAVING COUNT(title) > 1
            ORDER BY COUNT(title) DESC
            LIMIT 100
            ')->fetchAll();

            $viewParams = [
               'duplicates' => $duplicates,
           ];

               return $this->view('AV\ThreadTitles:TitleController\Duplicates', 'av_thread_titles_duplicates', $viewParams);           
        }

My problem now is displaying COUNT(title) values next to thread titles in a template

Code:
<xf:title>Duplicated Thread Titles</xf:title>

<xf:foreach loop="$duplicates" value="$duplicate">
    <div class="block" style="margin-bottom: 3px">
        <div class="block-container">
            <div style="padding:10px;">
                <h3 style="margin:0">{$duplicate.title}</h3>
            </div>
        </div>
    </div>
</xf:foreach>

And I don't really understand why {$duplicate.title} works as $duplicates is array of arrays, not array of objects.
I'm stuck and need a magic butt kick. 🤷‍♂️
 

briansol

Well-known member
alias it:
Code:
 SELECT title, COUNT(title) AS mycount
and of course change your groupby and having statements to use the alias as well

and then use $duplicates.mycount in the template
Code:
<h3 style="margin:0">{$duplicate.title} {$duplicates.mycount}</h3>
 

Anatoliy

Well-known member
alias it:
Code:
 SELECT title, COUNT(title) AS mycount
and of course change your groupby and having statements to use the alias as well

and then use $duplicates.mycount in the template
Code:
<h3 style="margin:0">{$duplicate.title} {$duplicates.mycount}</h3>
Yeah! Thank you! It works!

one thing I didn't understand. you said to change groupby statement to use the alias. but it works with title, and throwing error with mycount "can't goup by mycount".

Code:
            $duplicates = $db->query('
            SELECT title, COUNT(title) AS mycount
            FROM xf_thread
            GROUP BY title
            HAVING mycount > 1
            ORDER BY mycount DESC
            LIMIT 100
            ')->fetchAll();
 
Top