Using relations, it looks like dependent table should update on model->save, it does not

This is probably very obvious, but I'm having issues. I've got two tables which are using foreign keys

CREATE TABLE `activity_invoice_map` (
  `activity_invoice_map` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `activity_id` int(10) unsigned NOT NULL,
  `invoice_id` int(5) unsigned NOT NULL,
  PRIMARY KEY (`activity_invoice_map`),
  KEY `activity_id` (`activity_id`),
  KEY `invoice_id` (`invoice_id`),
  CONSTRAINT `activity_invoice_map_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`),
  CONSTRAINT `activity_invoice_map_ibfk_2` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`invoice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1594 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

CREATE TABLE `invoices` (
  `invoice_id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `client_id` int(5) unsigned NOT NULL,
  `invoice_number` varchar(29) NOT NULL,
  `invoice_description` varchar(255) NOT NULL,
  `invoice_created` datetime NOT NULL,
  `invoice_edited` datetime NOT NULL,
  `invoice_submitted` datetime DEFAULT NULL,
  `invoice_status` enum('Unpaid','Submitted','Paid','Voided') NOT NULL DEFAULT 'Unpaid',
  `invoice_paid` datetime DEFAULT NULL,
  `invoice_due` datetime DEFAULT NULL,
  `invoice_amount` float DEFAULT NULL,
  PRIMARY KEY (`invoice_id`),
  KEY `invoice_number` (`invoice_number`),
  KEY `invoice_description` (`invoice_description`),
  KEY `invoice_created` (`invoice_created`),
  KEY `invoice_submitted` (`invoice_submitted`),
  KEY `invoice_status` (`invoice_status`),
  KEY `invoice_paid` (`invoice_paid`),
  KEY `client_id` (`client_id`),
  CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`)
) ENGINE=InnoDB AUTO_INCREMENT=312 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

In the model for Invoices I have

    public function initialize()
    {
        $this->hasMany('invoice_id', 'ActivityInvoiceMap', 'invoice_id', array('alias' => 'ActivityInvoiceMap'));
        $this->belongsTo('client_id', 'Clients', 'client_id', array('alias' => 'Clients'));
    }

And ActivityInvoiceMap

    public function initialize()
    {
        $this->belongsTo('activity_id', 'Activities', 'activity_id', array('alias' => 'Activities'));
        $this->belongsTo('invoice_id', 'Invoices', 'invoice_id', array('alias' => 'Invoices'));
    }

I tried adding

use App\Models\ActivityInvoiceMap

To see if that would get model->save() to save and it says it can't find it despite having namespace App\Models at top of ActivityInvoiceMap and Invoices models, namespace App\Controllers at top of Invoices Controller, and

$loader->registerNamespaces(
        [
                'App\Models' => $config->application->modelsDir,
                'App\Controllers' => $config->application->controllersDir,
                'Sendgrid' => 'vendor/sendgrid/sendgrid/lib/SendGrid',
                'App\Cli\Tasks' => $config->application->cliTasksDir,
        ]);

In loader.php. This just worked with Yii without me doing anything, and it looks as if it should work with Phalcon also, so it must be something obvious. Any help is much appreciated. thanks- -doug

edited Nov '15

Show us full models. In hasMany first parameter is field in current model, second is model(with namespace if its diffrent forum current model - i just put always with full namespace for sure?), thrid parameter is field in related model, fourth is array for options.

In belongsTo first parameter is field in current model, second is related model, third is parameter in related model, fourth is for options.

You have in your relations same field in current and related model, are you sure there are in both models those properties and in database? To be honest it shouldnt be called like this..

Oh i see it should working. But calling in your table invoices each column with prefix invoice_ its bad idea. Cuz you already know its invoice, you dont have to repeat it.

Check if there are any errors for related models after ->save, if yes - then its why they not saving.

If I try to save by explicitly creating a new ActivityInvoiceMap (tried this as it does not seem to be doing it "magically")

excerpt from InvoicesController.php:

                    $activity_invoice_map = new ActivityInvoiceMap();
                    $activity_detail = Activities::findFirstByactivity_id($invoice_rows[$n]['activity_id']);
                    $activity_detail->activity_state = 'Billed';
                    if (!$activity_detail->save()) {
                        foreach ($activity_detail->getMessages() as $message) {
                            $this->flash->error($message);
                        }
                    }

and further down:

            $invoice = new Invoices();
            $activity_invoice_map = new ActivityInvoiceMap();
            $invoice->client_id = $details['client_id'];
            $invoice->invoice_number = $details['invoice_number'];
            $invoice->invoice_description = 'Invoice '.$details['invoice_number'].' - '.date('M j Y');
            $invoice->invoice_created = date('Y-m-d H:i:s');
            $invoice->invoice_submitted = date('Y-m-d H:i:s');
            $invoice->invoice_edited = date('Y-m-d H:i:s');
            $invoice->invoice_status = 'Submitted';
            $invoice->invoice_due = date('Y-m-d H:i:s', strtotime((date('Y-m-d H:i:s').'+ 5 days')) );
            $invoice->invoice_amount = preg_replace("/([^0-9\\.])/i", "", ($details['invoice_detail_total_billable']));
            if (!$invoice->create()) {
                foreach ($invoice->getMessages() as $message) {
                    $this->flash->error($message);
                }

If does not like that - it says it can't find the model. I tried using save(), update(), create() as per docs to no effect.

The schema was designed to be beyond obvious, e.g. in invoices there's invoiceid and in activities there's activityid, in the map table it's referred to as exactly the same, activityid and invoiceid. It's an old schema. Here are the models, a lot of this is just standard generation from Phalcon Tools.

ActivityInvoiceMap.php

<?php

namespace App\Models;

class ActivityInvoiceMap extends \Phalcon\Mvc\Model
{

    /**
     *
     * @var integer
     */
    public $activity_invoice_map;

    /**
     *
     * @var integer
     */
    public $activity_id;

    /**
     *
     * @var integer
     */
    public $invoice_id;

    /**
     * Initialize method for model.
     */
    public function initialize()
    {
        $this->belongsTo('activity_id', 'Activities', 'activity_id', array('alias' => 'Activities'));
        $this->belongsTo('invoice_id', 'Invoices', 'invoice_id', array('alias' => 'Invoices'));
    }

    /**
     * Returns table name mapped in the model.
     *
     * @return string
     */
    public function getSource()
    {
        return 'activity_invoice_map';
    }

    /**
     * Allows to query a set of records that match the specified conditions
     *
     * @param mixed $parameters
     * @return ActivityInvoiceMap[]
     */
    public static function find($parameters = null)
    {
        return parent::find($parameters);
    }

    /**
     * Allows to query the first record that match the specified conditions
     *
     * @param mixed $parameters
     * @return ActivityInvoiceMap
     */
    public static function findFirst($parameters = null)
    {
        return parent::findFirst($parameters);
    }

}

Invoices.php

<?php

namespace App\Models;
use Phalcon\Mvc\Model;
use Phalcon\Mvc\Model\Resultset\Simple as Resultset;
use Phalcon\Mvc\Model\Manager as ModelsManager;
use App\Models\Activities;
use App\Models\ActivityInvoiceMap;
use App\Models\Clients;
use App\Models\Projects;

class Invoices extends \Phalcon\Mvc\Model
{

    /**
     *
     * @var integer
     */
    public $invoice_id;

    /**
     *
     * @var integer
     */
    public $client_id;

    /**
     *
     * @var string
     */
    public $invoice_number;

    /**
     *
     * @var string
     */
    public $invoice_description;

    /**
     *
     * @var string
     */
    public $invoice_created;

    /**
     *
     * @var string
     */
    public $invoice_edited;

    /**
     *
     * @var string
     */
    public $invoice_submitted;

    /**
     *
     * @var string
     */
    public $invoice_status;

    /**
     *
     * @var string
     */
    public $invoice_paid;

    /**
     *
     * @var string
     */
    public $invoice_due;

    /**
     *
     * @var double
     */
    public $invoice_amount;

    /**
     * Initialize method for model.
     */
    public function initialize()
    {
        $this->hasMany('invoice_id', 'ActivityInvoiceMap', 'invoice_id', array('alias' => 'ActivityInvoiceMap'));
        $this->belongsTo('client_id', 'Clients', 'client_id', array('alias' => 'Clients'));
    }

    /**
     * Returns table name mapped in the model.
     *
     * @return string
     */
    public function getSource()
    {
        return 'invoices';
    }

    /**
     * Allows to query a set of records that match the specified conditions
     *
     * @param mixed $parameters
     * @return Invoices[]
     */
    public static function find($parameters = null)
    {
        return parent::find($parameters);
    }

    /**
     * Allows to query the first record that match the specified conditions
     *
     * @param mixed $parameters
     * @return Invoices
     */
    public static function findFirst($parameters = null)
    {
        return parent::findFirst($parameters);
    }

    public static function getHourlyBilling($client_id, $params = null)
    {
        if (is_numeric($client_id)) {
            $sql = 'select client_companyname, client_contact_address, client_contact_email, client_id, activity_id, project_id, project_name, project_currency, activity_description, activity_hourly, activity_start,
                    activity_end, time, round((time * activity_hourly),2) as billable from (
                    select c.client_companyname as client_companyname, c.client_contact_email as client_contact_email, c.client_id as client_id, activity_id, project_id, p.project_name as project_name,
                    p.project_currency as project_currency, activity_description, activity_hourly,
                    concat_ws(\'<br />\',`client_contact`,`client_billing_street`,`client_billing_city`,`client_billing_state`,`client_billing_postcode`) as client_contact_address,
                    convert_tz(activity_start,\'UTC\',\'America/New_York\') as activity_start, convert_tz(activity_end,\'UTC\',\'America/New_York\') as activity_end,
                    (hour(timediff(`activity_end`,`activity_start`))+round((ceil(minute(timediff(`activity_end`,`activity_start`)))*0.016666667),2)) as time
                    from activities a
                    join projects p using (project_id)
                    join clients c using (client_id)
                    where activity_state = "Unbilled" and activity_type not like "Fixed" and activity_type not like "Subcontractor" and activity_start is not null and activity_end is not null
                   and c.client_id = '.$client_id.' ) sq1 where time > 0 order by sq1.activity_start';
            $invoice = new Invoices();
            $unbilled = (new Resultset(null, $invoice, $invoice->getReadConnection()->query($sql,$params)))->toArray();
            return $unbilled;
        } else {
            return false;
        }
    }

    public static function getFixedCostBilling($client_id, $params = null)
    {
        if (is_numeric($client_id))
        {
            $invoice = new Invoices();
            $sql = 'select c.client_companyname, c.client_id, c.client_contact_email, activity_id, project_id, p.project_name, p.project_currency, activity_description, activity_fixed_amount as billable,
                    concat_ws(\'<br />\',`client_contact`,`client_billing_street`,`client_billing_city`,`client_billing_state`,`client_billing_postcode`) as client_contact_address
                    from activities a
                    join projects p using (project_id)
                    join clients c using (client_id)
                    where activity_state = "Unbilled" and activity_type like "Fixed" and activity_type not like "Subcontractor" and activity_fixed_amount != 0 and c.client_id='.$client_id.' order by activity_start';
            $unbilled = (new Resultset(null, $invoice, $invoice->getReadConnection()->query($sql,$params)))->toArray();
            return $unbilled;
            }
            else
            {
                return false;
            }

    }

    public static function getLast()
    {
        $invoice = new Invoices();
        $sql = 'select max(invoice_id) as max from invoices';
        $params = new \StdClass();
        $lastId = (new Resultset(null, $invoice, $invoice->getReadConnection()->query($sql,$params)))->toArray();
        return $lastId;
    }

}
edited Nov '15

But where are you doing $activityinvoicemap->save() cuz i dont see it anywhere ? You are not setting anywhere related objects, you are just trying to save/create entity without any related model in it.

To the loader problem - is the filenames same as class names ?

Reading https://docs.phalconphp.com/en/latest/reference/models.html#relationships-between-models it says Phalcon uses the magic methods set and get which looking at their examples seems to say that the model definitions would handle that without needing to explicitly save activityinvoicemap. That worked in Yii without me doing anything special, the models handled it and it looks like the same basic setup in the current models. The original files for models and controllers were done with scaffold automatically so that filenames match. Also, since the invoice id is generated at the time of save() it would not be easy to manually set that without going through another round of queries.

What ? But you are not setting ANYWHERE this related models for ActivityInvoiceMap, i dont see anywhere:

$activityInvoiceMap->invoice = $invoice.

You have to do:

$activityInvoiceMap->invoice = $invoice.

and then $activityInvoiceMap->save or create and it should save/create both invoice and activityInvoiceMap

That almost works. This map has to reflect both an activity ID and an invoice ID. It only creates one record, however, and fails to update the activity as Billed. If the loop that updates activities is set to either create or delete it fails

            $invoice = new Invoices();
            $activityInvoiceMap = new ActivityInvoiceMap();
            $invoice->client_id = $details['client_id'];
            $invoice->invoice_number = $details['invoice_number'];
            $invoice->invoice_description = 'Invoice '.$details['invoice_number'].' - '.date('M j Y');
            $invoice->invoice_created = date('Y-m-d H:i:s');
            $invoice->invoice_submitted = date('Y-m-d H:i:s');
            $invoice->invoice_edited = date('Y-m-d H:i:s');
            $invoice->invoice_status = 'Submitted';
            $invoice->invoice_due = date('Y-m-d H:i:s', strtotime((date('Y-m-d H:i:s').'+ 5 days')) );
            $invoice->invoice_amount = preg_replace("/([^0-9\\.])/i", "", ($details['invoice_detail_total_billable']));
            if (!$invoice->create()) {
                foreach ($invoice->getMessages() as $message) {
                    $this->flash->error($message);
                    echo $message." in the invoice loop"; die;
                }
            }
            $activityInvoiceMap->invoice_id = $invoice->invoice_id;
            foreach($checkboxes as $key=>$value) {
                    $invoice_rows[$n] = unserialize(base64_decode($line_items[$key]));
                    $activity_detail = Activities::findFirstByactivity_id($invoice_rows[$n]['activity_id']);
                    $activityInvoiceMap->activity_id = $activity_detail->activity_id;
                    $activity_detail->activity_state = 'Billed';
                    if (!$activityInvoiceMap->create()){
                        foreach ($activityInvoiceMap->getMessages() as $message) {
                            $this->flash->error($message);
                            echo $message; die;
                        }
                    }
                    $invoice_rows[$n]['billable'] = money_format('%2n',$invoice_rows[$n]['billable']);
                    if ($invoice_rows[$n]['activity_hourly'])
                        $invoice_rows[$n]['activity_hourly'] = money_format('%2n',$invoice_rows[$n]['activity_hourly']);
                    $n++;
            }

Maybe change $activityInvoiceMap->activityid = $activitydetail->activityid try just to $activityInvoiceMap->activityid = $activity_detail and check then, if not working check $activityInvoiceMap->save() (its create/update in one method)

Does not work. With this logic, it for some reason only saves one item to the activityinvoicemap table despite the fact the loop shows it reading all of the activities.

        foreach($checkboxes as $key=>$value) {
...
                    $activity_detail = Activities::findFirstByactivity_id($invoice_rows[$n]['activity_id']);
                    $activity_invoice_map[]->activity_id = $invoice_rows[$n]['activity_id'];
                    $activity_detail->activity_state = 'Billed';
                    if (!$activity_detail->save()) {
                        foreach ($activity_detail->getMessages() as $message) {
                            $this->flash->error($message);
                        }
                    }
...
            }
...
            $invoice = new Invoices();
            $activityInvoiceMap = new ActivityInvoiceMap();
            $invoice->client_id = $details['client_id'];
...
            if (!$invoice->save()) {
                foreach ($invoice->getMessages() as $message) {
                    $this->flash->error($message);
                    echo $message." in the invoice loop"; die;
                }
            } 
            $invoice_id = $invoice->invoice_id;
            $activityInvoiceMap->invoice_id = $invoice_id;
            foreach ($activity_invoice_map as $aim) {
                $activityInvoiceMap->activity_id = $aim->activity_id;
                if (!$activityInvoiceMap->save()) {
                    foreach ($activityInvoiceMap->getMessages() as $message) {
                        $this->flash->error($message);
                        echo $message." in the activityinvoicemap loop"; die;
                    }
                }
            }

But what is saving only one ? Activity or Invoice ? Is invoice all is fine cuz you setted the same invoice for all activity invoice map.

If I used activityinvoicemap save as before, the activities are not getting marked billed. And in one approach it was creating multiple zero-price invoices. If I do it as above, activityinvoicemap only puts one of the activity ids into activityinvoicemap. Invoices save.

To clarify - activities belong to invoice invoice, invoices can have and usually do have multiple activities.

edited Nov '15

Just tell me what is your point and what you want achive cuz its code looks pretty bad its pretty nasty, you are mixing camelCase with underscore etc. Like what you have before and what you want after.

I just figured it out. By explicitly declaring in new ActivityInvoiceMap in the for loop not outside of it, that works. I was just doing camelcase to make it very obvious what was old variables and what was new.