CODEWITHSUNDEEP
phpmysqlsqllaraveljoin
Dora
Dora

Reputation: 6988

SQLSTATE[23000]: Integrity constraint violation: 1052 using join with laravel

I have two tables and I am trying to join them in order to get all the referenced tables but getting errors but I am not sure which part I am doing wrong, can someone please give me a hand?

I have this table migration for tickets

public function up()
{
    Schema::create('tickets', function (Blueprint $table) {
        $table->increments('id');
        $table->string('ticketNumber')->nullable(true)->unique();
        $table->boolean('is_deleted')->default(false);
        $table->timestamps();
    });
}

I have this table which saves notes for the tickets ticket_notes and I am referencing using the ticketNumber instead of the id

public function up()
{
    Schema::defaultStringLength(191);
    Schema::create('ticket_notes', function (Blueprint $table) {
        $table->string('ticketNumber');
        $table->foreign('ticketNumber')->references('ticketNumber')->on('tickets');
        $table->increments('id');
        $table->string('rloc', 50);
        $table->boolean('is_deleted')->default(false);
        $table->timestamps();
    });
}

I am usin query builder to do the query instead of eloquent relationships

DB::table('tickets')->where([
    'ticketNumber' => 12345,
    'is_deleted'   => false,
])
    ->join('ticket_notes', 'tickets.ticketNumber', '=', 'ticket_notes.ticketNumber')
    ->select('tickets.*');

I am getting this error SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'ticketNumber' in where clause is ambiguous (SQL: selecttickets.* fromticketsinner jointicket_notesontickets.ticketNumber=ticket_notes.ticketNumberwhere (ticketNumber= 12345 andis_deleted= 0))

Anyone can give me a fresh look where I did wrong with the join? Thanks in advance for any suggestions / help.

Upvotes: 1

Views: 1327

Answers (1)

ryantxr
ryantxr

Reputation: 4217

Because of the join, there are 2 columns named ticketNumber. Specify which one you want. 'tickets.ticketNumber' => 12345

DB::table('tickets')->where([
    'tickets.ticketNumber' => 12345,
    'is_deleted'   => false,
])
->join('ticket_notes', 'tickets.ticketNumber', '=', 
    'ticket_notes.ticketNumber')
->select('tickets.*');

Upvotes: 1

Related Questions