How to make a Spring JPARepository query to find most linked entities in other tables-CodePudding

I'm trying to find one query-solution to find the items in a table that are most present in two other tables. Simplified my structure looks like this:

@Entity
@Table(name = "item")
public class Item extends AbstractEntity {
   // ID is added by AbstractEntity
}

@Entity
@Table(name = "itemCounter1")
public class ItemCounter1 extends AbstractEntity {
   private UUID itemId;
   private ZonedDateTime datetime;
}

@Entity
@Table(name = "itemCounter2")
public class ItemCounter2 extends AbstractEntity {
   private UUID itemId;
   private ZonedDateTime datetime;
}

I believe my query should look something like the following, but it's not correct yet and I'm a bit puzzled what could be the best approach... Not sure even if I should do this within a query, but it would definitely be nice to be able to hand this over to the power of the database, instead of needing to code several separate DB calls and combine the results.

public interface ItemRepository extends JpaRepository<Item, UUID> {
    @Query("""
        SELECT i
            FROM Item i 
                LEFT OUTER JOIN ItemCounter1 counter1 ON i.id = counter1.itemId
                LEFT OUTER JOIN ItemCounter2 counter2 ON i.id = counter2.itemId
            WHERE counter1.datetime >= :fromDate
                AND counter2.datetime >= :fromDate
            GROUP BY counter1.itemId, counter2.itemId
            ORDER BY (COUNT(counter1.itemId)   COUNT(counter2.itemId)) DESC
        """)
    Page<Item> findMostCounted(ZonedDateTime fromDate, PageRequest of);
}

CodePudding user response：

    @Query("""
        SELECT  i
            FROM Item i
                LEFT OUTER JOIN ItemCounter1 counter1 ON i.id = counter1.itemId
                LEFT OUTER JOIN ItemCounter2 counter2 ON i.id = counter2.itemId
            WHERE counter1.datetime >= :fromDate
                AND counter2.datetime >= :fromDate
            GROUP BY counter1.itemId, counter2.itemId, i.id
            ORDER BY (COUNT(counter1.itemId)   COUNT(counter2.itemId)) DESC
        """)
    Page<Item> findMostCounted(ZonedDateTime fromDate, PageRequest of);

your query would need the ID column from item in the group by clause as it is what you are selecting by. I hope I could help :)