Sub query support

[abibell]

What needs to be done to support sub-queries like below so we don't get duplicate person records? JPQL seems to support this query format. Doing a JINQ's join and distinct seems to throw up errors when combined with paging and sorting.

SELECT * FROM Person
WHERE ID IN (SELECT ToId FROM Friends WHERE FromId = :personId )

[oberien]

Did you try using WhereWithSource like this (untested)?

streams.streamAll(Person.class)
    .where((p, source) -> JPQL.isIn(p, source.stream(Friends.class)
        .where(f -> f.getFromId() == personId)
        .select(f -> f.getTo()))
    )

[abibell]

Works

Interesting progress. I tried it and it worked for that example. Thanks.

stream
.where((p, source) -> JPQL.isIn(p.getId(),
		source
		.stream(Friends.class)
		.where(f -> f.getFromId() == personId)
		.select(l -> l.getToId())
				))
.toList();

What doesn't work

Our real query was a bit complex than what was posted involving a join in sub-query. I get this
org.jinq.rebased.org.objectweb.asm.tree.analysis.AnalyzerException: Unknown method org/jinq/orm/stream/JinqStream:join(Lorg/jinq/orm/stream/JinqStream$JoinWithSource;)Lorg/jinq/orm/stream/JinqStream; encountered

When I write

// Method 1: just join
stream
.where((p, s) -> JPQL.isIn(p.getId(),
	//Get Persons that are friends of friends
	s
	.stream(Friends.class)
	.where(f -> f.getFromId() == personId)
	.join((j, source) -> source.stream(Friends.class))
	.where(j -> j.getOne().getToId().equals(j.getTwo().getFromId()))
	.select(l -> l.getTwo().getToId())
			))

//Method 2: Use sub query with a sub query
stream
.where((p, s) -> JPQL.isIn(p.getId(),
	//Get Persons that are friends of friends
	s
	.stream(Friends.class)
	.where(f -> f.getFromId() == personId)
	.where((pp, ss) -> JPQL.isIn(pp.getFromId(), 
			ss.stream(Friends.class).where(ll -> branchIds.contains(ll.getFromId())).select(ll -> ll.getToId())
			) )
	.select(l -> l.getTwo().getToId())
			))

Question

Your insights would be greatly appreciated

[my2iu]

Oh, right. Sorry. I didn't get around to implementing join() in a subquery yet. Let me spend a while looking at your query to see what's possible.

[my2iu]

Can you just add an association between friend objects? Apparently, I did implement support for join() and streamAll() in subqueries, but I didn't implement support for join() with a source. If you have an association, then you don't need to use the variant of join() that uses a source.

