Symfony: Get Posts of Friends

Question

I was wondering if someone could point me into the right direction on how to go about getting the posts of all friends of a user to combine some sort of a newsstream

I have a simple user entity with $user->getPosts() and $user->getFriends() which return the appropriate posts/user objects via the according relation setup in doctrine. Those work fine, but how do i compile all the posts by all my friends into one ArrayCollection of posts?

The only way I can think of is loop through all friends, get the posts, merge them into a single array, then perform some sorting... But this sounds a bit complicated/imperformant

I don't assume it's simply possible to do $user->getFriends()->getPosts() also how would i sort the posts properly to get the only the latest ones? I am also wondering about performance issues. Given a user has a lot of friends, it will be quite a lot of data to be retrieved from the database. What would be the best way to conquer that efficiently? Or don't i worry about that at all because of symphony's lazy loading?

Thanks for your help!

Here is the User Entity

AppBundle\Entity\User:
type: entity
repositoryClass: AppBundle\Entity\UserRepository
table: user
indexes:
    id:
        columns:
            - id
uniqueConstraints:
    username:
        columns:
            - username
    handle:
        columns:
            - handle
id:
    id:
        type: integer
        nullable: false
        options:
            unsigned: true
        id: true
        generator:
            strategy: IDENTITY
fields:
    username:
        type: string
        nullable: false
        length: 30
        options:
            fixed: false
    password:
        type: string
        nullable: false
        length: 60
        options:
            fixed: false
    email:
        type: string
        nullable: false
        length: 60
        options:
            fixed: false
oneToMany:
    images:
        targetEntity: Image
        mappedBy: user
    posts:
        targetEntity: Post
        mappedBy: user
    postcomments:
        targetEntity: Postcomment
        mappedBy: user
oneToOne:
    sedcard:
        targetEntity: Sedcard
        mappedBy: user
    portfolio:
        targetEntity: Portfolio
        mappedBy: user
manyToMany:
    myFriends:
        targetEntity: User
        joinTable:
            name: friend
        joinColumns:
            user_source:
                referencedColumnName: id
        inverseJoinColumns:
            user_target:
                referencedColumnName: id
        inversedBy: friendsWithMe
    friendsWithMe:
        targetEntity: User
        mappedBy: myFriends                    
lifecycleCallbacks: {  }

Here is the Post Entity

AppBundle\Entity\Post:
type: entity
table: post
indexes:
    user_id:
        columns:
            - user_id
id:
    id:
        type: integer
        nullable: false
        options:
            unsigned: true
        id: true
        generator:
            strategy: IDENTITY
fields:
    userId:
        type: integer
        nullable: false
        options:
            unsigned: true
        column: user_id
    body:
        type: text
        nullable: false
        length: 65535
        options:
            fixed: false
    type:
        type: boolean
        nullable: false
    created:
        type: datetime
        nullable: false
    active:
        type: boolean
        nullable: false
oneToMany:
    postcomments:
        targetEntity: Postcomment
        mappedBy: post
manyToOne:
    user:
        targetEntity: User
        inversedBy: posts
        joinColumn:
            name: user_id
            referencedColumnName: id
lifecycleCallbacks: {  }

Answer 1

<?php

namespace AppBundle\Repository;

/**
 * PostRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class PostRepository extends \Doctrine\ORM\EntityRepository
{
    public function findAllPostsOfFriends($userId)
    {
        return $this->getEntityManager()
            ->createQuery(
                'SELECT p, u FROM AppBundle:Post p ' .
                'JOIN p.user u ' .
                'JOIN u.friendsWithMe f ' . 
                'WHERE f.id=:userId'
            )->setParameter('userId', $userId)
            ->getResult();
    }
}

Explanation:

start to select all posts: (we want to select p and u (posts and authors) into memory to avoid an extra query for each post displayed on the screen when we want to show some info from the author as well. You can count the queries made by doctrine on the debug bar.(remove , u and see the results) )

SELECT p, u FROM AppBundle:Post p

Then join the complete user entities of the authors from the posts

JOIN p.user u

Next join the user entities of all the users that are friend with the authors

JOIN u.friendsWithMe f

And last but not least, filter on only one friend (myself) with all the authors

WHERE f.id=:userId

controller:

public function indexAction(Request $request)
{
    $em = $this->getDoctrine()->getManager();

    // give the user.id as parameter from who you want to see the posts of all his friends
    $posts = $em->getRepository('AppBundle:Post')->findAllPostsOfFriends(2); 

    return $this->render('default/index.html.twig', array(
        'posts' => $posts
    ));
}

template:

{% extends 'base.html.twig' %}

{% block body %}
    <ul>
        {% for post in posts %}
            <li>{{ post.body }} <strong>Author: {{ post.user.username }}</strong></li>
        {% endfor %}
    </ul>
{% endblock %}