Ebean + ManyToMany连接

Question

我有一个应用程序，允许用户选择特定的关键字，然后应用程序监听包含这些关键字的tweet，并将它们存储在DB中。

对于给定的用户，他们有一个关键字列表。我想要做的是统计每个关键字在给定的日期、月份等上的执行情况，方法是统计在给定的一天/一个月中发生了多少条带有该关键字的tweet。

public class Person extends Model {
    @id
    long id;

    @ManyToMany 
    List<Keywords> keywords = new ArrayList();

}
public class Keyword extends Model {

@Id
int id;
String keyword;

@ManyToMany
public List<Tweet> tweets = new ArrayList();
}

public class Tweet extends Model {

@Id
int id;
    TimeStamp datetime;
}

我认为最好的方法是Tweet.find -- keyword=kw和date >d-但是我不知道怎么做。我应该用“取”吗？下面是普通SQL中的类似查询。

select datetime, count(*) from tweet t left outer join keyword_tweet on t.id=keyword_tweet.tweet_id group by cast(t.datetime as date) having t.datetime > '2014-02-02';

+---------------------+----------+
| datetime            | count(*) |
+---------------------+----------+
| 2014-02-02 13:27:45 |        1 |
| 2014-02-08 05:14:04 |        2 |
| 2014-02-09 08:34:31 |        1 |
| 2014-02-12 12:42:02 |        1 |
| 2014-02-13 06:00:09 |        2 |
| 2014-02-14 00:47:04 |        2 |
| 2014-02-15 07:26:30 |        6 |
| 2014-02-16 01:00:00 |       21 |
| 2014-02-17 00:06:50 |      916 |
| 2014-02-18 18:08:56 |        1 |
| 2014-02-19 01:28:40 |        1 |
| 2014-02-24 16:45:11 |        1 |
| 2014-02-26 14:43:54 |        4 |
| 2014-02-27 08:24:09 |        9 |
| 2014-02-28 05:08:16 |      411 |
+---------------------+----------+

因此，从tweet中选择哪里(tweet id在用户关键字列表中)

我还想根据以下几个方面进行分组：

  cast(t.datetime as date)

而拥有

  date > 2014-02-02 //example date

在某些问题上。谢谢你们的帮助！！

Answer 1

这可能不是你想要的，但也许它会让你开始。有一些方法可以实现联接，或者在下面的Tweet.find.where()中使用查询，而不是使用来自某个人的关键字列表，这可能更容易一些。在git集线器示例中有一些加入，但它们有点老了

package models.test;

import play.db.ebean.Model;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by aakture on 3/7/14.
 */
@Entity
public class Person extends Model {
    @Id
    long id;

    @OneToMany(cascade = CascadeType.ALL)
    List<Keyword> keywords = new ArrayList();

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public List<Keyword> getKeywords() {
        return keywords;
    }

    public void setKeywords(List<Keyword> keywords) {
        this.keywords = keywords;
    }

    public static Finder<Integer, Person> find = new Finder<Integer, Person>(
            Integer.class, Person.class);
}

package models.test;

import play.db.ebean.Model;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class Keyword extends Model {

    @Id
    Integer id;

    String text;


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getText() {
        return text;
    }

    public void setText(String text) {
        this.text = text;
    }
    public static Finder<Integer, Keyword> find = new Finder<Integer, Keyword>(
            Integer.class, Keyword.class);

}

package models.test;

import play.db.ebean.Model;

import javax.persistence.*;
import java.sql.Date;
@Entity
public class Tweet extends Model {

    @Id
    Integer id;

    Date datetime;

    @OneToOne
    Keyword keyword;

    public void setId(Integer id) {
        this.id = id;
    }

    public Keyword getKeyword() {
        return keyword;
    }

    public void setKeyword(Keyword keyword) {
        this.keyword = keyword;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public Date getDatetime() {
        return datetime;
    }

    public void setDatetime(Date datetime) {
        this.datetime = datetime;
    }

    public static Finder<Integer, Tweet> find = new Finder<Integer, Tweet>(
            Integer.class, Tweet.class);

}

    @Test
    public void testTweets() {
        List<Keyword> keywordList = new ArrayList<Keyword>();
        Keyword keyword = new Keyword();
        keyword.setText("hello");
        keywordList.add(keyword);

        keyword = new Keyword();
        keyword.setText("world");
        keywordList.add(keyword);
        Person person = new Person();
        person.setKeywords(keywordList);
        person.save();

        keyword  = Keyword.find.where().eq("text", "hello").findUnique();
        log.info("keywords " + keyword);
        java.sql.Date now = new java.sql.Date(new Date().getTime());
        for(int i = 0; i < 10; i++) {
            Tweet tweet = new Tweet();
            tweet.setKeyword(keyword);
            tweet.setDatetime(now);
            tweet.save();
        }
        log.info("now is        " + now.getTime());
        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.DAY_OF_YEAR, -7);
        java.sql.Date oneHourAgo = new java.sql.Date(cal.getTimeInMillis());
        log.info("oneHourAgo is " + oneHourAgo.getTime());
        List<Keyword> personsKeywords = person.getKeywords();
        List<String> keyWordsStringList = new ArrayList<String>();
        for(Keyword kw : keywordList) {
            keyWordsStringList.add(kw.getText());
        }
        List<Tweet> tweets = Tweet.find.where().and(Expr.in("keyword.text", keyWordsStringList), Expr.gt("datetime", oneHourAgo)).findList();
        log.info("tweets has " + tweets.size());
    }