Your evenements table does not have a year_id column so you can't sort on it. So either add it to the table or remove it from your query.
Jul 18, 2021
10
Level 1
SELECT many to many not working
Hello everyone,
An event can be in one to several years and a year can have several events, hence the use of the many-to-many relationship.
So I have 3 tables: events, years and evenement_year (pivot table).
I carefully read the Laravel 7 documentation and thought I had followed the procedure :
Evenement Model :
namespace App;
use Illuminate\Database\Eloquent\Model;
class Evenement extends Model
{
protected $fillable = ['name', 'year_id','mnemonique','color'];
//One yar can have severals events and I give the name events_years to pivot
public function years()
{
return $this->belongsToMany(Year::class, 'evenement_year');
}
}
Year Model :
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Year extends Model
{
protected $fillable = ['name'];
public function evenements()
{
// a event can be in several years and I give the name events_years to pivot
return $this->belongsToMany(Evenemnt::class, 'evenement_year');
}
}
When I try to SELECT all events with years with this code (in the EventementController) at the index method :
<?php
namespace App\Http\Controllers;
use App\Evenement;
use App\EvenementType;
use App\Type;
use App\Year;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use stdClass;
class EvenementController extends Controller
{
/**
* Create a new controller instance.
*
* @return void
*/
public function __construct()
{
$this->middleware('auth');
}
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$evenements = Evenement::orderBy('year_id')->orderBy('mnemonique')->get();
$years = Year::all();
$evenTypes= EvenementType::all();
$types= Type::All();
return view('evenement.index', compact('evenements', 'years','evenTypes','types'));
}
}
I have this error :
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'year_id' in 'order clause' (SQL: select * from `evenements` order by `year_id` asc, `mnemonique` asc)
Thank you for your help and have a nice Sunday.
Level 29
https://laravel.com/docs/7.x/eloquent-relationships#eager-loading
Try
Evenement::with(['years'=>function($q){
return $q->orderBy('year_id');
}])->orderBy('mnemonique')->get();
1 like
Please or to participate in this conversation.