Is it possible to define custom SQL query inside an entity in Symfony3

Question

looking for good practices here =)

Basically, I got one Entity, which is linked to Elems with a ManyToOne Relationship.

Lets say I want to select only some Elems from my Entity.

I can do

class Entity { 
    /* some vars here */ 
    public function getSpecificElems(){
        forEach($this->elems as $elem){
            /* do stuff here */
            if($someCondition){ $result[]=$elem;}
        }
        return $result;
    }

But that could imply large data fetching when there are many elems linked to my Entity. The other way would be

$em->getRepository("AppBundle:Repository")->getSpecificElems($entity);

Where getSpecificElems executes a DQL query.

I have got a problem here : the first solution is more intuitive to me because it is OOP. The second one is faster to execute, but seems bad to me.

Is there a way to mix both of 1) and 2) in order to get $entity->getSpecificElems() to return the list I want executing the good SQL query ?

Cheers,

Answer 1

Inside an Entity you can filter an ArrayCollection using Criteria class:

http://docs.doctrine-project.org/en/latest/reference/working-with-associations.html#filtering-collections

But this solution is not optimized if you have many data stored in db because Doctrine fetch all the data and then the filter is applied. The best approach is to filter the result using Dql queries.