KnpLabs DoctrineBehaviors translatable query count

Question

I am using KnpLabs/DoctrineBehaviors translatable. I have a Post entity and a BlogCategory entity.

Post.php

class Post
{
    use Timestampable;

    /**
     * @ManyToMany(targetEntity="BlogCategory")
     * @JoinTable(name="post_categories")
     * @var ArrayCollection
     */
    protected $categories;

    ...
}




class BlogCategory
{
    use Translatable;

    /**
     * @Id()
     * @GeneratedValue(strategy="AUTO")
     * @Column(type="integer")
     * @var int
     */
    private $id;

     ...
}




class BlogCategoryTranslation
{
    use Translation;

    /**
     * @Column()
     * @NotBlank()
     * @var string
     */
    protected $name;

    ...
}

I want to show posts with related categories. But now I have a lot of queries.

How can I join translation in many-to-many association to optimize query count?

Answer 1

What you're looking for is a LEFT JOIN on the translations:

SELECT post, category, category_trans
FROM Post post
LEFT JOIN post.categories category
LEFT JOIN category.translations category_trans

If you want to only select the translation for the current language:

LEFT JOIN category.translations category_trans WITH category_trans.locale = :locale

(bind a locale parameter to the query).

If you're using query builder:

->leftJoin('category.translations', 'category_trans', 'WITH', category_trans.locale = :locale)