Skip to content

Foreign key not set when using create_association magic method? #546

Open
@louismrose

Description

@louismrose
Contributor

I have the following (MySQL) database schema:

CREATE TABLE `jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `export_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `exports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` VARCHAR(1000) NOT NULL,
PRIMARY KEY (`id`))
  ENGINE = InnoDB DEFAULT CHARSET=utf8;

With the following PHP ActiveRecord models:

class Job extends ActiveRecord\Model {
  // A Job has 0 or 1 Export
  // (but we use belongs to as the foreign key is on the Job table)
  static $belongs_to = [['export']];

  // A Job has a name
  static $attr_accessible = ['name'];
}
class Export extends ActiveRecord\Model {
  // An Export has a URL
  static $attr_accessible = ['url'];
}

When I run the following code, I expected export_id to be the same as Export::last()->id but it is null:

$job = new Job(["name" => "testJob"]);
$job->create_export([
  "url" => "http://www.example.org"
]);
$job->save();
$job->reload();

var_dump($job->export_id);  // prints NULL

Interestingly, $job->name has the correct value (testJob). The MySQL database has the following data:

mysql>  select * from jobs;
+----+---------+-----------+
| id | name    | export_id |
+----+---------+-----------+
|  1 | testJob |      NULL |
+----+---------+-----------+

mysql> select * from exports;
+----+------------------------+
| id | url                    |
+----+------------------------+
|  1 | http://www.example.org |
+----+------------------------+

I've tried the code on both v1.1.2 and the master branch, and am getting the same results on both versions.

Any hints as to what I might be doing wrong? Could this be a bug?

Activity

visavi

visavi commented on Jun 29, 2016

@visavi

structure should be like this

CREATE TABLE `jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `exports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) DEFAULT NULL,
  `url` VARCHAR(1000) NOT NULL,
PRIMARY KEY (`id`))
  ENGINE = InnoDB DEFAULT CHARSET=utf8;

If there is no condition of links quite simply write static $belongs_to = ['export'];

but in your case should be a link $has_many or $has_one

then when such entries

$job->create_export([
  "url" => "http://www.example.org"
]);

whereas when such record create_export automatic fill field job_id

and yes I also have a question to the developers about filling if a many-to-many through the staging table in version 1.1

many have not tried it was impossible to fill all 3 tables

We have to first save the news
and then do something like this

....
$news->save(); // Store news

$tag = Tag::create(['name' => $tag]); // Store tags
$tag->create_news_tags([ 'news_id' => $news->id]); // Store the relationship between news and tags
koenpunt

koenpunt commented on Jun 30, 2016

@koenpunt
Collaborator

This is probably because you create the export before the job has been saved. Try calling $job->save(); before create_export

louismrose

louismrose commented on Jun 30, 2016

@louismrose
ContributorAuthor

Thanks for your quick replies, @visavi and @koenpunt

Try calling $job->save(); before create_export

I've tried changing the code to this:

$job = new Job(["name" => "testJob"]);
$job->save();

$job->create_export([
  "url" => "http://www.example.org"
]);
$job->save();
$job->reload();

And to this:

$job = new Job(["name" => "testJob"]);
$job->save();
$job->reload();

$job->create_export([
  "url" => "http://www.example.org"
]);
$job->save();
$job->reload();

But in both cases $job->export_id is still null.


I'd like to keep the foreign key on the Jobs table if possible. However, I tried the suggestion of @visavi and updated the MySQL schema such that the foreign key was on the Export table and was named job_id. However, I got the following exception:

ActiveRecord\UndefinedPropertyException: Undefined property: Job->export_id in /Users/louis/Code/php-activerecord-issues/vendor/php-activerecord/php-activerecord/lib/Model.php on line 540

/Users/louis/Code/php-activerecord-issues/vendor/php-activerecord/php-activerecord/lib/Model.php:540
/Users/louis/Code/php-activerecord-issues/vendor/php-activerecord/php-activerecord/lib/Model.php:345
/Users/louis/Code/php-activerecord-issues/belongs_to/test/BelongsToTest.php:20

Which seems to indicate that belongs_to is correctly looking for the foreign key attribute on the Jobs?

koenpunt

koenpunt commented on Jun 30, 2016

@koenpunt
Collaborator

To add to @visavi's answer, when going with the schema he suggests, you'll have to update the belongs_to association to a has_one

louismrose

louismrose commented on Jun 30, 2016

@louismrose
ContributorAuthor

I'd really like to keep the relationship as a "belongs to" (i.e., the foreign key on the Job table) if at all possible.

Interestingly, when I run this code:

$job = new Job(["name" => "testJob"]);
$job->export = Export::create([
  "url" => "http://www.example.org"
 ]);

I get the following exception:

ActiveRecord\UndefinedPropertyException: Undefined property: Job->export in /Users/louis/Code/php-activerecord-issues/vendor/php-activerecord/php-activerecord/lib/Model.php on line 432

/Users/louis/Code/php-activerecord-issues/vendor/php-activerecord/php-activerecord/lib/Model.php:432
/Users/louis/Code/php-activerecord-issues/belongs_to/test/BelongsToTest.php:9

But when I run:

$job = new Job(["name" => "testJob"]);
var_dump($job->export);

Then null is printed out!

So it seems there is an inconsistency: I can read from the export property but not write to it. Perhaps this is causing Relationship.php to not have the correct effect when it associates the Export with the Job?

Have I configured something incorrectly when setting up my belongs to association or DB schema?

louismrose

louismrose commented on Jul 5, 2016

@louismrose
ContributorAuthor

Just so I can be a little more sure that it's perhaps not my setup... I've been able to recreate this issue with the following new PHP AR unit test, which I've added to Relationship.php:

    public function test_belongs_to_create_association_sets_foreign_key()
    {
        $event = $this->get_relationship();
        $values = array('city' => 'Richmond', 'state' => 'VA', 'name' => 'Club 54', 'address' => '123 street');
        $venue = $event->create_venue($values);

                // Adding the following lines seems to have no effect
                // $event->save();
                // $event->reload();

        $this->assert_equals($venue->id, $event->venue_id);
    }

The test fails with the following output:

1) RelationshipTest::test_belongs_to_create_association_sets_foreign_key
Failed asserting that 6 matches expected '10'.

Which seems to imply that the event's foreign key column (venue_id) has not been set to the ID of the new venue (10). Looking at the fixtures, it seems that this event (ID 5) has venue_id 6 by default.

Any ideas what the issue might be @koenpunt, @visavi ?

louismrose

louismrose commented on Jul 5, 2016

@louismrose
ContributorAuthor

Ok, I think I might have a solution for this... I've cooked up PR #548.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @louismrose@koenpunt@visavi

        Issue actions

          Foreign key not set when using create_association magic method? · Issue #546 · jpfuentes2/php-activerecord