Doctrine 2, query inside entities

Question

How do I perform queries in an entity?

namespace Entities\Members;

/**
 * @Entity(repositoryClass="\Entities\Member\MembersRepository")
 * @Table(name="Members")
 * @HasLifecycleCallbacks
 */
class Members extends \Entities\AbstractEntity
{
    /**
     * @Id @Column(name="id", type="bigint",length=15)
     * @GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /** 
     * @Column(name="userid", type="bigint", length=26, nullable=true) 
     */
    protected $userid;

    /** 
     * @Column(name="fname", type="string", length=255,nullable=true) 
     */
    protected $fname;

    /**
     *  @OneToMany(targetEntity="\Entities\Users\Wall", mappedBy="entry", cascade={"persist"}) 
     */
    protected $commententries;

    public function __construct()
    {
        $this->commententries = new \Doctrine\Common\Collections\ArrayCollection();
    }
}

Example I would like to have a function inside this entity called: filter() and I want to be able to filter the commententries collection. It should return a collection with a certain condition such id=1. Basically it should be filtering the data received from the join query.

So something like this:

$this->commententries->findBy(array('id' => 1));

But obviously this does not work.

Landing

You can do exactly this using a custom repository which you already defined using @Entity(repositoryClass="\Entities\Member\MembersRepository"). Check my answer below: https://mcmap.net/q/270778/-doctrine-2-query-inside-entities – Harrington

Answer 1

Generally speaking, you shouldn't do this.

Entities, as a rule of thumb, should not know about the entitymanager (directly, or via some intermediary object).

The reason for this is mostly testability, but in my experience, it helps keeps things organized in other ways.

I'd approach it by designing a service class that handles the lookups for you. Your controller (or whatever) would drive it like this:

<?php
// create a new service, injecting the entitymanager.  if you later wanted 
// to start caching some things, you might inject a cache driver as well.
$member = $em->find('Member',$member_id); //get a member, some how.
$svc = new MemberService($em);

$favoriteCommentaries = $svc->getFavoriteCommentaries($member);

As I hint in the comment, if you decide later that you want to add caching (via memcached, for instance) to avoid frequent lookups, you'd do that somewhere near or in this service class. This keeps your entities nice and simple, and easily testable. Since you inject your entitymanager into the service at construction-time, you can mock that as needed.

getFavoriteCommentaries() could use various implementations. A trivial one would be to proxy it to Member::getFavoriteCommentaries(), which would actually load everything, and then filter out the "favorite" ones. That probably won't scale particularly well, so you could improve it by using the EM to fetch just the data you need.

Drowse

Answer 2

Your ArrayCollection already implements a filter() method, you need to pass a Closure to get it to work your entities (here, the commentEntries).

$idsToFilter = array(1,2,3,4);

$member->getComments()->filter(
    function($entry) use ($idsToFilter) {
       if (in_array($entry->getId(), $idsToFilter)) {
           return true;
       }

       return false;
    }
); 

(not tested)

Note that such method will iterate and eager load over all your Comments, so in case where a User has a lot it may be a big bottleneck;

In most case, you want to use a custom repositories, where you can place such logic.

As timdev suggested, you can create a MemberService which will wrap such call by being aware of the EntityManager.

Separating Entities from the Peristance Layer is a big improvement over Doctrine 1, and you should not break that rule.

Colorant

in the php closure you must enclose variables with parentheses e.g. use ($idsToFilter) – Jaynajayne

Answer 3

Use a custom repository for queries

You should not write queries in your entities, but you should use a repository for that. This is also explained in the doctrine documentation 7.8.8 Custom Repositories. It will allows you to build your custom queries on a central spot and keeps your entity definitions clean.

Use criteria to filter collections:

But if you want to filter inside your collection in a get method you can use Criteria. You can read on how to use Criteria in the Doctrine documentation 8.8 Filtering collections. To filter like you want to do would look something like this:

Declare at the top of your entity class the Criteria class

use Doctrine\Common\Collections\Criteria

In your getCommentEntries method use the class to filter:

public function getCommentEntries()
{
    $criteria = Criteria::create()
        ->where(Criteria::expr()->eq('id', 1));

    $filteredCommentEntries = $this->commententries->matching($criteria);

    return $filteredCommentEntries;
}

Harrington

@deanjase I ended up here again after long time and noticed that previously it wasn't clear you wanted to filter your collection. I updated my answer because filtering in your collection get method is definitely possible. – Harrington

Though the answer is old I found this little note in Filtering collections documentation which says "You can move the access of slices of collections into dedicated methods of an entity. For example Group#getTodaysBirthdayUsers(). Which obviously is making EntityManager visibile to your entity. Is that not breaking the rule? " – Exedra

@AftabNaveed You won't expose the EntityManager when adding this method to your entity. In the example they first get the $group to collect the $userCollection. In your group entity you can simply do $this->getUsers(); inside your custom getter and then add the criteria logic. – Harrington

Answer 4

Your question was really hard to understand, please try and work on how you structure your questions in the future. For instance, you say "return back the same result" but "filter", which could mean anything. Do you want to use the same result set (why on earth would you ever choose to do that), and just use array_filter or array_walk to filter the results or do you actually want to use a conditional join? It's incredibly ambiguous.

Anyway.. answer ( after reading your question 4 times).

$q = $qb->select ( "m","w" )
            ->from ( "Members", "m" )
            ->leftJoin( "m.commententries","w",'WITH', 'w.id = :id')
            ->setParameter ( "id", $id )
            ->getQuery ();

Flemish

can you use array_filter in a entity class? i tried: array_filter($a, array("Members", "entryFilter")); but class not found. – Landing

If you were to return your getResult() as hydration mode array you could. It would just be like stepping over any array. ->getQuery()->getResult(2) would do it. – Flemish

Answer 5

I agree with "timdev". You shouldn't define query in your entities class. My way to define a service class support the entities are repository classes. For example: User (entity -- YourBundle/Entity/User.php) will have UserRepository (service class -- YourBundle/Repository/UserRepository.php). Your "filter" method should be in here. You just need to map this service class in your entity class. In your controller, you can always access the "filter" via its repository. It's documented very detail in the Symfony2 book

Orchitis