Nested foreach loop in laravel 5.2

Question

I have 3 tables

1 Advertiser

2 Adverts

id

Advertiser can have multiple adverts

3 instances

id
ad_id
date

Adverts can have multiple date - date is the column in instances table

 $input = Input::get('fullname'); // fullname is the id of adverstiser
 // input is the advertiser id
   $adverts = DB::table('adverts')->where('a_id',$input)->get();
return view('adverts.index', compact( 'adverts'));

This query will give me all adverts data related to advertiser.

fine with that.

in my adverts.index

 @foreach($adverts as $advert)
 <div class="caption">
     <ul>           
          <li><i class="fa fa-picture-o"></i> {{$advert->height}}x{{$advert->height}}</li>
          <li><i class="fa fa-file"></i> {{$advert->finfo}}</li>
          <li><i class="fa fa-eye"></i> Last seen:</li>
          <li><i class="fa fa-link"></i> {{$advert->domain}}</li>
          <li><i class="fa fa-link"></i> <a href="{{$advert->src}}" target="_blank">Visit full ad</a></li>              
      </ul>

  </div>
  @endforeach

I would like to get the last seen date from the instance table.

SELECT MAX(date) AS "Last seen date" FROM instances WHERE ad_id =1

ad_id is the primary key id from adverts table

I would like to pass the value of this query to them same view as above adverts.index

Is the loop must be done in view?

Or something else I am missing.

So from what I understand, each $advert should have its own "last seen date"? Also, do you have models defined for these tables? — alexw
– alexw, Commented Mar 17, 2016 at 17:51
Each adverts has multiple dates, but wants to pick up the last seen dates. — sanainfotech
– sanainfotech, Commented Mar 21, 2016 at 11:07

alexw · Accepted Answer · 2016-03-21 17:39:10Z

1

I would first see if you can get the maximum date for each advert that has at least one corresponding instance. Something like this should do the trick:

$instances = DB::table('instances')
    ->select(DB::raw('*, MAX(date) as last_seen_date'))
    ->groupBy('ad_id')
    ->get();

If you can get this to work first, then you can join it with your advertiser and adverts tables to match a particular advertiser and get the remaining information for each advert. You can also do a leftJoin to ensure that you grab all adverts, even ones that don't have any matching instances.

I would also strongly encourage you to define models, which will make it easier to create reusable query fragments ("scopes") throughout your application.

answered Mar 21, 2016 at 17:39

alexw

8,7287 gold badges59 silver badges88 bronze badges

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

Collectives™ on Stack Overflow

Nested foreach loop in laravel 5.2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest