6

Let's say I have a Post entity, and a Comment entity. A comment can be approved or not by an admin (which is a flag in the db). The post entity has:

/**
 * @ORM\OneToMany(targetEntity="Comment", mappedBy="post")
 */
protected $comments;

And I also want a second attribute which will look like:

/**
 * @ORM\OneToMany(targetEntity="Comment", mappedBy="post")
 */
protected $approvedComments;

How is it possible to load only the approved comments here?

Thanasis Pap
  • 2,031
  • 2
  • 17
  • 19
  • By just only loading the approved ones? I don't really get your problem. Can you expand a bit on how you are loading it? Repository? Controller? – Koalabaerchen Dec 02 '14 at 14:23
  • I am not loading the approvedComments currently, I am asking a nice way that this can be done. – Thanasis Pap Dec 02 '14 at 14:25

3 Answers3

2

Idea #1

You could use Inheritance mapping: https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/inheritance-mapping.html

The idea would to have separate classes for each type (approved and non-approved), but to store everything in a single table (SINGLE_TABLE inheritance).

You will need to have additional column which will store class type discriminator.

Then, you would have:

/**
 * @ORM\OneToMany(targetEntity="ApprovedComment", mappedBy="post")
 */
protected $approvedComments;

/**
 * @ORM\OneToMany(targetEntity="NonApprovedComment", mappedBy="post")
 */
protected $nonApprovedComments;

The obvious downside is creation of additional classes.

Idea #2

You could just tweak you Query/QueryBuilder like:

`SELECT p, c FROM AcmeDemoBundle:Post p LEFT JOIN p.comments c WITH c.approved = FALSE`

This idea seems more reasonable.

circulosmeos
  • 424
  • 1
  • 6
  • 19
Jovan Perovic
  • 19,846
  • 5
  • 44
  • 85
1

This can not be achieved through relationships as you describe it. 2 tables can not be related "conditionally" as the relations are based on primary keys.

You have at least solutions here

  1. Leave the "comments" field on the entity with the annotations, remove the annotations from the approved comments field as it is the same as comments as understood by the ORM. You would then have the ->getComments() function to get all comments and you could add a function "getApprovedCommentsForPost($post)" in your repostitory class to retrieve those approved ones.
  2. You could distinguish between comments with single inheritance, so you would have a Comment class and an ApprovedComment class in one table for example, then you could make 2 relations on your entity (read here doctrine-orm.readthedocs.org/en/latest/reference/inheritance-mapping.html#single-table-inheritance)
  3. You could use doctrine filters to filter out unapproved comments by default when retrieving data from Comments repository
Bartłomiej Wach
  • 1,968
  • 1
  • 11
  • 17
  • Thanks for the solutions provided, one question, on the first solution you suggest to call a repository from inside an entity? I though this is not good or even possible.. Can you explain how? – Thanasis Pap Dec 02 '14 at 14:33
  • No, what I mean is that you fetch the comments separately. So first you get the Post object from Post repository and then, separately, for example in your action, you do something like $doctrine->getRepository('...Comment')->getApprovedForPost($post) or ->getApprovedForPosts($arrayOfPostIds) – Bartłomiej Wach Dec 02 '14 at 14:39
  • Aha I get it now, but this will create a performance issue. As I want to retrieve many posts with their approved comments for one page. Checking now the other two solutions :) – Thanasis Pap Dec 02 '14 at 14:41
  • 1
    On the contrary, doing 2 queries separately will be faster as you skip the "JOIN" operation between tables, which is a costly one. and instead you just filter the Comments table by post_id columnt compared to filtering + merging results with the proper Post record – Bartłomiej Wach Dec 02 '14 at 14:44
  • 1
    Here you have a good answer: http://dba.stackexchange.com/questions/42998/are-individual-queries-faster-than-joins it shortly is "it depends" but when you get to large tables and hight traffic, the dabase becomes the bottleneck of the application, that's why PHP is still in business – Bartłomiej Wach Dec 02 '14 at 14:47
1

You cannot define that contraint in your entity. Here is the related documentation:

http://doctrine-orm.readthedocs.org/en/latest/reference/annotations-reference.html#annref-onetomany

As you can see there is no option which is related to conditions. You have to define this condition using QueryBuilder.

András
  • 693
  • 5
  • 17