• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

database join - newby

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
As you get what? What error are you getting?

See,
https://coderanch.com/t/507204/ORM/java/JP-QL-selecting-specific-entities


 
tristanis ginger
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic