Symfony2 Sort by DESC in controller

Question

I can't seem to figure how to sort by DESC in my controller code. I have a category entity that has a OneToMany with Post entity.

I am trying to sort the result from category->getPosts by DESC, it's automatically sorting by ASC.

I've tried a custom repo but I need posts by category, using the repo below gives me all posts not the ones specific to the category.

I know it's something simple I'm missing completely. How do I add in a orderBy with my current setup?

This query worked for me:

public function getBlogsByCategory($category)
{
    return $this->createQueryBuilder('post')
        ->leftJoin('post.category','category')
        ->andWhere('category.title = :category')
        ->setParameter('category', $category)
        ->orderBy('post.createdAt', 'DESC')
        ->getQuery()
        ->getResult();
}

Controller

public function showAction($category = null)
{
    $em = $this->getDoctrine()->getManager();

    $category = $em->getRepository('AcmeDemoBundle:Category')
        ->findOneByTitle($category);

    if (!$category) {
        throw $this->createNotFoundException('Unable to find blog posts');
    }

    $posts = $category->getPosts();


    return array(
        'posts'    => $posts,
        'category' => $category
    );
}

Category entity

/**
 * @var integer
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var string
 *
 * @ORM\Column(name="title", type="string", length=255)
 */
private $title;

/**
 * @var string
 *
 * @Gedmo\Slug(fields={"title"}, unique=false)
 * @ORM\Column(length=255)
 */
private $slug;

/**
 * @ORM\OneToMany(targetEntity="Post", mappedBy="category")
 */
protected $posts;


public function __construct()
{
    $this->posts = new ArrayCollection();
}

public function __toString()
{
    return $this->getTitle() ? $this->getTitle() :  "";
}

/**
 * Get id
 *
 * @return integer
 */
public function getId()
{
    return $this->id;
}

/**
 * Set title
 *
 * @param string $title
 * @return Category
 */
public function setTitle($title)
{
    $this->title = $title;

    return $this;
}

/**
 * Get title
 *
 * @return string
 */
public function getTitle()
{
    return $this->title;
}

/**
 * Set slug
 *
 * @param string $slug
 * @return Category
 */
public function setSlug($slug)
{
    $this->slug = $slug;

    return $this;
}

/**
 * Get slug
 *
 * @return string
 */
public function getSlug()
{
    return $this->slug;
}

/**
 * Add posts
 *
 * @param Post $posts
 * @return Category
 */
public function addPost(Post $posts)
{
    $this->posts[] = $posts;

    return $this;
}

/**
 * Remove posts
 *
 * @param Post $posts
 */
public function removePost(Post $posts)
{
    $this->posts->removeElement($posts);
}

/**
 * Get posts
 *
 * @return \Doctrine\Common\Collections\Collection
 */
public function getPosts()
{
    return $this->posts;
}

Post entity

/**
 * @ORM\ManyToOne(targetEntity="Category", inversedBy="posts")
 * @ORM\JoinColumn(name="category_id", referencedColumnName="id")
 */
protected $category;

/**
 * Set category
 *
 * @param \Acme\DemoBundle\Entity\Category $category
 * @return Post
 */
public function setCategory(\Acme\DemoBundle\Entity\Category $category = null)
{
    $this->category = $category;

    return $this;
}

/**
 * Get category
 *
 * @return \Acme\DemoBundle\Entity\Category 
 */
public function getCategory()
{
    return $this->category;
}

Post repo

public function getPosts()
{
    $qb = $this->createQueryBuilder('p')
        ->addOrderBy('p.createdAt', 'DESC');

    return $qb->getQuery()
        ->getResult();
}

Answer 1

public function getOrderedPostsByCategory($categoryId)
{
    return $this->createQueryBuilder('p')
        ->leftJoin('p.category','c')
        ->addWhere('c.id = :categoryId')
        ->setParameter('categoryId', $categoryId)
        ->orderBy('p.createdAt', 'DESC')
        ->getQuery()
        ->getResult();

}

1) p.category put category in lowercase

2) and change p.categoryId into categoryId in setParameter

Answer 2

EDIT

Since you are using custom repo already then you will have to modify your repo method to look like:

public function getOrderedPostsByCategory($categoryId)
{
    return $this->createQueryBuilder('p')
        ->leftJoin('p.Category','c')
        ->addWhere('c.id = :categoryId')
        ->setParameter('c.categoryId', $categoryId)
        ->orderBy('p.createdAt', 'DESC')
        ->getQuery()
        ->getResult();

}

or second option would be to modify your getPosts method of entity and sort array in memory before doing return $this->posts;