How to get value from query using select as. It returns NULL instead of value

Question

I have query:

$query = Task::query()->select(['tasks.*', 'rootTask.is_public as rootPublic']);
        $query->leftJoin('tasks as rootTask', function ($join) {
        $join->on('tasks._lft', '>=', 'rootTask._lft')
            ->whereNull('rootTask.deleted_at')
            ->where('tasks._lft', '<=', 'rootTask._rgt')
            ->whereNull('rootTask.parent_id');
        });

when I try to get $task->rootPublic it returns NULL But if I do $query->toSql(); and run that SQL it returns 1.

What can be an issue?

Answer 1

When you use select(['tasks.*', 'rootTask.is_public as rootPublic']), Laravel may not map the aliased column correctly.

To resolve this issue, you can use the DB::raw method to define the alias directly in the select statement. Here's an updated version of your query:

$query = Task::query()->select(['tasks.*', DB::raw('rootTask.is_public as rootPublic')]);
$query->leftJoin('tasks as rootTask', function ($join) {
    $join->on('tasks._lft', '>=', 'rootTask._lft')
        ->whereNull('rootTask.deleted_at')
        ->where('tasks._lft', '<=', 'rootTask._rgt')
        ->whereNull('rootTask.parent_id');
});

$tasks = $query->get();

I hope it can help you! Thanks