Missing column in a database view while executing a query

Level 63

If I have this code, I don't have any error any more.

Why ?

class Commissions extends Component
{
    public $users;
    public $selected_user_id;

    public function mount()
    {
        $this->users = User::
            where('company_id', auth()->user()->company_id)
            ->orderBy('name')
            ->get();
    }

    public function render()
    {
        $commissions = MonthCommission::
            with('user')
            ->where('user_id', $this->selected_user_id)
            ->whereNotNull('month')
            ->orderByDesc('month')
            ->get();

        return view('livewire.commissions', compact('commissions'));
    }
}

Level 74

@vincent15000 are you using different views when querying the database?

1 like

Level 63

@Tray2 I have 2 views, but for this query, it's only with one view. I have create the MonthCommission model to be binded to the database view.

Level 74

@vincent15000 Then in my book it shouldn't be possible for that column to be missing.

1 like

Level 63

@Tray2 In the view, I don't have the commissions.id column, but I don't need it.

Level 74

@vincent15000 The query expects it, so try adding it and see what happens.

1 like

Level 63

@Tray2 If I add it, I need to add it to group by and I don't get the same result any more.

Level 74

@vincent15000 Can you share the SQL you have in the view?

1 like

Level 63

@Tray2

            CREATE OR REPLACE VIEW month_commissions_view 
            AS
            SELECT
                users.name AS user_name,
                users.company_id AS company_id,
                commissions.year AS year,
                commissions.month AS month,
                CONCAT(commissions.year, '-', LPAD(commissions.month, 2, '0')) AS year_and_month,
                SUM(CASE WHEN commissions.sale_id IS NOT NULL THEN commissions.amount ELSE 0 END) AS sale_based_amount,
                SUM(CASE WHEN commissions.sale_id IS NULL THEN commissions.amount ELSE 0 END) AS month_based_amount,
                SUM(commissions.amount) AS total_amount
            FROM
                users
                LEFT JOIN commissions ON commissions.user_id = users.id
            GROUP BY users.name, users.company_id, commissions.year, commissions.month

Level 74

@vincent15000 I don't see any reason for this, other than it might be that the public $commissions doesn't get reset properly.

What happens if you set that to null before you call getComissions?

 public function mount()
    {
		$this->comissions = null;
        $this->getCommissions();

        $this->users = User::
            where('company_id', auth()->user()->company_id)
            ->orderBy('name')
            ->get();
    }

Snapey

Level 122

dont grab your user model and put it in a public property.

The whole user model will be shared and visible in the browser

2 likes