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.

Answer 1

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.

Answer 2

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.

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;
}

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 ();

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