One-to-Many relation in postgreSQL and how to test it

Question

I have an issue where I have an object that takes some fields, and one of these fields is a List (0-2, so nullable) and need to save it in a database (PostgreSQL is what I use). Now I know that having a separate table with a foreign key reference is recommended, one-to-many relationship. So I have to establish a foreign key from the child table to the main entity. I used @MappedCollection in Spring Data JDBC to map the relation. I also don't need to a separate repository for the child table because Spring Data JDBC treats it as a part of the aggregate owned by the parent Table.

record ParentEntity(@Id Long id, other fields, @MappedCollection(idColumn = "parent_table_id") Set<ChildEntity> childField){}
record ChildEntity(String childField){}

now when testing it manually, everything works fine. The problem is that I need to test this. I tried using LEFT JOIN because obviously the tests will always complain that it can't find the child column when we try to retrieve the parent entity This is the query I used to solve it:

"SELECT pt.*, ct.child_table " +
            "FROM parent_table pt " +
            "LEFT JOIN child_table ai ON ct.parent_table_id = pt.id " +
            "WHERE pt.position_id = ?"

But the problem is that this will now give me two rows with the same fields except the child column, and thats not what we want, we want to have only one row, because the parent entity takes a set

Any ideas? the framework takes care of the production code and I don't really know how to do with the tests.

This is how SQL works; it gives you one record for each match. If a parent has two children, you get two records. — Frank Heikens
– Frank Heikens, Commented Mar 11 at 14:48

Rahul · Accepted Answer · 2025-03-11 10:32:16Z

0

You're on the right track with setting up a one-to-many relationship in PostgreSQL using Spring Data JDBC. The issue you're facing is common when using LEFT JOIN, It duplicates the parent row for each child, which isn't ideal since you're using a Set<ChildEntity>.
Since Spring Data JDBC automatically maps child entities when fetching a parent, you don’t need to manually join tables. Instead of using LEFT JOIN, just fetch the parent entity directly or fetch separately the child records separately.
SELECT * FROM parent_table WHERE position_id = ?; or SELECT * FROM child_table WHERE parent_table_id = ?;

answered Mar 11 at 10:32

Rahul

1

Sign up to request clarification or add additional context in comments.

2 Comments

18929371 Mar 11 at 10:39

the issue I face when I try running SELECT * FROM parent_table WHERE position_id = ?; is that the test fails because it expects a child_column, this is why I went with the LEFT JOIN solution and then realized that it actually duplicate the parent row for each child. Maybe I was not clear, what faild is the tests I'm writing, not the code itself

18929371 Mar 11 at 10:54

This is what I get

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT * FROM parent_table WHERE position_id = ?] Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "child_field" not found [42122-232]

Collectives™ on Stack Overflow

One-to-Many relation in postgreSQL and how to test it

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related