Integrity constraint violation: 1052 Column 'updated_at' in field list is ambiguous

Question

Content of App\User Model file:-

<?php

namespace App;

use Illuminate\Contracts\Auth\MustVerifyEmail;
use Tymon\JWTAuth\Contracts\JWTSubject;
use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;

class User extends Authenticatable implements JWTSubject
{
    use Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'name', 'email', 'password',
    ];

    public function khatmas()
    {
        return $this->hasMany('App\Khatma');
        
    }

    public function messages()
    {
        return $this->hasManyThrough('App\KhatmaRead','App\Khatma')
        ->select('reader_name','message','updated_at')
        ->where('message','!=',NULL);
    }

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password', 'remember_token',
    ];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

     public function getJWTIdentifier()
    {
        return $this->getKey();
    }

    /**
     * Return a key value array, containing any custom claims to be added to the JWT.
     *
     * @return array
     */
    public function getJWTCustomClaims()
    {
        return [];
    }
}

khatma_reads migration file:-

    $table->bigIncrements('id');
    $table->unsignedBigInteger('khatma_id');
    $table->foreign('khatma_id')->references('id')->on('khatmas')->onDelete('cascade');
    $table->string('reader_name')->nullable();
    $table->longText('message')->nullable();
    $table->timestamps();

in the user->message() method when i put the column "updated_at" or "created_at" on the ->select method i got error :-

"SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'updated_at' in field list is ambiguous (SQL: select `reader_name`, `message`, `updated_at`, `khatmas`.`user_id` as `laravel_through_key` from `khatma_reads` inner join `khatmas` on `khatmas`.`id` = `khatma_reads`.`khatma_id` where `message` is not null and `khatmas`.`user_id` in (1))"

when i totally remove the ->select() method from the query builder, i got response with all columns including the created_at and updated_at column.

How can i fix this?

Jonatan Lavado · Accepted Answer · 2020-06-22T15:27:53.113

8

That's because your KhatmaRead and Khatma models has update_at column and here:

->select('reader_name','message','updated_at')

You don't specify from what table you will take that column and it's ambiguous. You should do something like this:

->select('reader_name','message','khatma_reads.updated_at')

edited Jun 22 '20 at 15:27

answered Jun 22 '20 at 00:06

Jonatan Lavado

954
2
15
26

1

I tried it returned error "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Khatma.updated_at' in 'field list' (SQL: select `reader_name`, `message`, `Khatma`.`updated_at`, `khatmas`.`user_id` as `laravel_through_key` from `khatma_reads` inner join `khatmas` on `khatmas`.`id` = `khatma_reads`.`khatma_id` where `message` is not null and `khatmas`.`user_id` in (1))" – Q8root Jun 22 '20 at 11:03
Also i tried with 'KhatmaRead.updated_at' returned error "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'KhatmaRead.updated_at' in 'field list' (SQL: select `reader_name`, `message`, `KhatmaRead`.`updated_at`, `khatmas`.`user_id` as `laravel_through_key` from `khatma_reads` inner join `khatmas` on `khatmas`.`id` = `khatma_reads`.`khatma_id` where `message` is not null and `khatmas`.`user_id` in (1))" – Q8root Jun 22 '20 at 11:05
It was only a example. Your Model is named `KhatmaRead`, I don't know the name of your table, you shouldn't put the model name but the table's name. But seems like it's `khatma_reads` so you have to do this `khatma_reads.updated_at` – Jonatan Lavado Jun 22 '20 at 14:10
Its worked, thanks. i put the khatma_reads table name, and now its working fine 'khatma_reads.updated_at', i've updated my question and included the khatma_reads migration file content, can you please update your answer with 'khatma_reads.updated_at' so i can accept. – Q8root Jun 22 '20 at 14:28

score 0 · Answer 2 · answered Aug 02 '22 at 19:00

if there are any same name column between tables must put name table before column

public function messages()
{
    return $this->hasManyThrough('App\KhatmaRead','App\Khatma')
    ->select('reader_name','message','khatma_reads.updated_at')
    ->where('message','!=',NULL);
}

Integrity constraint violation: 1052 Column 'updated_at' in field list is ambiguous

2 Answers2