Main element id replaced by the joined element id

I am running this query:

TaskNote::select(['id AS task_note_id', 'task_id', 'added_by ', 'note'])
                ->join('tasks', 'tasks.id', '=', 'task_notes.task_id')
                ->where('tasks.user_id', auth()->id())
                ->orWhere('tasks.created_by', auth()->id())
                ->orWhere('tasks.reviewer_id', auth()->id())
                ->get();

but for some reason, in the result the task_notes.id is replaces by the task.id. I've tried also to set the field like task_notes.id AS task_note_id, but in this case task_note_id is missing from the result and again the task_note.id is replaced by the task.id

This is one of the results:

Illuminate\Database\Eloquent\Collection {#2867 ▼ // app\Livewire\Task\TasksNotes.php:44
  #items: array:15 [▼
    0 => 
App\Models
\
TaskNote {#2869 ▼
      #connection: "mysql"
      #table: "task_notes"
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #with: []
      #withCount: []
      +preventsLazyLoading: true
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #escapeWhenCastingToString: false
      #attributes: array:25 [▼
        "id" => "9e0e16a4-9faa-4a67-950c-ff8314d21e96"
        "task_id" => "9e0e16a4-9faa-4a67-950c-ff8314d21e96"
        "added_by" => "9e0e0e5c-4e62-424d-a012-7853f1ce0efa"
        "note" => "test note"
      ]
      #original: array:25 [▼
        "id" => "9e0e16a4-9faa-4a67-950c-ff8314d21e96"
        "task_id" => "9e0e16a4-9faa-4a67-950c-ff8314d21e96"
        "added_by" => "9e0e0e5c-4e62-424d-a012-7853f1ce0efa"
        "note" => "test note"
      ]

RemiM

1 year ago

Level 16

The issue comes from your select() statement. When using select(['id AS task_note_id', ...]), Laravel interprets id as task_notes.id, but when you alias it (id AS task_note_id), it may get overridden due to the join(). Here's how to fix it explicitly:

TaskNote::select([
        'task_notes.id AS task_note_id', 
        'task_notes.task_id', 
        'task_notes.added_by', 
        'task_notes.note'
    ])
    ->join('tasks', 'tasks.id', '=', 'task_notes.task_id')
    ->where('tasks.user_id', auth()->id())
    ->orWhere('tasks.created_by', auth()->id())
    ->orWhere('tasks.reviewer_id', auth()->id())
    ->get();

Merklin

1 year ago

Level 7

@RemiM I've mentioned that I've tried this approach and the result is the same.

RemiM

1 year ago

Level 16

It looks like there's a mismatch between your model and the actual data. Your id appears to be a UUID string, but your TaskNote model defines #primaryKey: "id" with #keyType: "int".

Since the id is not an integer, Eloquent is likely treating it incorrectly. You may need to update your model to ensure it recognizes the id as a string instead of an integer.

Check the official documentation for UUID keys: https://laravel.com/docs/11.x/eloquent#uuid-and-ulid-keys and https://laravel.com/docs/11.x/migrations#column-method-uuid

krisi_gjika

1 year ago

Level 14

don't use a join for the job of a whereHas

Please or to participate in this conversation.