0

I have two tables with many-to-many relationships which called Position and Content.

Mapping is collect, because insert and and other queries all can work properly.

and I use 

Criteria c = sessionFactory.getCurrentSession().createCriteria(Content.class);
c.add(Restrictions.isEmpty("position"));

to get the dataset which means all the Contents are not taken by any Position.

It works properly.

And then, I add:

Criteria c = sessionFactory.getCurrentSession().createCriteria(Content.class);
c.add(Restrictions.isEmpty("position"));
c.createAlias("position", "p");
c.add(Restrictions.eq("p.id", 100));

which means that to get all the Contents are not taken by Position 100.

I get a empty return.

And some suggested me using:

c.createAlias("position", "p");
c.add(Restrictions.not(Restrictions.eq("p.id", 100)))

It worked but with the result like this sql:

SELECT * FROM `position_has_content` WHERE content_id not in (select content_id from `position_has_content` where position_id = 100)

while I need the result like this:

SELECT * FROM `content` WHERE `content`.id not in (select content_id from `position_has_content` where position_id = 100)

Which is such different.

May I know what I missed here please?

I will keep updating when the problem solved.

The tables in the case like below:

position          Position_has_content       content
id                     position_id             id
                       content_id

so, it is many-to-many case, they are already mapped properly in hibernate.

the phase of mapping relationships:

@ManyToMany(mappedBy = "content")
    private Set<Position> position;
-------------------------------------------------------------
@ManyToMany(fetch = FetchType.EAGER,cascade=CascadeType.ALL)
    @JoinTable(name = "position_has_content",  joinColumns = { 
            @JoinColumn(name = "position_id") }, 
            inverseJoinColumns = { @JoinColumn(name = "content_id") })
    @OrderBy("position")
    private Set<Content> content;

the mapping is correct, because all the other operations(normal EAGER query without "not in", insert, delete of both two sides) could work properly.

Charles Li
  • 1
  • 2
  • Criteria c = sessionFactory.getCurrentSession().createCriteria(Content.class); c.createAlias("position", "p"); c.add(Restrictions.eq("p.id", 100)); List nc = c.list(); List idList = new ArrayList(); for(Content cont:nc) { idList.add(cont.getId()); } Criteria c2 = sessionFactory.getCurrentSession().createCriteria(Content.class).setFetchMode("position", FetchMode.SELECT); if(nc.size()!=0) c2.add(Restrictions.not(Restrictions.in("id",idList))); return c2.list(); I know it is not efficient and effective, so any ideas to help? thank you. – Charles Li Feb 04 '15 at 00:54

1 Answers1

0

Can you please post your complete entities and perhaps some example entries in the database to better illustrate how you're attempting to filter the results. I expect there are numerous ways to achieve the result you are expecting but you might consider using a DetachedCriteria and exists/not exists clauses. Regardless, I'll take a closer look once more information is available!

Sean A
  • 31
  • 3
  • Sure, here is the detail. – Charles Li Jan 24 '15 at 09:21
  • I used, but subquery does not support good way of "not in", or I just do not know how to make it happen. Thanks a lot. – Charles Li Jan 24 '15 at 09:28
  • @CharlesLi - Can you please post your complete entities and hibernate mapping file (if you are using an XML based configuration). Can you please double check that your relationships are correct as well. I can understand why/how "Content" would have a position, but am not understanding how that then becomes a many-to-many relationship. Can a piece of a content have more than one position? Can a position be consumed by more than one piece of Content? – Sean A Jan 25 '15 at 23:26
  • thank you for helping, because Position maintains a list of Contents which will be analyse for how many and kind of contents be used ,using or waiting to use. – Charles Li Jan 26 '15 at 02:23