Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

database join - newby

 
tristanis ginger
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi

I converting from php to a java framework, I am using play. But i am still finding my feet.

I have catgories that contain products, when a product is deleted it just changes a field in the database.

I am having a problem getting the select query right to get out all undeleted products by category.

I want to be able to execute this

SELECT * FROM category
INNER JOIN product WHERE product.cat_id = category.id and product.deleted='FALSE'
ORDER BY category.orderNum asc, product.orderNum asc



the closest i can get is below, however it is incorrect. As I get

@Entity
public class Category extends Model{
@Required
public String name;

public int orderNum;

@OneToMany(mappedBy="cat" , targetEntity=Product.class,
fetch=FetchType.LAZY)
public List<Product> products;


public static List<Category> getCatsandProds(){

return JPA.em().createQuery("SELECT category FROM Category category INNER JOIN FETCH category.products AS catProds WHERE catProds.deleted='false' ORDER BY category.orderNum asc, catProds.orderNum asc").getResultList();

}

please help.

If anyone can point to a good reference point that would also be appreciated,

cheers
 
James Sutherland
Ranch Hand
Posts: 553
 
tristanis ginger
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My results

Meat
Bacon

Fruit
Raspberry
Pineapple
Apple

Fruit
Raspberry
Pineapple
Apple

Fruit
Raspberry
Pineapple
Apple

I am expecting

Meat
Bacon

Fruit
Raspberry
Pineapple
Apple


The join seems to repeat itself for each product in the category
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic