Spring Data Jpa JPQL using inner join to select minimum priced holiday

Question

I am trying to find the minimum priced holiday for each month from each departure airport to a target destination airport from a table with over 1 million records. I have successfully created the required MySQL query :-

SELECT * FROM results r INNER JOIN (SELECT id,MIN(price) FROM results GROUP BY BulkLoaderRef) r2 ON r.id=r2.id ORDER BY r.departureDate, r.depAirportCode, r.resortID LIMIT 10

But I cannot find a valid JPQL syntax for this query?

Help please?

My table format is as follows :-

CREATE TABLE `results` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `HotelReference` varchar(128) NOT NULL DEFAULT '',
  `HotelName` varchar(64) NOT NULL DEFAULT '',
  `Resort` varchar(128) NOT NULL DEFAULT '',
  `Description` longtext NOT NULL,
  `Photo` varchar(255) NOT NULL DEFAULT '',
  `OfferProcessType` varchar(1) NOT NULL DEFAULT 'A',
  `TradingName` varchar(32) NOT NULL DEFAULT 'Heffernans Travel',
  `OfferLabelDesc` varchar(16) NOT NULL DEFAULT 'EasyHolidays',
  `DestinationName` varchar(128) NOT NULL DEFAULT '',
  `DepAirportCode` varchar(3) NOT NULL DEFAULT '',
  `TelephoneNumber` varchar(16) NOT NULL DEFAULT '021 230 0700',
  `ReturnAirportCode` varchar(3) NOT NULL DEFAULT '',
  `ArrivalAirportCode` varchar(3) NOT NULL DEFAULT '',
  `ResortID` int(11) NOT NULL,
  `ABTA` varchar(16) NOT NULL DEFAULT 'ABTA',
  `ATOL` varchar(16) NOT NULL DEFAULT 'ATOL',
  `OtherBonding` varchar(64) NOT NULL DEFAULT 'Agent for ATOL protected operator',
  `BoardTypesShort` varchar(2) NOT NULL DEFAULT '',
  `AccomodationShort` varchar(3) NOT NULL DEFAULT 'Htl',
  `AllocationShort` varchar(3) DEFAULT '""',
  `OperatorShort` varchar(16) NOT NULL DEFAULT 'EasyHolidays',
  `DepartureDate` date NOT NULL,
  `DepartureMonth` varchar(2) DEFAULT NULL,
  `DepartureYear` varchar(4) DEFAULT NULL,
  `Price` int(11) NOT NULL,
  `ShareBasisAdult` int(11) DEFAULT '2',
  `BulkLoaderRef` varchar(64) DEFAULT NULL,
  `Email` varchar(64) DEFAULT '',
  `PublicNotes` varchar(64) DEFAULT 'EasyHolidays',
  `CCC` varchar(32) DEFAULT ' Credit Card Charges',
  `TOD` varchar(16) DEFAULT '',
  `DCC` varchar(16) DEFAULT '',
  `AMEX` varchar(16) DEFAULT '',
  `CDW` varchar(16) DEFAULT '',
  `BookingFee` double DEFAULT NULL,
  `AccomodationName` varchar(64) NOT NULL DEFAULT '',
  `duration` int(11) NOT NULL DEFAULT '0',
  `ShareBasisChild` int(11) DEFAULT '2',
  `Rating` varchar(16) NOT NULL DEFAULT '',
  `OfferLink` varchar(16) NOT NULL DEFAULT '',
  `AccomodationRef` varchar(128) NOT NULL DEFAULT '',
  `Transfers` varchar(32) NOT NULL DEFAULT '',
  `OperatorsRating` varchar(32) NOT NULL DEFAULT '',
  `QuoteRef` varchar(64) NOT NULL DEFAULT '',
  `Url` varchar(128) NOT NULL DEFAULT '',
  `OutDepTime` varchar(16) NOT NULL DEFAULT '',
  `OutArrTime` varchar(16) NOT NULL DEFAULT '',
  `InDepTime` varchar(16) NOT NULL DEFAULT '',
  `InArrTime` varchar(16) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `HotelReference` (`HotelReference`),
  KEY `BulkLoaderRef` (`BulkLoaderRef`),
  KEY `Price` (`Price`)
) ENGINE=InnoDB AUTO_INCREMENT=1091073 DEFAULT CHARSET=utf8;

Answer 1

JPQL doesn't support sub-queries for JOIN statements. With that said, I would suggest that you move the subquery of your JOIN into a view. Then it becomes a simple JPQL statement.

Move subquery into a view:

CREATE VIEW min_price_view AS 
    SELECT id,MIN(price) FROM results GROUP BY BulkLoaderRef;

Then replace in query:

SELECT 
    * 
FROM 
    results r 
INNER JOIN 
    min_price_view r2 ON r.id=r2.id 
ORDER BY 
    r.departureDate, r.depAirportCode, r.resortID 
LIMIT 10

At this point, it becomes very simple to convert it to JPQL.