Bidirectional many-to-many without foreign key

[Radek Krzak]

Hello,

I have a ManyToMany relationship between two tables, although I am not using "foreign keys" to establish it (this is a deliberate decision that I wouldn't like to explain more at this point). However, I am struggling to define this relationship using SQLAlchemy. At the very beginning, I found the primaryjoin that I tried using with partial success. I managed to get it working with backref (and with only one side being a list, which is not what I wanted), but to be honest it was rather by accident. I keep missing the information/examples of how exactly the foreign_keys and remote_side arguments are and how to use them effectively. I went through every single SO question on that topic and the documentation that mainly involves self joins (that is understandable as my use case is rather uncommon).

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(UUID, primary_key=True)
    address_id = Column(String(8), nullable=False)
    is_deleted = Column(Boolean, server_default=expression.false(), nullable=False)
    companies = relationship("Company", primaryjoin="...", back_populates="addresses")

class Company(Base):
    __tablename__ = 'companies'
    id = Column(UUID, primary_key=True)
    address_id = Column(String(8), nullable=False)
    addresses = relationship("Address", primaryjoin="...", back_populates="companies")

I would like a bidirectional relationship with using back_populates (I don't like using backref as my models are often scattered across different modules and I prefer to be explicit). I am not using primary keys on purpose as I want to keep the historical addresses data just by marking address by deleted (and keeping companies and address linked via address_id). Referential integrity is enforced by the 3rd party provider where I import the data from, so this is not an issue in my case.

Any help on how I should define the relationship on both models is appreciated. I really want to understand foreign_keys and remote_side because at the moment I cannot seem to be able to wrap my head around, even after reading https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#creating-custom-foreign-conditions :(

Thanks in advance

[Simon King]

Company has an address_id column, which means each company only has a
single address, doesn't it? ie. this is a many-to-one relationship,
not a many-to-many?

Simon

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/e0c7d5f6-58fe-410e-b0af-1d60973d3ba9%40googlegroups.com.

[Radoslaw Krzak]

Hey Simon,

address_id is not a primary key and is not unique across the addresses table. One Company can reference many addresses rows (because there might be multiple rows with the same address_id value, hmm maybe I should have used a different name) and vice-versa, one Address can be referenced by many companies, so it's many to many. The point is I am not linking by primary keys, but simply by the value of address_id column. This is a deliberate action and I would love to see how I could set up such a relationship using primaryjoin, foreign_keys and remote_side (if necessary) :)

Radek

[Simon King]

So conceptually, an address_id represents a *group* of addresses. A
company can be associated with exactly one group of addresses, and one
group of addresses can be shared by multiple companies. Is that right?

A normal many-to-many relationship involves an association table. When
you add and remove items to one side or the other of these
relationships, SQLAlchemy will add and delete rows from the
association table. I'd be amazed if SQLAlchemy would be able to figure
out how to add and remove objects from your relationship. Do you need
it to be writable, or would a readonly relationship be good enough?

Simon

> CONFIDENTIAL
>
> This e-mail and any attachments are confidential and intended solely for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this e-mail in error, please notify the sender immediately, delete it from your system and do not copy, disclose or otherwise act upon any part of this e-mail or its attachments.
>
> Internet communications are not guaranteed to be secure or virus-free. Revolut does not accept responsibility for any loss arising from unauthorised access to, or interference with, any Internet communications by any third party, or from the transmission of any viruses. Replies to this e-mail may be monitored by Revolut for operational or business reasons.
>
> Any opinion or other information in this e-mail or its attachments that does not relate to the business of Revolut is personal to the sender and is not given or endorsed by Revolut.
>
> Registered Office: 9th Floor 107 Cheapside, London, EC2V 6DN, United Kingdom. Main Office: 4th Floor, The Columbus Building, 7 Westferry Circus, London, E14 4HD, United Kingdom. Revolut Ltd is authorised and regulated by the Financial Conduct Authority under the Electronic Money Regulations 2011, Firm Reference 900562. Revolut Ltd is an Appointed Representative of Lending Works Ltd (for the activity of “operating an electronic system for lending” only).

>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/65f13263-1dd8-4705-8329-ef858a3fe070%40googlegroups.com.

[Radoslaw Krzak]

Yes, exactly :)

My goal was to set up read only relationship. I have no idea if writable one is possible without the secondary table.

[Simon King]

You'd be able to use traditional relationship definitions if you
defined an "AddressGroup" table where address_id was unique. Company
and Address would both have one-to-many relationships to the
AddressGroup table, and you could probably use association proxies for
Company.addresses and Address.companies. Would you be willing to
define that table?

Simon

[Richard Damon]

On 1/28/20 9:03 AM, 'Radoslaw Krzak' via sqlalchemy wrote:
> Hey Simon,
>
> *address_id* is *not* a primary key and is not unique across the
> *addresses* table. *One* /Company/ can reference *many* addresses rows

> (because there might be multiple rows with the same

> *address_id* value, hmm maybe I should have used a different name) and
> vice-versa, *one* /Address/ can be referenced by *many* companies, so

> it's many to many. The point is I am not linking by primary keys, but

> simply by the value of *address_id* column. This is a deliberate

> action and I would love to see how I could set up such a relationship

> using *primaryjoin, foreign_keys *and***remote_side (if necessary)* :)

>
> Radek
>
> On Tuesday, January 28, 2020 at 9:28:06 AM UTC, Simon King wrote:
>
> Company has an address_id column, which means each company only has a
> single address, doesn't it? ie. this is a many-to-one relationship,
> not a many-to-many?
>

I suspect this design has some unintended consequence that you might not
have thought about. If a company that has multiple locations shares one
of its locations with another company, but not all of them, either you
need to incorrectly imply that the second company is at all the first
companies locations (give them the same address-id) or imply that they
don't share that location (give them different address-id). This latter
case requires that you might need to create multiple records for the
exact same place (with different address-ids) which would be a violation
of the normal form, and makes operations for creating these
relationships very complicated.

--
Richard Damon THis

[Radoslaw Krzak]

Simon, thanks for the suggestion with association proxies I used them once in the past.
Richard, I see your point this design might indeed have some unintended consequence.

As I mentioned in the first message I am aware that it's an unusual approach and this is a deliberate choice. I just wanted to know if it's possible to set up this kind of relationships using primaryjoin without having secondary table.

I did some digging in the source code and I think that it's not possible to define MANYTOMANY with back_populates without using secondary table, cause of the following:

if (
    self.direction in (ONETOMANY, MANYTOONE)
    and self.direction == other.direction
):
    raise sa_exc.ArgumentError(
        "%s and back-reference %s are "
        "both of the same direction %r.  Did you mean to "
        "set remote_side on the many-to-one side ?"
        % (other, self, self.direction)
    )

I managed to use:

companies = relationship(
    "Company",

    foreign_keys="Company.address_id",
    primaryjoin=(
        "Company.address_id == Address.address_id"
    ),
    viewonly=True
)

and:

addresses = relationship(
    "Address",

    foreign_keys="Address.address_id",
    primaryjoin=(
        "Address.address_id == Company.address_id"
    ),
    viewonly=True
)

Even with viewonly=True it appears that I can still mutate the InstrumentedList, but as far as I understand the changes SQLAlchemy will not try to persist the operations, right?
Sadly, I still don't really know the meaning of foreign() and remote() and how to use confidently.