OneToMany mapping from Table to Foreign Table [Symfony 2 / Doctrine]

Question

From what I've gathered, Symfony 2 / Doctrine uses the database definitions (foreign key constraints in my case) to map relations between entities. I have two tables in particular that I want to be able to relate from both sides, but I do not want to create redundant foreign keys in each table. In this case, I have an Account table, and a Transaction table.

Account Table

CREATE TABLE "account" ( "account_id" BIGSERIAL NOT NULL, "name" VARCHAR (100) NOT NULL, "date_created" TIMESTAMP (6) WITH TIME ZONE NOT NULL, "date_modified" TIMESTAMP (6) WITH TIME ZONE, CONSTRAINT "pk-account-account_id" PRIMARY KEY ("account_id"), );

Transaction Table

CREATE TABLE "transaction" ( "transaction_id" BIGSERIAL NOT NULL, "account_id" BIGINT NOT NULL, "amount" MONEY NOT NULL, "date_created" TIMESTAMP (6) WITH TIME ZONE NOT NULL, "date_modified" TIMESTAMP (6) WITH TIME ZONE, CONSTRAINT "pk-transaction-transaction_id" PRIMARY KEY ("transaction_id"), CONSTRAINT "fk-transaction-account_id-account-account_id" FOREIGN KEY ("account_id") REFERENCES "account" ("account_id") ON DELETE RESTRICT ON UPDATE CASCADE, );

When I generate the entities using php bin/console doctrine:generate:entities I see that the transaction entity has an $account property, but my account entity does not have a $transaction entity. I assume this is because I do not define a foreign key constraint in my account table.

In my code, I create my account object by the following:

$accounts = $this->getDoctrine() ->getRepository('BalancesBundle:Account') ->findAll();

I then would want to iterate over the array to get the total balance for each account. In the long-term, I'd like to create a helper method inside my account entity that would call getTransactions() to add up all of the transactions into one sum total.

Is this possible? I feel like I'm missing something, and that my only recourse would be to do this from within the transaction entity. I would like to avoid doing this from the transaction entity if possible.

Just a note: Doctrine doesn't use DB to map relations. It uses mappings. Also doctrine:generate:entities doesn't generate entities from DB, but from mappings, so I assume you have created them first. Since you started with sql tables, I assume that you used doctrine:mapping:import command. If you want to have bidirectional relation you should modify your mappings, not DB. Actually you should work with mappings and not DB. — Jakub Matczak
– Jakub Matczak, Commented Jun 1, 2016 at 6:29
You are right, I created the tables first and then used import. At the time I was unaware that Doctrine could generate the necessary SQL for me. I probably will be going that route from here on out. Thanks for the head's up. — dohpaz42
– dohpaz42, Commented Jun 1, 2016 at 15:41

Shakealot · Accepted Answer · 2016-06-01 07:58:16Z

From what I get, you can't get transaction entities of the account entity. The weird thing is that you don't have "transactions" property inside your ccount entity, am I right ? Seems like a bad mapping.

Take a look at Doctrine documentation, you have to define your "transcations" property inside your "account" entity and map it with the OneToMany association.

Then you can use "php bin/console do:ge:entities AppBundle:Account"

This answer, along with the comment from @dragoste, got me the total (pun intended) solution. I added the transaction property to my Account entity, along with the OneToMany annotation, updated my Transaction entity with the needed "inversedBy", then I had to edit my Account mapping to add the oneToMany definition, re-ran the generate:entities, and viola, it works. Thank you!

sketchthat · Accepted Answer · 2016-06-01 05:28:26Z

If your entities are setup correctly then you should be able to access the transactions from the account.

foreach ($accounts as $account) { $transactions = $account->getTransactions(); $transaction_total = 0; foreach($transactions as $transaction) { $transaction_total += $transaction->getAmount(); } echo 'Transactions Total: '.$transaction_total; }

Collectives™ on Stack Overflow

OneToMany mapping from Table to Foreign Table [Symfony 2 / Doctrine]

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related