Ebean + ManyToMany joins

I've got an application that allows the user to select certain keywords, the app then listens for tweets containing those keywords and stores them in a DB.

For a given user, they have a list of keywords. What I'm trying to do is get some statistics on how each keyword is performing on a given day, month, etc, by counting how many tweets with that keyword happened on a given day/month.

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;
}

I think the best way to do it would be Tweet.find --where keyword=kw and date > d-- but I do not know how to do this. Should I be using fetch? Here's a similar query in plain SQL below.

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 |
+---------------------+----------+

So, select from tweet where (tweet id is in user keyword list)

I'd also like to group by:

  cast(t.datetime as date)

and having

  date > 2014-02-02 //example date

on certain queries. Thanks for any help guys!!

Answers


This may not be exactly what you're looking for, but maybe it'll get you started. There's ways to do joins, or using a Query in the Tweet.find.where() below, instead of using the List of keywords from a Person, that may be easier. There some joins in the git hub examples, but they're a bit old

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());
    }

Need Your Help

Using a different service contract for DotNetOpenAuth

asp.net web-services dotnetopenauth datacontract

Recently the company I work for has decided to use DotNetOpenAuth to authorize users who want to make use of a certain webservice I've been developing.