XF 2.2 Finding duplicated thread titles

[Anatoliy]

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.

<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]

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]

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

thanks! I red match tutorial - looks cool. can I do this match thingy with Finder or I should write raw database queries?

 

[RichDevman]

I would just use phpadmin.

 

[Anatoliy]

I would just use phpadmin.

But I need to pass that data to an add-on template.

 

[Anatoliy]

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.

 

[Anatoliy]

Is "class extension" a right direction for doing that?

 

[Kirby]

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:

$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]

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]

Thank you guys! I'll try tomorrow.

 

[Anatoliy]

I added to repository

    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

        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]

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()

 

[Anatoliy]

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

    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]

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.

 

[Anatoliy]

Erm, no.

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

        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]

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

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

        return $items;
    }

controller

        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]

just realized that I don't use $duplicates

 

[Anatoliy]

So after trying all possible combinations I ended up with the one that works. No code in repository, in controller

        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

<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]

alias it:

 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

<h3 style="margin:0">{$duplicate.title} {$duplicates.mycount}</h3>

 

[Anatoliy]

alias it:

 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

<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".

            $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();