<div dir="ltr"><div dir="ltr"><div dir="ltr">Hi Agatha!<br><br>Do I understand correctly that you have pre-existing database and want to define PonyORM models to work with it?<br><br>At the first sight, the tables looks a bit strange. With a good table design, the primary key should consist of a minimum set of columns, and the rest columns should functionally depend on the entire primary key. In your `branches` table, `id` is already unique, so primary key should be `id` and not a combination of `id` and `client`.<br><br>PonyORM assumes that the tables are properly normalized, so it is not possible to define exactly these tables with Pony, but is is possible to define models which are "similar enough", so you can do SELECTs and UPDATEs without any issues. As the database is already exists, you will not call "CREATE TABLE" commands anyway, so differences in the exact definition of foreign keys are not important.<br><br>You can try to define models in the following way:<br><br> class Branch(db.Entity):<br> _table_ = "branches"<br> id = PrimaryKey(int)<br> client = Required(str)<br> parent = Optional("Branch", column="pid")<br> config_items = Set("ConfigItem")<br><br> class ConfigItem(db.Entity):<br> _table_ = 'branches_config'<br> id = PrimaryKey(int, auto=True)<br> branch = Required("Branch", column="bid")<br> client = Required(str)<br><br>In these models, `ConfigItem` entity assumes that the foreign key to `Branch` is just `id`. It should work because when constructing SQL query `id` alone is sufficient to find unique instance of a `Branch` object. But if you create a new `ConfigItem` object, you need to always specify correct `client` attribute value which should be the same as in the linked Branch object, otherwise the database will throw an error about the violation of a foreign key constraint. You can use the following helper functions when creating a new objects:<br><br> def create_branch(id, client, parent=None):<br> return Branch(id=id, client=client, parent=parent)<br><br> def create_config_item(branch):<br> return ConfigItem(branch=branch, client=branch.client)<br><br>Actually, the table `branches_config` looks useless without any additional columns, as it does not contains any additional information comparing to the `branches` table. Anyway, it should works as expected, I think.<br><br>By the way, the main discussion is moved from this mailing list to a telegram channel: <a href="https://telegram.me/ponyorm" target="_blank">https://telegram.me/ponyorm</a>. But this mailing list is still functioning too<br><br>Hope I answered your question<br><br>Best regards,<br>Alexander<br><br><br><br><br><br><br><br><br></div></div></div><br><div class="gmail_quote"><div dir="ltr">On Thu, Oct 11, 2018 at 10:50 AM Agatha Ng <<a href="mailto:agathang93@gmail.com" target="_blank">agathang93@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div>Hello,<br></div><div><div class="gmail_quote"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div><br></div><div>I ran into some problem when building the entity relationship with Pony ORM. Following are two tables in the database.</div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><font size="1">TABLE `branches` (<br> `id` int(11) NOT NULL,<br> `client` varchar(64) NOT NULL,<br> `pid` int(11) DEFAULT NULL,<br> PRIMARY KEY (`id`,`client`),<br> KEY `branches_ibfk_1` (`pid`),<br> CONSTRAINT `branches_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `branches` (`id`)<br>)<br>TABLE `branches_config` (<br> `id` int(11) NOT NULL AUTO_INCREMENT,<br> `bid` int(11) NOT NULL,<br> `client` varchar(64) NOT NULL,<br> PRIMARY KEY (`id`),<br> KEY `branches_config_ibfk_1` (`bid`,`client`),<br> CONSTRAINT `branches_config_ibfk_1` FOREIGN KEY (`bid`, `client`) REFERENCES `branches` (`id`, `client`)<br>)</font></blockquote><div> </div><div>For the 'branches' table, 'id' is a part of the primary key and also referred by 'pid'. I tried to define 'id' as a Set attribute to satisfy the one-to-many self-reference. But Set attribute cannot be a part of primary key. Could you tell me how to define such a relationship?<br></div><div><br></div><div>For the 'branches_config' table, (bid, client) refers 'branches' (id, client). How to define the reference between two composite key?</div><div><br></div><div>Thank you and I look forward to hearing from you.</div><div><br></div><div>Best wishes,</div><div>Agatha</div></div></div></div></div>
</div></div>
_______________________________________________<br>
ponyorm-list mailing list<br>
<a href="mailto:ponyorm-list@ponyorm.org" target="_blank">ponyorm-list@ponyorm.org</a><br>
<a href="/ponyorm-list" rel="noreferrer" target="_blank">/ponyorm-list</a><br>
</blockquote></div>