Hi everyone, I'm having some issues with Laravel's Eloquent queries. This is the situation:
$jeciste = JecJeciste::with('militant.militer', 'militant.sections.regions', 'militant.sections.dioceses')
->select()
->find($id);
The columns of the main JecJeciste table are present, but those of the other tables cannot be found.
Here are the contents of the select
->select(
'jec_jecistes.id as jec_jeciste_id',
'militants.id as militant_id',
'militants.name as militant_name',
'militants.email as militant_email',
'regions.name as region_name',
'dioceses.name as diocese_name'
)
I have this error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'militants.id' in 'field list' (SQL: select
jecistes.idasjeciste_id,jecistes.matriculeasjeciste_matricule,militants.idasmilitant_id, fromjecisteswherejecistes.id.
Thank you for your help.
You are asking the database for a column that doesn't exists.
Column not found: 1054 Unknown column 'militants.id' in 'field list'
'militants.id as militant_id',
You need to check your spelling in the query.
I also suggest giving this a read
@Tray2 The column does exist, as shown in the table extract
public function up()
{
Schema::disableForeignKeyConstraints();
Schema::create('militants', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('jeciste');
$table->unsignedBigInteger('section_origine');
$table->timestamps();
});
}
@pkouadio No it does not, otherwise they database wouldn't say it does not exist.
This doesn't refer to any militants table
$jeciste = JecJeciste::with('militant.militer', 'militant.sections.regions', 'militant.sections.dioceses')
@Tray2 Excuse me, but I'm confused. Here's the result for one line in tinker:
$jeciste = JecJeciste::with('militant.militer', 'militant.sections.regions', 'militant.sections.dioceses')->find($id);
= App\Models\jecJeciste {#7264
id: 7,
matricule: "23",
nom: "DOE",
prenom: "John",
date_naissance: "1999-07-14",
lieu_naissance: "Abidjan",
genre: "M",
Tel_1: "0123456789",
Tel_2: "0213456789",
email: "[email protected]",
cni: "C 005445 097",
sang: "AB-",
photo: "23.png",
created_at: "2023-09-29 13:45:28",
updated_at: "2023-09-29 13:45:28",
militant: App\Models\militant {#7293
id: 7,
jeciste: 7,
section_origine: 227,
created_at: "2023-09-29 13:45:28",
updated_at: "2023-09-29 13:45:28",
militer: Illuminate\Database\Eloquent\Collection {#7303
all: [
App\Models\jecMiliter {#7301
id: 5,
id_militant: 7,
id_annee: 1,
level_instance: 3,
instance: 24,
poste: "Responsable",
debut: null,
fin: null,
created_at: "2023-09-29 13:45:28",
updated_at: "2023-09-29 13:45:28",
},
],
},
sections: App\Models\jecSection {#7302
id: 227,
id_region: 22,
id_diocese: 8,
nom_section: "Coeur Immaculé",
localisation_section: "Grand Bassam Quartier Impérial",
type_section: 1,
created_at: "2023-04-17 09:54:13",
updated_at: "2023-04-17 09:54:13",
regions: App\Models\jecRegion {#7310
id: 22,
id_diocese: 8,
nom_region: "Bassam",
localisation_region: "Bassam",
created_at: "2023-04-17 09:54:13",
updated_at: "2023-04-17 09:54:13",
},
dioceses: App\Models\jecDiocese {#7311
id: 8,
nom_diocese: "Grand-Bassam",
level_diocese: 3,
description_diocese: "Une brève description de notre diocèse",
created_at: "2023-04-17 09:54:13",
updated_at: "2023-04-17 09:54:13",
},
},
},
}
I just want to select specific columns and rename them for simpler use in my view. Thank you for your assistance.
@pkouadio Do a ->toSQL() on your query, you will then see that the query looks for a militants table, and its id.
Do you have a table called militants?
And ffs follow the Laravel naming convention and use English for your table and column names, it will help you immensly.
@Tray2 Thanks for your comments.
Yes, I have a table named militants and here is part of the migration code:
public function up()
{
Schema::disableForeignKeyConstraints();
Schema::create('militants', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('jeciste');
$table->unsignedBigInteger('section_origine');
$table->timestamps();
});
}
Here is the associated model:
class militant extends Model{
use HasFactory;
protected $table = 'militants';
protected $fillable = ['jeciste', 'section_origine'];
public function jecistes(){
return $this->belongsTo(jecJeciste::class, 'jecistes', 'id');}
}
In my first ($jeciste = JecJeciste::with('militant.militer', 'militant.sections.regions', 'militant.sections.dioceses')->find($id);) request, there are several id columns, so I want to rename them to make them clearer for use in the view.
The with method does not do a join. It makes a completely different query. So you cannot do a select. If you want a join, do a join :)
@Sinnbeck Thank you for your help.
I mistakenly thought that the relations defined in the models could exempt me from using join. This is because I had all the columns of the tables cited in the relations in the result of my query.
Thank you and have a nice evening.
@pkouadio You need to select the primary key. Otherwise, query didn't find the relationship. Please follow like this:
$jeciste = JecJeciste::with('militant.id','militant.militer', 'militant.sections.regions', 'militant.sections.dioceses')
->select()
->find($id);
@smrokon no, this is not correct. militant.id is invalid for a with statement and also unnecessary
You are trying to include columns from related models (loaded with with).
The columns will be present but only against the related model(s)
If you want to limit the columns loaded in the relation you can use a :
For example
'militant.sections.dioceses:id,name'
and then you can access $jeciste->militant-sections->first()->name
Please or to participate in this conversation.