esteemed.squire esteemed.squire - 3 months ago 18
Twig Question

Doctrine2 - How to setup a query by months?

Some quick context I am creating a blog archive based on month/year in the sidebar. I'm at the point where I need to show the posts for that month. I need some help in creating this query to pass onto twig.

Below is the link to the original question and what I have thus far for the setup, the query is obviously not correct (as to the nature of this post) and could use some help fleshing this out.

Help would be appreciated on showing me how to finish off these last steps with creating the proper query for posts in a month and linking it to twig.

Symfony2 - Setting up a blog archive

Thus far this is what I have.

Query ( NEED HELP WITH THIS PART - HOW TO SETUP QUERY TO RETRIEVE POSTS BY MONTH/YEAR)

public function getPostsByMonth($year, $month)
{
$qb = $this->createQueryBuilder('b')
->from('Blog', 'b')
->select('b')
->where('created BETWEEN :june AND :july')
->setParameter('june', $june->format('2013-June'))
->setParameter('july', $july->format('2013-July'))
???
}


Route

general_sym_project_archive:
path: /archive/{year}/{month}
defaults: { _controller: GeneralSymProjectBundle:Blog:archive }


Controller

public function archiveAction($year, $month)
{
$em = $this->getDoctrine()->getManager();

$blogs = $em->getRepository('GeneralSymProjectBundle:Blog')
->getPostsByMonth($year, $month);

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

foreach ($blogs as $post) {
$year = $post->getCreated()->format('Y');
$month = $post->getCreated()->format('F');
$blogPosts[$year][$month][] = $post;
}



return $this->render('GeneralSymProjectBundle:Default:archive.html.twig', array(
'blogPosts' => $blogPosts,
));


Twig (need to link posts by month to this twig)

<h4>Archives</h4>
<ol class="list-unstyled">
<li><a href="{{ path('general_sym_project_archive', { 'year': '2013', 'month': '07'}) }}">July 2013</a></li>
</ol>

Answer

Assuming your created field is a date time and you want to get the posts for a given month in a given year, this should work:

public function getPostsByMonth($year, $month)
{
    $date = new \DateTime("{$year}-{$month}-01");

    $qb = $this->createQueryBuilder('b');
    $query = $qb
        ->where('b.created BETWEEN :start AND :end')
        ->setParameter('start', $date->format('Y-m-d'))
        ->setParameter('end', $date->format('Y-m-t'))
    ;
    return $query->getQuery()->getResult();
}