0

I have two entities Outlet and products having one to many relationship as follows-

@Entity
public class Outlet{

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
Long id;

@Fetch (FetchMode.SELECT)
@OneToMany(mappedBy = "outletProduct",fetch = FetchType.LAZY)
List<Product> products;

and 

@Entity
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
Long id;
Boolean isActive;

@ManyToOne
@JoinColumn(name = "outletId")
Outlet outletProduct;

Now i want Outlets where products isactive is true.

I am writing follwing JPQL for-

 @Query("Select op From Outlet op  join op.products pro where pro.isActive=1  order by op.id")
 List<Outlet>  findAllByVarietiesPriceTimePeriodIn( );

But i am still getting outlets with prodcuts isactive false. Please suggest.

I want to achieve result which will come from this sql query-

SELECT * FROM outlet join product on product.outlet_id= outlet.id where product.is_active=1 ;
meGaMind
  • 95
  • 1
  • 7
  • Care to post the real JPQL? Because what you have posted is invalid `WHERE AND pro pro .isActive=1`. When you've done that, post what SQL is EXECUTED in the database by your chosen JPA provider (in the JPA providers log) –  Sep 05 '18 at 13:37
  • @BillyFrost updated the JPQL please check. – meGaMind Sep 05 '18 at 13:39
  • The query will return all Outlet where it has at least one Product that is activity value of 1. That's what you are asking for –  Sep 05 '18 at 13:45
  • @BillyFrost No thats not what i want ,Please check the updated ques for outcome..I want only that outlets which have products active corresponding to them.. – meGaMind Sep 05 '18 at 13:49
  • 1
    As i already said, look at the generated SQL that is executed for any JPQL query if you dont understand your result. Your proposed SQL can still return `Outlet` that has a `Product` that is inactive (if it also has one that is active). –  Sep 05 '18 at 13:57
  • @BillyFrost I looked at the SQL generated and understood but i have one query. I want Outlet where it has at least one Product but i dont want Products which is isactive false. Right now I am getting all Products in the List – meGaMind Sep 05 '18 at 14:26
  • If an `Outlet` has 2 `Product`s and 1 is active and 1 is inactive then that `Outlet` is returned. And the `Product`s for that `Outlet` will always be WHAT PRODUCTS IT HAS. e.g Outlet1 has Product1 (active) and Product2 (inactive), you will NEVER EVER get Outlet1 returned with just Product1. Impossible –  Sep 05 '18 at 14:29
  • Possible duplicate of [Construct JPA query for a OneToMany relation](https://stackoverflow.com/questions/6564550/construct-jpa-query-for-a-onetomany-relation) – K.Nicholas Sep 05 '18 at 15:45

1 Answers1

0

I want Outlet where it has at least one Product but i dont want Products which is isactive false. Right now I am getting all Products in the List

It's not recommended, but will work using JOIN FETCH:

Select op From Outlet op join fetch op.products pro where pro.isActive = 1 order by op.id
Dherik
  • 17,757
  • 11
  • 115
  • 164