laravel query returns weird order

- 1 answer

Ad

So I came across a bug in my 'bugoverview' page. (how ironic).. this weird way of order came to my knowledge after an bug was deleted out of the database (id : 7 was deleted)

Anyways the problem is as seen in the picture :

enter image description here

I get all the rows in a simple blade foreach.

my query codes are :

$bugs_all = Bug::with('klant','user')->orderBy('id','asc')->get();

and

$projects_all = Project::all();

my foreach code :

@foreach($projects_all as $project)
    @foreach($bugs_all as $bug)
        @if(count($bugs_all) > 0)
            @if($bug->project_id == $project->id)
                <tr>
                    <td>{{$bug->id}}</td>
                    <td>{{substr($bug->titel,0,15)}}...</td>
                    <td>{{$bug->status}}</td>
                    <td>{{$bug->soort}}</td>
                    <td>
                        @if($bug->prioriteit == 'laag')
                            <span class="label label-success">Laag</span>
                        @elseif($bug->prioriteit == 'gemiddeld')
                            <span class="label label-warning">Gemmideld</span>
                        @elseif($bug->prioriteit == 'hoog')
                            <span class="label label-danger">Hoog</span>
                        @elseif($bug->prioriteit == 'kritisch')
                            <span class="label label-purple">Kritisch</span>
                        @else
                            <span class="label label-info">Geen prioriteit</span>
                        @endif
                    </td>
                    <td>{{date('d-m-y - H:i',strtotime($bug->eind_datum))}}</td>
                    @if($bug->klant)
                        <td>{{$bug->klant->voornaam .' '.$bug->klant->tussenvoegsel.' '. $bug->klant->achternaam}}</td>
                    @endif
                    <td>{{$project->projectnaam}}</td>
                    @if($bug->user)
                        <td>{{$bug->user->voornaam .' '.$bug->user->tussenvoegsel.' '. $bug->user->achternaam}}</td>
                    @else
                        <td>Geen</td>
                    @endif
                    <td>
                        <a target="_blank" rel="nofollow noreferrer" target="_blank" rel="nofollow noreferrer" href="/bugchat/{{$bug->id}}" class="">
                            <button type="submit" class="btn btn-success btn-xs">
                                <i class="glyphicon glyphicon-search"></i>
                            </button>
                        </a>
                    </td>
                </tr>
            @endif

        @endif
    @endforeach
@endforeach
Ad

Answer

Ad

This question contains some ambiguity, without seeing an ERD or your table/relationship structure it's hard to truely help.

However, I assume you are using InnoDB and you are not ordering your relationships correctly.

First of all InnoDB is an engine that will re-uses deleted space. For example: You create records 1, 2 and 3. Then you delete record 2 and create record 4. The default order is auto_increment in ascending order (this is not explicitly order by id ASC).

With that said the order of your bugs are as follows: 1, 4, 3 - this is simply because record 4 replaced the space of record 2 (which was previously deleted).

Secondly I would simply advise you set-up your relationships correctly. I assume your Projects are related to Bugs somehow? Something along the following lines would suffice:

// Inside your Project model.
public function bugs()
{
    return $this->hasMany('App\Bug', 'bug_id', 'id')
                ->orderBy('id', 'ASC');
}

// Inside your Bug model.
public function project()
{
    return $this->belongsTo('App\Project', 'bug_id', 'id');
}

To get all bugs for all projects:

$projects = Project::with('bug.klant', 'bug.user')->all();

Inside your blade template, you can do:

@foreach ($project->bugs as $bug)

    {{ $bug->user }}
    {{ $bug->klant }}

@endforeach
Ad
source: stackoverflow.com
Ad