typo3-news-tags

What this skill does

# TYPO3 News Tags — Bulk Generation & Assignment

> Source: https://github.com/dirnbauer/webconsulting-skills

> **Compatibility:** TYPO3 v14.x with `georgringer/news` ^14.
> All code examples target TYPO3 v14 APIs only. Do not use this skill for v12 / v13 sites.

> **Scope.** Designing, generating and assigning **thematic news tags** (`tx_news_domain_model_tag`)
> in georgringer/news at scale (hundreds to tens of thousands of news), and optionally installing
> the orthogonal generic tag system from `b13/tag` (`sys_tag`).
>
> **Not in scope.** Frontend rendering of tags in Fluid templates, tag translations, or tag merging.

## When to use this skill

Trigger this skill when the user asks to:
- "Tag all news / tag the latest N news"
- "Define a tag catalogue / generic tags / thematic tags"
- "Assign categories or tags in bulk to existing news"
- "Install a generic tagging extension" (b13/tag)
- "Build a Symfony command that tags news" / "automate tagging in TYPO3"
- "Why are large UIDs missing from my QueryBuilder result" (DBAL gotcha, §8)

## Mental model

EXT:news ships **two relations** for news classification:

| Relation | Table | Purpose | Routing |
|---|---|---|---|
| Categories | `sys_category` ← `sys_category_record_mm` | Primary hierarchical taxonomy | Optional |
| Tags | `tx_news_domain_model_tag` ← `tx_news_domain_model_news_tag_mm` | Flat, slug-routable thematic crosscuts | `PersistedAliasMapper` in site config |

**Categories** answer "what bucket is this in?" — typically a few per news, hierarchical, often
locale-aware. **Tags** answer "what themes does this touch?" — typically 5–10 per news, flat,
URL-friendly via `/{tag-slug}/`.

If the site already has narrow business categories ("Betrügerische Shops", "Phishing", …),
tags should be **orthogonal and thematic** (e.g. "Künstliche Intelligenz", "Banking", "Senioren")
— not redundant copies of categories.

`b13/tag` is a **different system**: `sys_tag` + `sys_tag_mm` with a `keywords` int column on
the target table. It is generic across all record types but **not** integrated with EXT:news.
Use it for tagging *other* tables; keep EXT:news's native tags for news.

## Default workflow

### 1. Verify the corpus (always)

```bash
ddev mysql -e "SELECT COUNT(*) FROM tx_news_domain_model_news WHERE pid=<PID> AND deleted=0 AND hidden=0;"
ddev mysql -e "SELECT COUNT(*) FROM tx_news_domain_model_tag WHERE deleted=0;"
ddev mysql -e "SELECT COUNT(*) FROM tx_news_domain_model_news_tag_mm;"
```

Identify where the readable text actually lives. With `georgringer/news-content-elements`
or `mask` based content, **`tx_news_domain_model_news.bodytext` is often empty** — the real
content sits in `tt_content` rows linked via `tx_news_related_news`. Verify:

```sql
SELECT AVG(LENGTH(bodytext)) FROM tx_news_domain_model_news WHERE pid=<PID> AND deleted=0;
SELECT CType, COUNT(*) FROM tt_content
  WHERE tx_news_related_news > 0 AND deleted=0 AND hidden=0
  GROUP BY CType ORDER BY 2 DESC;
```

If average bodytext is ~0, you **must** harvest `tt_content` to get meaningful scoring.

### 2. Derive tag candidates from corpus frequency

Don't guess the catalogue — let the corpus pick it. Read 50–100 titles + teasers first to
get a feel:

```sql
SELECT uid, title, LEFT(teaser, 200) FROM tx_news_domain_model_news
  WHERE pid=<PID> AND deleted=0 AND hidden=0
  ORDER BY datetime DESC LIMIT 100;
```

Then run a **frequency analysis** on the full target corpus (latest N news + their linked
`tt_content`). Brainstorm ~100 candidate concepts with 1–3 keywords each, count how many
news mention each, and pick the top ~65 (or whatever count you need). This is much more
defensible than a guessed catalogue and surfaces non-obvious recurring themes
(e.g. *Unternehmen* and *Polizei* ranked top-5 in the example corpus — neither was on
the original guess list).

**Single-concept rule.** Each tag should be ONE concept — never a `X & Y` combination.
Compose multiple tags per news instead. So:

