Accessing the join table in a hql query for a many-to-many relationship in grails

I have 2 domain classes with a many-to-many relationship in grails: decks and cards.

The setup looks like this:

class Deck {
static hasMany = [cards: Card]
}

class Card {
static hasMany = [decks: Deck]
static belongsTo = Deck
}

After I delete a deck, I want to also delete all cards which no longer belong to a deck. The easiest way to accomplish this is to write something like the following sql:

delete from card where card.id not in(select card_id from deck_cards);

However, I can't figure out how to write a HQL query which will resolve to this SQL because the join table, deck_cards, does not have a corresponding grails domain class. I can't write this statement using normal joins because HQL doesn't let you use joins in delete statements, and if I use a subquery to get around this restriction mySQL complains because you're not allowed to refer to the table you're deleting from in the "from" section of the subquery.

I also tried using the hibernate "delete-orphan" cascade option but that results in all cards being deleted when a deck is deleted even if those cards also belong to other decks. I'm going crazy - this seems like it should be a simple task.

edit There seems to be some confusion about this specific use of "decks" and "cards". In this application, the "cards" are flashcards and there can be tens of thousands of them in a deck. Also, it is sometimes necessary to make a copy of a deck so that users can edit it as they see fit. In this scenario, rather than copying all the cards over, the new deck will just reference the same cards as the old deck, and if a card is changed only then will a n开发者_Go百科ew card be created. Also, while I can do this delete in a loop in groovy, it will be very slow and resource-intensive since it will generate tens of thousands of sql delete statements rather than just 1 (using the above sql). Is there no way to access a property of the join table in HQL?

First, I don't see the point in your entities. It is illogical to make a card belong to more than one deck. And it is illogical to have both belongTo and hasMany.

Anyway, Don't use HQL for delete.

If you actually need a OneToMany, use session.remove(deck) and set the cascade of cards to REMOVE or ALL.

If you really want ManyToMany, do the checks manually on the entities. In pseudocode (since I don't know grails):

for (Card card : deck.cards} {
    if (card.decks.size == 0) {
        session.remove(card);
    }
}

I won't be answering the technical side, but challenging the model. I hope this will also be valuable to you :-)

Functionally, it seems to me that your two objects don't have the same lifecycle:

• Decks are changing : they are created, filled with Cards, modified, and deleted. They certainly need to be persisted to your database, because you wouldn't be able to recreate them using code otherwise.
• Cards are constant : the set of all cards is known from the beginning, they keep existing. If you delete a Card once in the database, then you will need to recreate the same Card later when someone needs to put it in a Deck, so in all cases you will have a data structure that is responsible for providing the list of possible Cards. If they are not saved in your database, you could recreate them...

In the model you give, the cards have a set of Decks that hold them. But that information has the same lifecycle than the Decks' (changing), so I suggest to hold the association only on the Deck's side (uni-directional Many-To-Many relationship).

Now you've done that, your Cards are really constant information, so they don't even need to be persisted into the database. You would still have a second table (in addition to the Deck), but that Card table would only contain the identifying information for the Card (could be a simple integer 1 to 52, or two values, depending what you need to "select" in your queries), and not other fields (an image, the strength, some points etc...).

In Hibernate, these choices turns the Many-To-Many relationship to a Collection of values (see Hibernate reference).

With a Collection of Values, Card is not an Entity but a Component. And you don't have to delete them, everything is automatically taken care by Hibernate.