-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 
Author Message
 Post subject: Any kind of relationship through PostgreSQL array
PostPosted: Thu Apr 14, 2016 6:23 am 
Newbie

Joined: Thu Apr 14, 2016 6:06 am
Posts: 4
Given a database structure as:
Code:
CREATE TABLE users (
   id             SERIAL            NOT NULL,
   name           CHARACTER VARYING NOT NULL,
   email          CHARACTER VARYING NOT NULL,
   password       CHARACTER VARYING NOT NULL,
   settings       JSONB             NOT NULL DEFAULT '{}' :: JSONB,
   permissions    JSONB             NOT NULL DEFAULT '{"allow": [], "deny":[]}' :: JSONB,
   groups         INTEGER []        NOT NULL DEFAULT ARRAY [] :: INT [],
   ...
);
CREATE TABLE groups (
   id          SERIAL            NOT NULL,
   name        CHARACTER VARYING NOT NULL,
   permissions JSONB             NOT NULL DEFAULT '{"allow": [], "deny": []}' :: JSONB,

   type        INTEGER           NOT NULL,
   state       INTEGER           NOT NULL,
   flags       BIGINT            NOT NULL,

   CONSTRAINT groups__pk PRIMARY KEY (id),
   CONSTRAINT groups__name__type__uniq UNIQUE (name, type)
);


There's a many-to-many mapping of "users" <-> "groups" through "users.groups" column which is an array of "groups.id". Is there any possibility to declare such a mapping programmatically with:
hibernate-core:4.3.11.Final
hibernate-entitymanager:4.3.11.Final

?


Top
 Profile  
 
 Post subject: Re: Any kind of relationship through PostgreSQL array
PostPosted: Thu Apr 14, 2016 7:00 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
Due to lack of standardization in regards to column array, Hibernate does not offer a generic type for database array. You can write your own custom type, like this one.

As for the relation, there is nothing based on arrays either.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Any kind of relationship through PostgreSQL array
PostPosted: Thu Apr 14, 2016 7:05 am 
Newbie

Joined: Thu Apr 14, 2016 6:06 am
Posts: 4
mihalcea_vlad wrote:
As for the relation, there is nothing based on arrays either.

I understand that there's no vendor-provided implementation for this kind of relationship binding. But is there a starting point to look at to write my own implementation for at least @OneToMany based on array column types like as for just array type itself you linked above?


Top
 Profile  
 
 Post subject: Re: Any kind of relationship through PostgreSQL array
PostPosted: Thu Apr 14, 2016 7:15 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
You'll have to implement `UserCollectionType` and adapt the logic that's provided in that blog post that I mentioned you.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Any kind of relationship through PostgreSQL array
PostPosted: Thu Apr 14, 2016 10:19 am 
Newbie

Joined: Thu Apr 14, 2016 6:06 am
Posts: 4
mihalcea_vlad wrote:
You'll have to implement `UserCollectionType` and adapt the logic that's provided in that blog post that I mentioned you.

As I've successfully implemented a raw Integer[] class field based on a database's ARRAY[] column, I've stuck with there's no mention of relationship in the blogpost you've linked. As I understand, I need to implement something like query builder proxy for this new UserType that would compile this special case's queries.

Code:
   @Type(type = "i.g.p.t.IntArrayType")
   @Column(name = "groups", nullable = false)
   private Integer[] groupIds = new Integer[]{};

   @OneToMany(targetEntity = Group.class)
   @JoinColumn(name = "groups")
   private List<Group> groups;

With this code:
- `groupIds` works perfectly just as planned (thanks, your link helped very much)
- `groups` raises SQLProgrammingException with both @JoinColumn(name = "groups") and @JoinColumn(referencedColumnName = "groups") that leads to the fact: query builder tries to query field "groups.groups" (which does not exist). While in this case LAZY loading strategy should produce
Code:
SELECT ... FROM groups WHERE id = any(?)  -- here "users.groups" value should be bound as the parameter value


With
Code:
   @OneToMany
   @JoinColumn(referencedColumnName = "groups", table = "users", name = "id")
   private List<Group> groups;

- org.hibernate.AnnotationException: Cannot find the expected secondary table: no users available for i.g.p.m.Group

Could you point me to the docs what should I implement on my own for this type of relationship?


Top
 Profile  
 
 Post subject: Re: Any kind of relationship through PostgreSQL array
PostPosted: Thu Apr 14, 2016 10:27 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1638
Location: Romania
For the @OneToMany, you'll have to implement the UserCollectionType, as I suggested you. There is no resource that I can give you for that. I'm not sure if that's going to work or not.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Any kind of relationship through PostgreSQL array
PostPosted: Mon Apr 18, 2016 4:42 am 
Newbie

Joined: Thu Apr 14, 2016 6:06 am
Posts: 4
Well, for now I've just ended with just PG ARRAY[] <-> UserType type declaration. Still can't declare a relational mapping that is based on this type. Will stay at the query-time binding approach for now.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.