I'm attempting to set up a many-to-one relationship between a series of sales memos and transaction records.
/**
* @var TransactionInterface
*
* @ORM\ManyToOne(targetEntity="Twb\Common\Model\Broker\TransactionInterface")
* @ORM\JoinColumn(name="FormNoSeller", referencedColumnName="Form")
*/
private $formnoseller;
/**
* @var TransactionInterface
*
* @ORM\ManyToOne(targetEntity="Twb\Common\Model\Broker\TransactionInterface")
* @ORM\JoinColumn(name="FormNoBuyer", referencedColumnName="Form")
*/
private $formnobuyer;
They are split between two different bundles at the moment ('SalesBundle' and 'BrokerBundle'), and with that in mind I am using interfaces from the SalesMemo entity in SalesBundle to the Transaction entity in BrokerBundle.
For some reason, when I reference either or both of $formnoseller and $formnobuyer in my forms, I notice in dev.log that, after selecting all Transaction rows matching the $formnoseller and/or $formnobuyer fields in the SalesMemos, Doctrine tries to SELECT all rows in the Transaction table (the entity for which TransactionInterface references). This is a bit of a problem, since there is an innumerable amount of rows in the DB, which takes up a lot of memory.
Is there any way to have Doctrine avoid selecting all rows with associations? Or am I even understanding properly how Doctrine does associations? Many thanks for any help.
My understanding of your problem is that you're using an Entity Field Type for $formnoseller and $formnobuyer (or you don't specify the type). Giving the choice to select any élément from the underlying table is the expected behaviour for the Entity Field Type (Used by default for OneToMany relationships)
If you don't whant a select list of all the elements of your table for those Fields, you should use an other form field type. You should also have a look at data transformers in the documentation.
If it were me, I would write a stored procedure and do an inner or outer join as appropriate.
Once upon a time, they called this "client server" code. About 15 years ago, it created such a mess the whole industry moved to n-tier development. I'd like to know how the table joins got placed back into the presentation tier again? ORMs and LINQ-to-SQL are a return to client/server".
If you have to do it this way, do the join in LINQ on the Models. Do not do it with the ORM language.