Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) 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
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!