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