按相关文章计数排序标签

Question

我想获取带有相关文章的标签。但是，目标是按相关文章的数量对它们进行排序。如何使用 QueryBuilder 和存储库类来实现这一点？我是symfony的新手，我尝试通过DQL获取标签，但它没有获取整个实体。

标签和文章@ManyToMany关系：

<?php
namespace AppEntity;
use DoctrineCommonCollectionsArrayCollection;
use DoctrineCommonCollectionsCollection;
use DoctrineORMMapping as ORM;
/**
* @ORMEntity(repositoryClass="AppRepositoryArticleRepository")
*/
class Article
{
/**
* @ORMId()
* @ORMGeneratedValue()
* @ORMColumn(type="integer")
*/
private $id;
/**
* @ORMColumn(type="string", length=255)
*/
private $title;
/**
* @ORMColumn(type="string", length=255)
*/
private $shortDescription;
/**
* @ORMColumn(type="text")
*/
private $content;
/**
* @ORMColumn(type="float")
*/
private $price;
/**
* @ORMManyToOne(targetEntity="AppEntityCategory", inversedBy="articles")
*/
private $category;
/**
* @ORMManyToMany(targetEntity="AppEntityTag", inversedBy="articles")
*/
private $tags;
/**
* @ORMManyToMany(targetEntity="AppEntityAuthor", inversedBy="articles")
*/
private $authors;
public function __construct()
{
$this->tags = new ArrayCollection();
$this->authors = new ArrayCollection();
}

public function getId(): ?int
{
return $this->id;
}
public function getTitle(): ?string
{
return $this->title;
}
public function setTitle(string $title): self
{
$this->title = $title;
return $this;
}
public function getShortDescription(): ?string
{
return $this->shortDescription;
}
public function setShortDescription(string $shortDescription): self
{
$this->shortDescription = $shortDescription;
return $this;
}
public function getContent(): ?string
{
return $this->content;
}
public function setContent(string $content): self
{
$this->content = $content;
return $this;
}
public function getPrice(): ?float
{
return $this->price;
}
public function setPrice(float $price): self
{
$this->price = $price;
return $this;
}
public function getCategory(): ?Category
{
return $this->category;
}
public function setCategory(?Category $category): self
{
$this->category = $category;
return $this;
}
/**
* @return Collection|Tag[]
*/
public function getTags(): Collection
{
return $this->tags;
}
public function addTag(Tag $tag): self
{
if (!$this->tags->contains($tag)) {
$this->tags[] = $tag;
}
return $this;
}
public function removeTag(Tag $tag): self
{
if ($this->tags->contains($tag)) {
$this->tags->removeElement($tag);
}
return $this;
}
/**
* @return Collection|Author[]
*/
public function getAuthors(): Collection
{
return $this->authors;
}
public function addAuthor(Author $author): self
{
if (!$this->authors->contains($author)) {
$this->authors[] = $author;
}
return $this;
}
public function removeAuthor(Author $author): self
{
if ($this->authors->contains($author)) {
$this->authors->removeElement($author);
}
return $this;
}
}
<?php
namespace AppEntity;
use DoctrineCommonCollectionsArrayCollection;
use DoctrineCommonCollectionsCollection;
use DoctrineORMMapping as ORM;
/**
* @ORMEntity(repositoryClass="AppRepositoryTagRepository")
*/
class Tag
{
/**
* @ORMId()
* @ORMGeneratedValue()
* @ORMColumn(type="integer")
*/
private $id;
/**
* @ORMColumn(type="string", length=255)
*/
private $name;
/**
* @ORMManyToMany(targetEntity="AppEntityArticle", mappedBy="tags")
*/
private $articles;
public function __construct()
{
$this->articles = new ArrayCollection();
}

public function getId(): ?int
{
return $this->id;
}
public function getName(): ?string
{
return $this->name;
}
public function setName(string $name): self
{
$this->name = $name;
return $this;
}
/**
* @return Collection|Article[]
*/
public function getArticles(): Collection
{
return $this->articles;
}
public function addArticle(Article $article): self
{
if (!$this->articles->contains($article)) {
$this->articles[] = $article;
$article->addTag($this);
}
return $this;
}
public function removeArticle(Article $article): self
{
if ($this->articles->contains($article)) {
$this->articles->removeElement($article);
$article->removeTag($this);
}
return $this;
}
}

我在TagRepository中使用的最后一种方法：

public function findTagsByArticlesCount()
{
return $this->createQueryBuilder('tag')
->leftJoin('tag.articles', 'article')
->orderBy('count(article.id)', 'DESC')
->getQuery()
->execute();
}

Answer 1

我依稀记得 2 年前的一个类似案例，我能够通过HIDDEN列来解决：

public function findTagsByArticlesCount()
{
return $this->createQueryBuilder('tag')
->leftJoin('tag.articles', 'article')
->addSelect('COUNT(article.id) as HIDDEN cnt')
->orderBy('cnt', 'DESC')
->getQuery()
->execute();
}

但是，我目前不在工作站上对此进行测试。你能试试吗？

希望对你有帮助...

Answer 2

在文章中计算标签时缺少groupBy：

$this->createQueryBuilder('tag')
->leftJoin('tag.articles', 'article')
->addSelect('COUNT(tag.id) AS tagcount')
->groupBy('tag.id')
->orderBy('tagcount', 'DESC')
->getQuery()
->execute();

注意：加入Articles时，您将收到number of tags * number of articles条记录，您需要按标签分组以计算相关文章中的标签数量。

注意2：我宁愿使用innerJoin而不是leftJoin选择没有相关文章的标签。