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,

putting this into the repository is absolutely the way to go to for filtering or paging of related entities, and btw totaly oop as well — john Smith
– john Smith, Commented Nov 28, 2016 at 11:21
@johnSmith : why is this OOP while you use the EntityRepository to get Entity properties instead the Entity itself ? — Sierra Leonne
– Sierra Leonne, Commented Nov 28, 2016 at 11:55
youre free to filter inside the entity, this answer states arrayCollection does filter results on sql level stackoverflow.com/questions/23689043/… — john Smith
– john Smith, Commented Nov 28, 2016 at 14:09
The "correct" ORM approach is to use a custom query to load the desired Elems when you query for the Entity. In fact, an argument can be made that lazy load should be avoided. — Cerad
– Cerad, Commented Nov 28, 2016 at 14:13

Samuele Mazza · Accepted Answer · 2016-11-28 11:29:22Z

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.

answered Nov 28, 2016 at 11:29

Samuele Mazza

613 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sierra Leonne Over a year ago

Exactly what I was looking for. Seems to do the filter on SQL level according to @johnSmith's comment. Thanks =)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related