CODEWITHSUNDEEP
postgresqlmany-to-many
Pablo Fernandez
Pablo Fernandez

Reputation: 287840

Many to many relationship to itself in PostgreSQL

Using PostgreSQL, I have a table that should have a many to many relationship to itself. The table is called tools and I'm doing the many to many with a secondary table, called integrations, that has two fields, tool_a_id and tool_b_id.

Is there a better way? The problem with this is that whenever I want to find all integrations I need to check for both tool_a and tool_b, as in:

SELECT * FROM integrations
WHERE tool_a_id = x AND tool_b_id = x

or when I want to select all tools that integrate with another one, I have to do:

SELECT "tools".* FROM tools, integrations
WHERE ((tools.id = integrations.tool_a_id AND integrations.tool_b_id = x) 
   OR (tools.id = integrations.tool_b_id AND integrations.tool_a_id = x))

This for example, doesn't allow to easily define integrations as a relationship in Rails as Rails expects one and only one foreign key to match. Is there a better way? It feels inelegant. I don't mind being stuck with PostgreSQL.

Upvotes: 3

Views: 391

Answers (1)

Martín
Martín

Reputation: 19

Well, that's the way we've learned it from RDBMS books. ;)

But in your SQL example, you're not defining the objects correctly. I think what you might be looking for is something more like this (with the same database schema):

SELECT t1.* FROM tools t1 JOIN integrations i ON (t1.id = i.tool_a_id)
       JOIN tools t2 ON (t2.id = i.tool_b_id)
WHERE t2.id = x

That is more elegant, as it labels t1 as the list of tools to pick from and t2 as the list from which I will select the one tool I want to know which are the ones integrated to it.

Upvotes: 1

Related Questions