How to get published posts by tag with active category and user?

Question

symfony

I study.
There are Entities: User, Category, Post, Tag

Post     - Tag  - many-to-many (таблица post_tag)
Category - Post - one-to-many
User     - Post - one-to-many

You need to select all Posts by tag, for example "php" (select ... from ... where tags.slug='php') and get posts by this tag.

As a result, you need to display a list of found posts, the category of each post, the user and tags of each post.

It turns out I need to write a query on SQL Query Builder and choose from 4 tables with joins and conditions?

class PostRepository extends ServiceEntityRepository
{
  public function findAllActiveByTag(string $slug)
  {
    return $this->createQueryBuilder('p')
            ->join('p.tags', 't')->addSelect('t')
            ->leftJoin('p.user', 'u')->addSelect('u')
            ->leftJoin('p.category', 'c')->addSelect('c')
            ->where('t.slug = :slug')->setParameter('slug', $slug)
            ->andWhere('p.isActive = true')
            ->andWhere('c.isActive = true')
            ->orderBy('p.createdAt', 'DESC')
            ->getQuery()
            ->getResult();
}

In the template I output something like this

{{ post.title }}
{{ post.category.name }}
{{ post.user.username }}
{% for tag in post.tags %}
    {{ tag.name }}
{% endfor %}

But:
- it selects all fields from all tables (it seems to me a bit too much)
- when listing posts, only the tag for which it searched is present (other post tags are missing)

Am I doing the right thing?
How to specify only the required fields for selection? (for example, only username is needed for a user). I understand that `partial u.{id,username}` is not recommended and Creating Partial Objects through DQL is deprecated...
It's not very clear how to display all tags for each found post?

Answer 1

I don’t know if this is correct or beautiful, I solved the problem like this:

class PostRepository extends ServiceEntityRepository
{
    public function findAllActiveByTag(string $slug)
    {
        $ids = $this->createQueryBuilder('p')
            ->select('p.id')
            ->leftJoin('p.tags', 't')
            ->where('t.slug = :slug')
            ->setParameter('slug', $slug)
            ->getQuery()
            ->getSingleColumnResult();

        if ($ids) {
            return $this->createQueryBuilder('p')
                ->leftJoin('p.category', 'c')->addSelect('c')
                ->leftJoin('p.tags', 't')->addSelect('t')
                ->leftJoin('p.user', 'u')->addSelect('u')
                ->where((new Expr())->in('p.id', $ids))
                ->andWhere('p.isActive = true')
                ->andWhere('c.isActive = true')
                ->orderBy('p.createdAt', 'DESC')
                ->getQuery()
                ->getResult();
        } else {
            return [];
        }
    }
}