Models and Virtual Database Fields

[eluser]Shaun Andrews[/eluser]
Hi All,

I'm working on an app for creating and tracking simple financial budgets. I have a database table named Budgets, which contains the fields: id, name, monthly_amount, created_at, and updated_at.

Users enter their expenses (tracked in a separate table) and can see how their spend habits are lining up with their set monthly budgets. Each month (4 weeks, 28 days) their budget is "reset." In reality, I'm calculating their budget based on a 4 week cycle, and only include expenses that exist within the current cycle. Hopeful that's not too confusing, but I don't think its too important for my question:

I want my Budgets model to hold information that doesn't exist within the database. For instance, I want to be able to store the current cycle's spent amount, remaining amount, and status (under, at, or over budget) within each instance of the Budgets object. This information is not something I want (or should) store in the database, as its constantly changing and is based on a number of outside variables (i.e. expenses, date, cycle).

How do I create "virtual" fields for my Budgets model?

Hopefully that all makes sense; I've only been at this whole MVC thing for a few months.

Any help would be greatly appreciated! Thanks!

[eluser]andyy[/eluser]
[quote author="Shaun Andrews" date="1288144555"]Hi All,
I want my Budgets model to hold information that doesn't exist within the database. For instance, I want to be able to store the current cycle's spent amount, remaining amount, and status (under, at, or over budget) within each instance of the Budgets object. This information is not something I want (or should) store in the database, as its constantly changing and is based on a number of outside variables (i.e. expenses, date, cycle).[/quote]
Creating a table that holds the "constantly changing" information may be the only way. You could link a specific row in the "constantly changing" table to the id's in your budget and expense tables for a proper relationship, controlling the contents with properly designed methods.

I don't see any other way to do it, unless you run methods when you need the "constantly changing" data to appear in the interface, maybe leveraging sessions to carry this data throughout.

[eluser]kaejiavo[/eluser]
Hi Shaun,

this depends on the functions you are performing in your model.
Two szenarios for example:

You use your model mainly to retrieve only one record:

Code:

class my_model extends Model
{

   public $expenses;
   public $date;
   ...


   __construct()
   {
     parent::__construct();
   }

   function get_budget()
   {
    // retrieve budget from database and fill variables
    $this->monthly_amount = ...

    // calculate virtual fields and set the variables
    $this->$expenses = _get_expenses();

    
   }

   function _get_expenses()
   {
    // calculate your "virtual" Field
    $expenses = ....
    return $expenses;
   }
}

// in your controller you then can use it:

$this->my_model->$expenses;

(of course there are many different ways to do this w/ private vars and setters/getters and so on - really depends on your context...)

If you need the fields calculated for many records you better calculate the fields in the SQL Query with aliases you want to use as field names and there you go.

Code:

$query = $this->db->query('SELECT *, (Your calculation) as expenses, ... FROM budgets WHERE ...');

foreach ($query->result() as $row)
{
   echo $row->id;
   echo $row->name;
   echo $row->monthly_amount;
   // 'virtual' fields (calculated)
   echo $row->expenses;
   ...
}

Marco

[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.

[eluser]danmontgomery[/eluser]
What's in get_budget_entries_for_current_cycle()? Seems like you should be able to leave some steps out here, maybe use SUM() in your query?

[eluser]Shaun Andrews[/eluser]
The method (get_budget_entries_for_current_cycle) grabs all entries that have the provided budget_id and where created after the cycle_start_date:

Code:

function get_budget_entries_for_current_cycle($budget_id, $cycle_start)
    {
        $this->db->select('description, amount, created_at')->where('budget', $budget_id)->where('created_at >', $cycle_start)->from('entries');
        $query = $this->db->get();
        return $query->result();
    }

The cycle_start is based on the users account creation date (which can be manually reset if they desire). Here's the current formula:

Code:

# Find place in current monthly cycle:
        # Start date is hard coded for now, but will pull from the users table.
        $start_date = 1284076800;
        
        # Find the difference (in s) between now and the start date.
        $time_from_start_to_now = time() - $start_date;

        # Divide the difference in time by 86,400s (a day) to find the number of days between start and current time.
        $number_of_days_between = $time_from_start_to_now / 86400;

        # Using the remainder (called 'mod' - %), find the current cycle day (based on a 28 day cycle) and week (based on a 7 day week).
        $current_cycle_day = $number_of_days_between % 28;
        $current_cylce_week = $current_cycle_day % 7;

        # Take the current cycle day in seconds (86,400s = day) and subtract from the current time to find when the current cycle began.
        $current_cylce_days_in_seconds = $current_cycle_day * 86400;
        $current_cycle_start = time() - $current_cylce_days_in_seconds;