Join three entities/tables: is that possible with ebean?

[waldem...@gmail.com]

I feel like it is not...

I'm trying for a week now how I can join my table A with table B via a join table A_B.
Althought, I've set up the annotations correctly (I believe) I don't know how to do the right call so that these three tables are joined.
All I get is a join of A with  A_B and a second sql with just B, but not like "from A join A_B join B".

Is three anyone out there, who knows how to do that? Or is it alread too much for ebean?

What I did is the following:

class A {

 @OneToMany
  List <A_B> a_b;

  // Is this correct?
  List<B> b = new ArrayList<B>();

  // I'm calling this in my page
  public static Page<A> page(int page, int pageSize, String sortBy, String order, String filter, String searchField) {

              Page<Publication> pageList = find.where()
                .ilike(searchField, "%" + filter + "%")
                .orderBy(sortBy + " " + order)
                .fetch("B")   // If I use this I get a NullPointerExcepteion from the Persistence
                //.fetch("A_B.B") // If I use this I get the two SQLs
                .fetch("A_B")
                .findPagingList(pageSize)
                .setFetchAhead(false)
                .getPage(page);


        return pageList;
  }

}

class A {

 @ManyToOne
 private A a;

 @ManyToOne
 pricate B b;
}

class B {

 @OneToMany
  List <A_B> a_b;
}


So how can I manage to join these three table and where do I have to put my "find" call?
Btw. I got this thing to work with a @ManyToMany mapping, but my join table has an additional field, which I need to order the result.

Thanks
waldy

[Daryl Stultz]

On Jan 18, 2014 8:28 AM, <waldem...@gmail.com> wrote:
>
> I feel like it is not...
>
> I'm trying for a week now how I can join my table A with table B via a join table A_B.

A single query joining three tables is not necessarily the most efficient way to build an object graph, the primary factors being width of the entities (number and size of fields on each side) and cardinality. A good ORM will let you control the method, though. Have a look at FetchConfig to see how to do this.

> Btw. I got this thing to work with a @ManyToMany mapping, but my join table has an additional field, which I need to order the result.

You'll likely need an entity for the join table then.

/D

[Daryl Stultz]

On Jan 18, 2014 10:50 AM, "Daryl Stultz" <kungfum...@gmail.com> wrote:

>
> > Btw. I got this thing to work with a @ManyToMany mapping, but my join table has an additional field, which I need to order the result.
>
> You'll likely need an entity for the join table then.

Actually the ordering thing is going to be a lot more challenging than just modelling the join table. It's easy enough to sort a child collection on an internal property, not sure if you can do that on a property on a different entity.
>
> /D

[waldem...@gmail.com]

Hello Daryl,

thank you very much for your answer.

I've also tried to use something like this

.fetch("articles_authors", "full_name", new FetchConfig().query())

My model ist actually pretty simple.
I have a table articles, where I store some articles with title and a date.
articles
id | title | date

And then there is a table authors, where I store the authors of the article.
It is important that the order of the authors for each article is preserved.
authors
id | full_name

Finally, I have my join_table articles_authors, which has three fields
articles_authors
articles_id | authors_id | id

If I sort this table by the id then, I think, I can preserve the order.

Now I want to do the same as I did with the @ManyToMany annotation,
[debug] c.j.b.PreparedStatementHandle - select t0.id as c0, t1.id as c1, t1.full_name as c2 from articles t0 left outer join articles_authors t1z_ on t1z_.articles_id = t0.id  left outer join authors t1 on t1.id = t1z_.authors_id  where t0.id in (270,229,230,231,232,233,234,235,236,237,209,210,211,58,59,60,61,62,63,64)  order by t0.id

But this is not possible, if I use two @OneToMany annotations.

On the acutal website there is a table with the date, title and a list of the authors for the articles, which should be in the exact same order as the authors were inserted into the join table articles_authors.

If I use FetchConfig()  then I get two SQLs instead of one, which look somewhat odd.
And I also cannot access the authors here.

[debug] c.j.b.PreparedStatementHandle - select t0.id as c0, t1.id as c1, t1.authors_id as c2 from articles t0 left outer join articles_authors t1 on t1.articles_id = t0.id  where t0.id in (270,229,230,231,232,233,234,235,236,237,209,210,211,58,59,60,61,62,63,64)  order by t0.id
[debug] c.j.b.PreparedStatementHandle - select t0.id as c0, t0.full_name as c1 from authors t0 where t0.id in (206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,
232,233,234,235,236,157,322,323,324,325,326,327,328,329,330,342,343,344,345,346,347,348,349,350,351,352,353,
354,355,288,356,357,358,359,360,361,362,363,364,365,366,382,206,206,206,206,206,206,206,206,206,206,206,206,206,
206,206,206,206,206,206,206,206,206,206,206,206,206,206,206,206,206,206,206)

