CODEWITHSUNDEEP
javaormplayframework-2.0ebean
Nils Kullberg
Nils Kullberg

Reputation: 13

Play Framework 2.x Ebean query ManyToMany for all

I have two models in Play Framework 2/Ebean, 'Article' and 'Tag', which has a ManyToMany-relationship to each other.

Article:

@Entity   
public class Article extends Model {

@ManyToMany
List<Tag> tags;

// ...

}

Tag:

@Entity   
public class Tag extends Model {

@ManyToMany
List<Article> articles;

// ...

}

Given a List<Tag>, how do i write a query for Articles that contains All of the tags in the list?

I am aware that I can use Article.find().where().in("tags", tags) to find all Articles that contains Any of the tags in the list, but I'm looking to be more specific

Upvotes: 1

Views: 572

Answers (1)

Philihp Busby
Philihp Busby

Reputation: 4485

Just use a RawSql query; once you start joining and adding lots of clauses, things get out of hand pretty quickly. At that point it gets really easy to write a query that's hard to debug and even harder to modify in the future. In the end, your query still gets turned into an SQL query, and you aren't saving yourself much aside from satisfying curiosity to see if it can be done.

Set a String sql=

SELECT a.id 
  FROM ARTICLE_TAG a
    JOIN TAG t ON t.id = a.tag_id
  WHERE t.name IN ( :tags )
  GROUP BY a.id
  HAVING COUNT(DISTINCT t.name) = :num_tags

And then do this:

RawSql rawSql =
  RawSqlBuilder
    .parse(sql)
    .columnMapping("a.id", "id")
    .create();

List<Article> articles = Ebean.find(Article.class)
  .setRawSql(rawSql)
  .setParameter("tags", tags)
  .setParameter("num_tags", tags.length)
  .findList();

Upvotes: 2

Related Questions