Finding duplicate images via MySQL

[Pete Jones]

 I have a PW install which takes a data feed (JSON). Currently I have a strange instance where certain images are being duplicated on import. I'd like to create a list of all the duplicated items including their 'name' (which is not the page name). I have the following query:

SELECT   C.`data`, COUNT(*) c FROM `field_h_media_url` AS C
GROUP BY C.`data` HAVING c > 1;

Which gives me all of the duplicates, but now I need to navigate my way through the PW data structure. I have the following structure:

field_h_media (repeater)

field_h_media_url (the external image URL)

field_h_id (refers to the external ID of the horse)

field_h_name (refers to the external NAME of the horse)

I wish to show:

Horse Name

Image repeated

Number of times repeated

Thanks

[Francesco Bortolussi]

Hi.

Maybe you could mix your custom research with some API calls.

If you do a:

$page->find()

, using the field 

field_h_media_url

 as part of the selector and using the result from your custom query as value; you could access pages and modify them.

Assuming you are coding from outside the main site files:

foreach ($repeated_urls as $duplicate) {
    $repeated_pages = wire('pages')->find("template=your_template, field_h_media.field_h_media_url=$duplicate");
}

Hope this could help.

[Pete Jones]

I was after a pure SQL solution rather than an API on but thanks for the pointer. I'll take a look.

[benbyf]

You could check for duplicates on import. If you saving them as pages or in fields. (Hey Pete)

[Pete Jones]

Hi Ben,

Can you check for a duplicate URL field in a repeater on import?

[benbyf]

You can request all pages with required fields, check against those field values and then carry on import if not present. Not sure how efficient it would be but should work.

[bernhard]

This is what I came up today:

<?php
  /**
   * Show warning of duplicate invoices
   * This will be executed on ready() of admin pages
   */
  public function showDuplicatesWarning() {
    $result = $this->wire->database->query("SELECT
        COUNT(pages_id) AS cnt,
        GROUP_CONCAT(pages_id SEPARATOR '|') AS ids,
        `data`
      FROM field_yourfieldname
      LEFT JOIN pages ON pages.id = pages_id
        WHERE pages.status = 1
      GROUP BY `data`
      HAVING cnt > 1");
    foreach($result->fetchAll(\PDO::FETCH_OBJ) as $dup) {
      $pages = $del = "";
      foreach($this->wire->pages->find("id=".$dup->ids) as $page) {
        $pages .= "$del<a href={$page->editUrl}>#$page</a>";
        $del = ', ';
      }
      $file = $dup->data;
      $this->warning("Duplicate: $file found on pages $pages", Notice::allowMarkup);
    }
  }

LEFT JOIN ... WHERE ... pages.status = 1 makes sure that files of pages that are in the trash are not counted ? 

PS: The code is part of a module and attached via hook:

<?php
$wire->addHookAfter("Pages::saved()", function($event) {
  $invoices = $this->wire->modules->get('Invoices');
  $invoices->showDuplicatesWarning();
});