This is actually very simple, but it seems so difficult to make...
I would be very happy, if you could give me a little hint how to handle this
Thanks!

[waldem...@gmail.com]

I have defined my calsses as follows

Publication.class

Entity
@Table(name="articles")
public class Publication extends Model {
    @Id
    public Long id;
    @OneToMany(cascade = CascadeType.ALL,mappedBy="articles")
    public List<Articles_Authors> articles_authors = new ArrayList<Articles_Authors>();
}

Authors.java

@Entity
@Table(name="authors")
public class Author extends Model {
    @Id
    public Long id;
    @OneToMany(cascade = CascadeType.ALL,mappedBy="authors")
    public List<Articles_Authors> articles_authors = new ArrayList<Articles_Authors>();
}

Articles_Authors.java

@Entity
@Table(name="articles_authors")
public class Articles_Authors extends Model {
    @Id
    public Long id;
    @ManyToOne
    public Publication articles;
    @ManyToOne
    public Author authors;
}

Here is the find statement

    Page<Publication> pageList = find.where()
            .ilike(searchField, "%" + filter + "%")
            .orderBy(sortBy + " " + order)

            .fetch("articles_authors.authors", new FetchConfig().query(3).lazy(10))
            .findPagingList(pageSize)
            .setFetchAhead(false)
            .getPage(page);

On the website I can access the authors_id, but how can I get the full_name?

                              @for( (author,index) <- Publication.articles_authors.zipWithIndex) {
                                    @if(index == Publication.articles_authors.size-1) {
                                      <a href="@routes.Application.list(0, currentSortBy, currentOrder, "", "Authors")">@author.id</a>
                                    }else{
                                      <a href="@routes.Application.list(0, currentSortBy, currentOrder, "", "Authors")">@author.id</a>,
                                    }
                              }

[Daryl Stultz]

On Sat, Jan 18, 2014 at 4:10 PM, <waldem...@gmail.com> wrote:

I have defined my calsses as follows

I'm somewhat confused by your mix of singular and plural entity names. You use both Authors and Author as class names, for example. Is this real code?

Authors.java

@Entity
@Table(name="authors")
public class Author extends Model {
    @Id
    public Long id;
    @OneToMany(cascade = CascadeType.ALL,mappedBy="authors")
    public List<Articles_Authors> articles_authors = new ArrayList<Articles_Authors>();
}

On the website I can access the authors_id, but how can I get the full_name?

I don't see full_name defined anywhere (it does not appear to be a property that you can get). Do you need to define it? What does the Authors database table look like?

/D

[waldem...@gmail.com]

I'm somewhat confused by your mix of singular and plural entity names. You use both Authors and Author as class names, for example. Is this real code?

Authors.java

@Entity
@Table(name="authors")
public class Author extends Model {
    @Id
    public Long id;
    @OneToMany(cascade = CascadeType.ALL,mappedBy="authors")
    public List<Articles_Authors> articles_authors = new ArrayList<Articles_Authors>();
}

Sorry, I've mistyped the filename. It should read "Author.java".

 

On the website I can access the authors_id, but how can I get the full_name?

I don't see full_name defined anywhere (it does not appear to be a property that you can get). Do you need to define it? What does the Authors database table look like?

I've  removed too much from the class.

Authors.java

@Entity
@Table(name="authors")
public class Author extends Model {

    @Constraints.Required
    public String full_name;

    @Id
    public Long id;
    @OneToMany(cascade = CascadeType.ALL,mappedBy="authors")
    public List<Articles_Authors> articles_authors = new ArrayList<Articles_Authors>(); 

}

Here are the columns of the authors table

tracker_model=> \d authors
            Tabelle »public.authors«
  Spalte   |          Typ           | Attribute
-----------+------------------------+-----------
 id        | integer                | not null
 full_name | character varying(100) |
Indexe:
    "authors_pkey" PRIMARY KEY, btree (id)

I use "Author" as the class name and "authors" as the table name.
If I have a list instance of the class Author, then I call it authors.

Thanks!

[waldem...@gmail.com]

I've copy and paset the class "Author.java" once again


package models;

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

import play.data.format.*;
import play.data.validation.*;

import play.db.ebean.*;
import com.avaje.ebean.*;

@Entity
@Table(name="authors")
public class Author extends Model {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sq_authors_id")
    public Long id;

    @Constraints.Required
    public String full_name;

    @OneToMany(cascade=CascadeType.ALL, mappedBy="authors")

    public List<Articles_Authors> articles_authors = new ArrayList<Articles_Authors>();

    // some trash removed
}