Inner join in play framework ebean

Question

I have a query which i want to write in my Product model

select * from shop inner join product on product.shops_id=shop.id where product.name=keyword

Shop.java

public class Shop extends Model {

    @Id
    @SequenceGenerator(name="shop_gen", sequenceName="shop_id_seq", allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="shop_gen")
    @Column(name="id")
    public Long id;

    @Required
    public String name;

    @Required
    public String addressLine1;

    public String addressLine2;

    public String addressLine3;

    @Required
    public String city;

    @Required
    public String town;

    @Required
    public String phoneNumber;

    @OneToMany(mappedBy = "shops",cascade = CascadeType.REMOVE)
    public List<Product> products=new ArrayList<>();


    @Required
    @OneToOne
    public String category;

    @Lob
    @Column(name = "shop_pic")
    public  byte[] shop_pic;


    @ManyToOne
    @Required
    public User owner;

}

Product.java

public class Product extends Model {
   @Id
    @SequenceGenerator(name="product_gen", sequenceName="product_id_seq", allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="product_gen")
    @Column(name="id")
    public Long id;
    @Required
    public String name;
    @Required
    public Float price;
    @OneToOne
    @Required
    public String category;
    @ManyToOne
    public Shop shops;
}

My main aim is to find list of shops which has a particular product.

where keyword change everytime. i have referred to similar question but dint understand any of them. Any help would be appreciated

score 1 · Answer 1 · answered Apr 29 '14 at 07:22

1

With proper relations in your models it would be:

Shop.find.where().like("product.name", keyword).findList();

Anyway we don't know nothing about your models.

answered Apr 29 '14 at 07:22

biesior

55,576
10
125
182

score 0 · Answer 2 · answered Dec 19 '14 at 21:54

This is solution to your problem:

At first add finder to Product class

public static Finder<Long,Product> find = new Finder<Long,Product>(
    Long.class, Product.class
);

Change attribute shops to shop in Product class. It is inappropriate to give plural name when there is single object.

Then to retrieve list of shops containing a product of a given name add code similar to this:

List<Product> productList = Product.find.where().like("name", keyword).findList();
List<Shop> shopList = new ArrayList<Shop>();
for(Product product:productList)
    shopList.add(product.shop);

Inner join in play framework ebean

2 Answers2