Models and Virtual Database Fields

[eluser]Shaun Andrews[/eluser]
Thanks for the replies. I've ended up using MYSQL Alias' for this, and it does what I was hoping. Here's my Budgets model:

Code:

function get_all()
    {
        $this->db->select('id, name, monthly_amount, (0) AS spent, (0) AS remaining');
        $query = $this->db->get('budgets');
        return $query->result();
    }

As you can see, I'm simply setting the 'spent' and 'remaining' alias' as zero. I then set them in my controller, using data from my Entries model:

Code:

$budgets = $this->Budgets->get_all();

        foreach($budgets as $budget)
        {
            $budget_entries_for_current_cycle = $this->Entries->get_budget_entries_for_current_cycle($budget->id, $current_cycle_start);

            foreach ($budget_entries_for_current_cycle as $entry)
            {
                $budget->spent = $budget->spent + $entry->amount;
            }

            $budget->remaining = $budget->monthly_amount - $budget->spent;
        }

I really don't love this, as I'd love to be able to add the alias data in my Model. The problem I keep running into is that these "virtual" fields are based on data from another Model. I know theres a way to load the Entries model from my Budgets model, but I keep thinking there must be as easier way.

I was thinking of creating a separate method to load each individual budget (by id) with the information from the Entries 'get_budget_entries_for_current_cycle' method, but then I'm making a _ton_ of db queries. I'm no expert, but that just feels like a bad idea.