Laravel 5 form another array from database result

Question

I'm new to Laravel and I've been struggling too long with this now, tried to search SO and google for information but can't solve this.

I'm creating blog and need to make some kind of navigation/archive which displays year and months and how many blog posts there have been per year and invidual month. By clicking those years/months I would then display posts during that time period on different view.

I want them to be displayed in the view like this:

2015 (10)
  January(3)
  February(3)
  March(3)
  April(1)
2014 (2)
  May(1)
  June(1)

And so on.

I got database query like this:

    $links = \DB::table('posts')
    ->select(\DB::raw('YEAR(created_at) year, MONTH(created_at) month, MONTHNAME(created_at) month_name, COUNT(*) id'))
    ->where('active',1)
    ->groupBy('year')
    ->groupBy('month')
    ->orderBy('year', 'desc')
    ->orderBy('month', 'desc')
    ->get();

Which gives me table like this:

   array:3 [
      0 => {#275
        +"year": "2015"
        +"month": "10"
        +"month_name": "October"
        +"id": "3"
      }
      1 => {#274
        +"year": "2015"
        +"month": "9"
        +"month_name": "September"
        +"id": "1"
      }
      2 => {#273
        +"year": "2014"
        +"month": "8"
        +"month_name": "August"
        +"id": "1"
      }
    ]

How can I print it on my view like I described? If I go through the array in views like this:

@foreach($links as $link)
    <h3 class="text-uppercase"><a href="{{ url('blog/'.$link->year) }}">{{ $link->year }}</a></h3>
    <p><small class="blog_date"><a href="{{ url('blog/'.$link->year.'/'.$link->month) }}">{{ $link->month_name }} ({{ $link->id }}) </a></small>
@endforeach

I tried to use foreach-loop in my Controller to create another array from DB-results where structure would be in correct form and I could just use foreach to print it on the view, but couldn't get it work.

I know I'm near the solution, but I'm still learning. Please someone tell me which is the best way to do this.

Peter Kota · Accepted Answer · 2015-10-13 11:41:37Z

1

Try this in your blade:

<?php $first_loop = 0; ?>
@foreach($links as $link)
    @if($first_loop == 0)
        <?php 
        $first_loop = 1;
        $current_year = $link->year;
        ?>
        <h3 class="text-uppercase"><a href="{{ url('blog/'.$link->year) }}">{{ $link->year }}</a></h3>
        <p><small class="blog_date"><a href="{{ url('blog/'.$link->year.'/'.$link->month) }}">{{ $link->month_name }} ({{ $link->id }}) </a></small></p>
    @else
        @if($current_year == $link->year)
            <p><small class="blog_date"><a href="{{ url('blog/'.$link->year.'/'.$link->month) }}">{{ $link->month_name }} ({{ $link->id }}) </a></small></p>
            <?php
            $current_year = $link->year;
            ?>
        @else
            <h3 class="text-uppercase"><a href="{{ url('blog/'.$link->year) }}">{{ $link->year }}</a></h3>
            <p><small class="blog_date"><a href="{{ url('blog/'.$link->year.'/'.$link->month) }}">{{ $link->month_name }} ({{ $link->id }}) </a></small></p>
            <?php
            $current_year = $link->year;
            ?>
        @endif
    @endif
@endforeach

answered Oct 13, 2015 at 11:41

Peter Kota

8,3905 gold badges29 silver badges53 bronze badges

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

1 Comment

mpak Over a year ago

This is what I actually tried to do first, but couldn't get it work properly. Solution looks a bit ugly to my eye but it still works. Thanks!

Glad To Help · Accepted Answer · 2015-10-13 11:24:58Z

1

You can do some preparation on your data before outputting it. For example:

$links = // .. your query;

$years = array_pluck($links, 'year');

$results = [];


foreach($years as $year)
{
   $posts_count = 0;
   $posts = array_where($links, function($key, $value) use ($year, $posts_count){
         if($value['year'] == $year)
         {
            $posts_count += $value['id'];
            return true;
         }

         return false;
   });

   $results[$year] = ['posts' => $posts, 'posts_count' => $posts_count];
}

You can do the above in some repository/service class, or even in the controller if you want (although not recommended)

And then in your view you can have something like:

@foreach($results as $year => $result)
   <a href="">{{ $year }} {{ data_get($result, 'posts_count') }}</a>
       <ul> 
            @foreach(data_get($result, 'posts') as $monthly_post)
               <li><a href="">{{ data_get($monthly_post, 'month_name') }} {{ data_get($monthly_post, 'id') }}</a></li>
            @endforeach
       </ul>
@endforeach

This code is untested, so use it as inspiration for your approach, not a copy-paste solution.

edited Oct 13, 2015 at 11:24

answered Oct 13, 2015 at 11:16

Glad To Help

5,3974 gold badges41 silver badges58 bronze badges

4 Comments

mpak Over a year ago

Thanks for the answer, couldn't get it work though, it says: "Cannot use object of type stdClass as array". I tried to modify it use $value->year and $value->id but that gave me "Call to a member function count() on a non-object", which I've got no idea why it says that. Also can you explain why using this in the controller is not recommended?

Glad To Help Over a year ago

Controllers should be just passing data to model/service classes and then sending it to the view. Controllers should have few lines of code so that someone reading the code can quickly get the idea what is going on. If you don't stick to this, they can get cluttered, unreadable and harder to test and maintain.

mpak Over a year ago

Okay, thank you sir, all this is very new to me. So optimal solution would be creating new provider and passing $links to some of it's methods from controller? I'm sorry if my questions are confusing.. :)

Glad To Help Over a year ago

yes exactly, although provider doesn't have to be a provider in the Laravel sense, it can be just a normal class where you do pre-processing. The point is to keep your controllers slim and do the actual work elsewhere. No problem about the questions, we are all learning

Collectives™ on Stack Overflow

Laravel 5 form another array from database result

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related