stream.where( (p, s) -> 
   JPQL.isIn(p.getId(), s.stream(Friends.class)
      .where( f -> f.getFromId() == personId )
      .selectAll( f -> JinqStream.from( f.getFriends() ) );

You mentioned that you encountered errors with paging when using distinct and join. What were those errors? Were they problems with EclipseLink/Hibernate or with Jinq?

[my2iu]

Oh, wait. I guess the query would be something more like

stream.where( (p, s) -> 
   JPQL.isIn(p.getId(), JinqStream.from(p->getFriends())
      .streamAll( JinqStream.from(p->getFriends()) )
      .stream(p.getId()) ));

[abibell]

Hi @my2iu ,

Sorry for the delay. Tried theses approaches and here are the results.

SelectAll() approach works if there is no .contains() in the where

Query 1

list = streams.streamAll(em, Person.class)
        .where((p, source) -> JPQL.isIn(p.getId(),
                source
                .stream(Friends.class)
                .where(l -> l.getTypeId().equals(containsId) && l.getTo().getTypeId().equals(groupTypeId))
                .selectAll(l -> JinqStream.from(l.getTo().getFriends()))
                .where(l -> allowedRelationships.contains(l.getTypeId()))//Causes error
                .select(l -> l.getFromId())
                        ))
.toList();

I get antlr.NoViableAltException: unexpected token: , at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:772). Using JPQL.isInList() instead of contains does throw the same error.

Query 2

list = streams.streamAll(em, Person.class)
        .where((p, source) -> JPQL.isIn(p.getId(),
                source
                .stream(Friends.class)
                .where(l -> l.getTypeId().equals(containsId) && l.getTo().getTypeId().equals(groupTypeId))
                .selectAll(l -> JinqStream.from(l.getTo().getFriends()).where(ll -> allowedRelationships.contains(ll.getTypeId())))// throws error here
                .select(l -> l.getFromId())
                        ))
.toList();

The error is
org.jinq.jpa.transform.QueryTransformException: Existing query cannot be transformed further at org.jinq.jpa.transform.JoinTransform.apply(JoinTransform.java:138)

streamAll() approach

I wasn't able to get streamAll() on JinqStream.from(p.getFriends())

Observation

It appears that contains() is not supported by subqueries.

[my2iu]

I don't really have the time to dig into these things and debug this. I'll try to find the time over my Easter holidays to add support for the join() with a source in subqueries to Jinq, but that might be as far as I can go. Hibernate doesn't handle subqueries really well, so if you try to nest things too deep, it will start to fall apart, and I can't really fix that from Jinq's side.

What are the errors that you're encountering when using join and distinct when combined with paging and sorting? That really seems like something that might be easier to fix or workaround than to try to get complicated subquery expressions working with Jinq/Hibernate.

[my2iu]

Support for "with source" variants of join(), select(), selectAll(), and where() is done. I'll try to get around to making a new release with these changes on Monday or so.

[my2iu]

Jinq 1.8.18 with join() with source in subqueries has now been released.

[abibell]

I tried 1.8.18, It sounded promising initially except the JPQL.isIn() part.

I pulled the Github project and added sample test cases to reproduce the issues for you in 1.8.18. If you don't want to reopen this issue, I can create new Issues. But here is the reproducible test case for Jinq-JPA project -> JinqJPAAggregateTest.java file in Jinq project or see abibell@2538a51. Ignore the expected queries. The Library should not throw exception

@Test
   public void testJoinAndDistinct()
   {
      // Search Customers who purchased Widgets & Talents
	  List<String> names = new ArrayList<String>();
		  names.add("Widgets");
		  names.add("Talent");
      // You may have Order Promotion codes or something in real life
	  List<Integer> quantities = new ArrayList<Integer>();
	  quantities.add(1);
	  quantities.add(2);
      List<Customer> customers = streams.streamAll(em, Item.class)
    		  .where(i -> names.contains(i.getName()))
    		  .selectAll(i -> JinqStream.from(i.getLineorders()))
    		  .select(lo -> lo.getSale())
    		  .select(i -> i.getCustomer())                
            .sortedBy(c -> c.getName())
            .distinct()
            .toList();//Dies here
      assertEquals("SELECT A FROM Customer A WHERE A.name IN (SELECT C.sale.customer.name FROM Item B JOIN B.lineorders C WHERE B.name IN :param0 AND C.quantity IN :param1) ORDER BY A.name ASC", query);
      assertEquals(1, customers.size());//No idea fails here
      assertEquals("Dave", customers.get(0).getName());
   }
   
   @Test
   public void testIsInSubQueryWithSelectSourceRealLifeScenario()
   {
      // Search Customers who purchased Widgets & Talents
	  List<String> names = new ArrayList<String>();
		  names.add("Widgets");
		  names.add("Talent");
      // You may have Order Promotion codes or something in real life
	  List<Integer> quantities = new ArrayList<Integer>();
	  quantities.add(1);
	  quantities.add(2);
      List<Customer> customers = streams.streamAll(em, Customer.class)
            .where((c, source) ->
                  JPQL.isIn(c.getName(), source.stream(Item.class)
                        .where(i -> names.contains(i.getName()))
                        .selectAll(i -> JinqStream.from(i.getLineorders()))
                        .where(i -> quantities.contains(i.getQuantity()))
                        .select(lo -> lo.getSale().getCustomer().getName())
                  ))
            .sortedBy(c -> c.getName())
            .toList();
      assertEquals("SELECT A FROM Customer A WHERE A.name IN (SELECT C.sale.customer.name FROM Item B JOIN B.lineorders C WHERE B.name IN :param0 AND C.quantity IN :param1) ORDER BY A.name ASC", query);
      assertEquals(1, customers.size());//No idea why it throws error here
      assertEquals("Dave", customers.get(0).getName());
   }

[my2iu]

Both queries seem to mostly work for me, using both Hibernate and EclipseLink.

For the testJoinAndDistinct() query, I had to move the distinct() to come before the sortedBy(). Then the query seems to work.

The testIsInSubQueryWithSelectSourceRealLifeScenario() query seemed to be fine once I changed the assertion to expect 2 results.

   @Test
   public void testJoinAndDistinct()
   {
      // Search Customers who purchased Widgets & Talents
          List<String> names = new ArrayList<String>();
                  names.add("Widgets");
                  names.add("Talent");
      // You may have Order Promotion codes or something in real life
          List<Integer> quantities = new ArrayList<Integer>();
          quantities.add(1);
          quantities.add(2);
      List<Customer> customers = streams.streamAll(em, Item.class)
                  .where(i -> names.contains(i.getName()))
                  .selectAll(i -> JinqStream.from(i.getLineorders()))
                  .select(lo -> lo.getSale())
                  .select(i -> i.getCustomer())                
            .distinct()
            .sortedBy(c -> c.getName())
            .toList();
      assertEquals("SELECT DISTINCT B.sale.customer FROM Item A JOIN A.lineorders B WHERE A.name IN :param0 ORDER BY B.sale.customer.name ASC", query);
      assertEquals(4, customers.size());
   }
   
   @Test
   public void testIsInSubQueryWithSelectSourceRealLifeScenario()
   {
      // Search Customers who purchased Widgets & Talents
          List<String> names = new ArrayList<String>();
                  names.add("Widgets");
                  names.add("Talent");
      // You may have Order Promotion codes or something in real life
          List<Integer> quantities = new ArrayList<Integer>();
          quantities.add(1);
          quantities.add(2);
      List<Customer> customers = streams.streamAll(em, Customer.class)
            .where((c, source) ->
                  JPQL.isIn(c.getName(), source.stream(Item.class)
                        .where(i -> names.contains(i.getName()))
                        .selectAll(i -> JinqStream.from(i.getLineorders()))
                        .where(i -> quantities.contains(i.getQuantity()))
                        .select(lo -> lo.getSale().getCustomer().getName())
                  ))
            .sortedBy(c -> c.getName())
            .toList();
      assertEquals("SELECT A FROM Customer A WHERE A.name IN (SELECT C.sale.customer.name FROM Item B JOIN B.lineorders C WHERE B.name IN :param0 AND C.quantity IN :param1) ORDER BY A.name ASC", query);
      assertEquals(2, customers.size());
   }

Thanks for the additional test cases. I still need my garbage query test case because I need one with both multiple joins and a join with source variant used inside a subquery in order to exercise the part of Jinq that needs testing. It's hard to construct a query with those properties using the small database schema I use in the test database.

[abibell]

Thanks it is correct. Swapping the order works. Yes, you need your garbage query. It is hard to construct queries with small DB schema, I faced the issue myself..