| Avoid                          | Prefer                                         |
|--------------------------------|------------------------------------------------|
| `Banking & Konto`              | `Banking` + `Konto` (two tags)                 |
| `Künstliche Intelligenz & Deepfake` | `Künstliche Intelligenz` + `Deepfake`     |
| `Paket & Lieferung`            | `Paket` + `Lieferung` (+ `DHL` if relevant)    |
| `Reise & Urlaub`               | `Reise` + `Hotel` + `Flug`                     |

Hyphenated compounds (`Fake-Shop`, `Online-Shopping`, `Login-Daten`) and standard German
two-word concepts (`Künstliche Intelligenz`) are fine — they are one concept.

Each tag needs a *name*, a *slug* (lowercase, ASCII-only, hyphenated), and a curated list
of *keywords*. See `references/NewsThematicTags.example.php` for a worked German example
covering 65 single-concept fraud-prevention themes derived from a real ~1500-news corpus.

**Keyword design rules:**

- Lowercase. Both singular and plural where common (`fake-shop`, `fake-shops`).
- Both hyphenated and spaced spellings (`fake-shop`, `fake shop`, `fakeshop`).
- Compound nouns over generic single words (`kostenpflichtiges abo` over `kosten`).
- **Beware short prefixes**: `auto` matches `autor`, `automatisch`, `autorin` under the
  left-only word boundary. Use compound forms instead (`autokauf`, `autoverkauf`, `kfz`).
  Same trap with `apple` (`applied`), `bank` (`bankrott`), `post` (`posten`).
- 1–3 char ambiguous tokens are OK with strict both-sides boundaries (`tan`, `ki`, `sms`)
  — the example command auto-applies stricter boundaries for short keywords.
- 5–15 keywords per tag is a good target for single-concept tags; broaden if a tag is
  legitimately under-firing in the dry-run.
- Multi-word keywords are matched verbatim after whitespace collapse — be specific.

### 3. Decide on tag storage

For EXT:news, tags live on a **storage PID**. Use the same PID as the news (most common) — the
route enhancer in `config/sites/<id>/config.yaml` uses the tag's `slug`, not its PID. Verify your
site has the tag route wired:

```yaml
routeEnhancers:
  News:
    type: Extbase
    extension: News
    plugin: Pi1
    routes:
      - routePath: '/{tag-name}'
        _controller: 'News::list'
        _arguments:
          tag-name: overwriteDemand/tags
    aspects:
      tag-name:
        type: PersistedAliasMapper
        tableName: tx_news_domain_model_tag
        routeFieldName: slug
```

If not present, add it before publishing tag URLs.

### 4. Build a Symfony Console command

Create one command per extension/package in `Classes/Command/AssignNewsTagsCommand.php` and
register it in `Configuration/Services.yaml` with the `console.command` tag. See
`references/AssignNewsTagsCommand.example.php` for a complete, idempotent implementation that:

- loads the tag catalogue from a PHP config file
- upserts tags via **DataHandler** (slug auto-generates, refindex updated)
- harvests content from `tt_content` linked via `tx_news_related_news`
- normalizes HTML → lowercase → collapsed whitespace
- scores each tag by **distinct keyword matches** (not total occurrences — avoids spam from
  one repeated word dominating)
- selects the top N tags per news (5–10 typical, threshold ≥ 1)
- bulk-inserts MM rows via raw SQL multi-row `INSERT` (batch of 500)
- updates `tx_news_domain_model_news.tags` counter for backend list display
- supports `--dry-run`, `--reset`, `--limit`, `--storage-pid`, `--force`, `--debug-uid`

### 5. Iterate with `--dry-run`

```bash
ddev exec vendor/bin/typo3 cache:flush
ddev exec vendor/bin/typo3 <vendor>:news:assign-tags --dry-run --limit=300
```

The dry-run prints the **tags-per-news distribution** and **per-tag popularity**. Healthy
targets for fraud/news corpora:

- median 5+, p25 ≥ 3, p75 ≤ 8
- unmatched (0 tags) < 1% of corpus
- per-tag count: most-popular tag covers ≤ 60% of news; least-popular ≥ 1%

If many news fall to 1–2 tags, **broaden keywords** on common tags (E-Mail-Betrug, Bankin