Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

dadub's avatar
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.

0 likes
10 replies
Tray2's avatar

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.

dadub's avatar
Level 1

Thank you for your reply.

I know that this field is not in my table "evenements".

Have I to use the pivot table name for my query ?

I bind the tables evenements and years through the models, why the field is not reachable ?

Thank you again.

dadub's avatar
Level 1

Great, thank you very much.

I have to take the name of the year into the year table, is this possible with "with" ?

Thank you again

frankielee's avatar

sorry, do you mind elaborating? I don't understand.

dadub's avatar
Level 1

Sorry.

I would like to add the year's name in the query.

This name is into the Years table and not into the evenements table.

Is this possible to add it ?

Thank you again.

dadub's avatar
Level 1

Thank you,

Is this possible with Laravel 7 ?

My code is this one :

$evenements = Evenement::with(['years'=>function($query)
        {
            return $query->orderBy('year_id');
        }])->orderBy('mnemonique')->get();

Thank you again

dadub's avatar
Level 1

Thank you very much.

Have a great week.

Please or to participate in this conversation.