3

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>
Community
  • 1
  • 1
esteemed.squire
  • 524
  • 1
  • 7
  • 20

1 Answers1

3

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();
}
tobymackenzie
  • 818
  • 6
  • 12
  • What is the -01 for on the $date variable? Do I need to change my route to include this and for the controller? Using this as is I get the following: (ContextErrorException: Warning: Missing argument 1 for General\SymProjectBundle\Entity\Repository\BlogRepository::getPostsByMonth(), called in /var/www/html/SymProject/src/General/SymProjectBundle/Controller/BlogController.php on line 127 and defined in /var/www/html/SymProject/src/General/SymProjectBundle/Entity/Repository/BlogRepository.php line 29) – esteemed.squire Mar 31 '14 at 02:45
  • The '-01' is setting the DateTime object to the first day of the month. Your error suggests you aren't passing arguments to the call to `getPostsByMonth()`, ie the $year and $month. – tobymackenzie Mar 31 '14 at 02:55
  • Hmm I've updated the controller to use getPostsByMonth() instead of getLatestBlogs(). Which loads the page fine, yet when I click on a month which has a year and month assigned it throws the error? (
  • July 2013
    • ) – esteemed.squire Mar 31 '14 at 03:04
  • Do I need to assign the $year and $month to null inside the controller? – esteemed.squire Mar 31 '14 at 03:05
  • Ah, you'll have to convert the format of those to 'm'. If you want the urls to have 'F' in them, you'll have to do something like this in the controller before you call that function: ` $date = new \DateTime("{$month} 01, {$year}"); $month = $date->format('m');` – tobymackenzie Mar 31 '14 at 03:11
  • and based on your code in your question, pass them into the `getPostsByMonth()` call like …->getPostsByMonth($year, $month) – tobymackenzie Mar 31 '14 at 03:13
  • I'll have to take more time and study this. How do I make this work with your query, what changes do I need to make in my controller? I don't have to use 'F' etc. – esteemed.squire Mar 31 '14 at 03:19
  • You can change 'July' to '07' in your view and it should work directly with the `getPostsByMonth($year, $month)`. Also, make sure to pass $year and $month to the call on line {5} of your controller. Edit: the call to `path` in your view that is – tobymackenzie Mar 31 '14 at 03:27
  • Ok, I tried that, changed the twig archive link from July to 07, included in ($year, $month) when calling the method in the controller. And now have this when clicking on the date - ([Semantical Error] line 0, col 54 near 'Blog b WHERE': Error: Class 'Blog' is not defined.) Everything else is left as is, did I need to make changes to the foreach loop? – esteemed.squire Mar 31 '14 at 03:30
  • Made changes to the code above to reflect exactly what I have currently. – esteemed.squire Mar 31 '14 at 03:33
  • You probably need to change 'Blog' to 'GeneralSymProjectBundle:Blog' or whatever bundle the entity is in. – tobymackenzie Mar 31 '14 at 03:33
  • Right tried that and get this - ([Semantical Error] line 0, col 83 near 'b WHERE created': Error: 'b' is already defined.) – esteemed.squire Mar 31 '14 at 03:37
  • Right now that leads back to the original error: (ContextErrorException: Warning: Missing argument 1 for Doctrine\ORM\EntityRepository::createQueryBuilder(), called in /var/www/html/SymProject/src/General/SymProjectBundle/Entity/Repository/BlogRepository.php on line 44 and defined in /var/www/html/SymProject/vendor/doctrine/orm/lib/Doctrine/ORM/EntityRepository.php line 81) – esteemed.squire Mar 31 '14 at 03:44
  • edited again to be more like how I've used the query builder. note that the query builder and query are stored in separate variables. – tobymackenzie Mar 31 '14 at 04:26
  • and `created` needs 'b.' before it – tobymackenzie Mar 31 '14 at 04:38
  • Just tried your new solution and put in ('b') for createQueryBuilder and have the following: ([Semantical Error] line 0, col 83 near 'b WHERE b.created': Error: 'b' is already defined.) – esteemed.squire Mar 31 '14 at 05:07
  • try `var_dump($query->getSql());` before the `return` and see what that says – tobymackenzie Mar 31 '14 at 05:36
  • FatalErrorException: Error: Call to undefined method Doctrine\ORM\QueryBuilder::getSQL() in /var/www/html/SymProject/src/General/SymProjectBundle/Entity/Repository/BlogRepository.php line 52 – esteemed.squire Mar 31 '14 at 05:39
  • note the case. It'd probably be better to do `getDql()` though, thinking about it, because it may error trying to create the sql – tobymackenzie Mar 31 '14 at 06:32
  • Performing a var_dump on getDql() displays: string 'SELECT b FROM General\SymProjectBundle\Entity\Blog b, GeneralSymProjectBundle:Blog b WHERE b.created BETWEEN :start AND :end' (length=124). then the error: ([Semantical Error] line 0, col 83 near 'b WHERE b.created': Error: 'b' is already defined.) – esteemed.squire Mar 31 '14 at 06:44
  • ah, I get it, you're using the `createQueryBuilder` of the `EntityRepository`. I've never used that before. That automatically adds the `select()` and `from` calls, so remove those from your code (see edited answer) – tobymackenzie Mar 31 '14 at 06:50
  • Figured it out, it WORKS! Thank you tobymackenzie for your knowledge and PATIENCE, you've been a great help and I've learned from this! – esteemed.squire Mar 31 '14 at 07:07
  • Ok may have jumped the gun, just to test with 2 posts in the same month, the 2nd post in the month doesn't come up in the query? Do I need to return as an array? – esteemed.squire Mar 31 '14 at 07:36
  • Odd---for some reason it doesn't like any posts on the the 31st, I set this back manually to the 30th and it appears? Is there something missing in the query to account for the last day of the month? ($date->modify('last day of this month')? – esteemed.squire Mar 31 '14 at 07:43
  • The date modifier seems to work properly for me locally. I can't figure out if Doctrine's `BETWEEN` is inclusive (without being able to test at the moment). You could try changing to `->where('b.created >= :start')->andWhere('b.created <= :end')`. I'll change my answer if that works. – tobymackenzie Mar 31 '14 at 13:02
  • Just tried this suggestion and no, it doesn't work, the last day of the month doesn't get pulled from the query. – esteemed.squire Mar 31 '14 at 20:14
  • Try the edited version of the end date. I wasn't aware of the 't' format, but it oughta work – tobymackenzie Mar 31 '14 at 22:33