Réécrivez le titre comme suit : Comment convertir AVEC `cte` AS (VALUES ROW(1), ROW(2), ROW(n)) dans la syntaxe MySQL en jOOQ DSL
J'ai une requête SQL pour MySQL :
WITH `cte` AS (
( SELECT 1431655747 AS `n` FROM DUAL ) UNION ALL
( SELECT 1431655733 AS `n` FROM DUAL ) UNION ALL
( SELECT 715827794 AS `n` FROM DUAL ) UNION ALL
( SELECT 715827865 AS `n` FROM DUAL ) UNION ALL
( SELECT 1073741809 AS `n` FROM DUAL ) UNION ALL
( SELECT 1073741759 AS `n` FROM DUAL ) UNION ALL
( SELECT 715827800 AS `n` FROM DUAL ) UNION ALL
( SELECT 1431655693 AS `n` FROM DUAL ) UNION ALL
( SELECT 715827789 AS `n` FROM DUAL ) UNION ALL
( SELECT 715827838 AS `n` FROM DUAL ) UNION ALL
( SELECT 715827823 AS `n` FROM DUAL ) UNION ALL
( SELECT 858993391 AS `n` FROM DUAL )) SELECT
`cte`.`n`,
`maxmind_country`.`country`.`name_en`
FROM
`cte`
JOIN `maxmind_country`.`ipv4` ON `cte`.`n` BETWEEN `maxmind_country`.`ipv4`.`start_int`
AND `maxmind_country`.`ipv4`.`last_int`
JOIN `maxmind_country`.`country` ON `maxmind_country`.`country`.`geoname_id` = `maxmind_country`.`ipv4`.`v_geoname_id`;
Après la conversion vers jOOQ DSL :
String ipAlias = "n";
SelectSelectStep<Record1<UInteger>> unionIps = ips
.stream()
.distinct()
.map(value -> DSL.select(DSL.val(value).as(ipAlias)))
.reduce((r1, r2) -> (SelectSelectStep<Record1<UInteger>>) r1.unionAll(r2))
.orElse(null);
if(unionIps == null)
return null;
CommonTableExpression<Record1<UInteger>> cte = DSL.name("cte").as(unionIps);
Field<UInteger> ipField = cte.field(ipAlias, UInteger.class);
return dslContext
.with(cte)
.select(ipField, COUNTRY.NAME_EN)
.from(cte)
.join(IPV4).on(ipField.between(IPV4.START_INT, IPV4.LAST_INT))
.join(COUNTRY).on(COUNTRY.GEONAME_ID.eq(IPV4.V_GEONAME_ID))
.fetchMap(ipField, Country.class);
Le résultat est :
+------------+----------------+ | n | name_en | +------------+----------------+ | 1431655747 | Spain | | 1431655733 | Spain | | 715827794 | China | | 715827865 | China | | 1073741809 | United States | | 1073741759 | United States | | 715827800 | China | | 1431655693 | Spain | | 715827789 | China | | 715827838 | China | | 715827823 | China | | 858993391 | United Kingdom | +------------+----------------+ 12 rows in set (0.16 sec)
Mais l'explication montre une UNION redondante :
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL 12 100,00 1 PRIMARY ipv4 ALL ipv4_start_int_UNIQUE,ipv4_last_int_UNIQUE,ipv4_v_geoname_id_fk_idx 325934 11,11 Range checked for each record (index map: 0x7) 1 PRIMARY country eq_ref PRIMARY PRIMARY 4 maxmind_country.ipv4.v_geoname_id 1 100,00 2 DERIVED No tables used 3 UNION No tables used 4 UNION No tables used 5 UNION No tables used 6 UNION No tables used 7 UNION No tables used 8 UNION No tables used 9 UNION No tables used 10 UNION No tables used 11 UNION No tables used 12 UNION No tables used 13 UNION No tables used
J'ai réécrit un SQL en utilisant une autre syntaxe MySQL - VALUES ROW(1), ROW(2), ROW(n) :
WITH `cte` AS (
VALUES ROW(1431655747),
ROW(1431655733),
ROW(715827794),
ROW(715827865),
ROW(1073741809),
ROW(1073741759),
ROW(715827800),
ROW(1431655693),
ROW(715827789),
ROW(715827838),
ROW(715827823),
ROW(858993391)) SELECT
`cte`.`column_0`,
`maxmind_country`.`country`.`name_en`
FROM
`cte`
JOIN `maxmind_country`.`ipv4` ON `cte`.`column_0` BETWEEN `maxmind_country`.`ipv4`.`start_int`
AND `maxmind_country`.`ipv4`.`last_int`
JOIN `maxmind_country`.`country` ON `maxmind_country`.`country`.`geoname_id` = `maxmind_country`.`ipv4`.`v_geoname_id`;
Même résultat :
+------------+----------------+ | column_0 | name_en | +------------+----------------+ | 1431655747 | Spain | | 1431655733 | Spain | | 715827794 | China | | 715827865 | China | | 1073741809 | United States | | 1073741759 | United States | | 715827800 | China | | 1431655693 | Spain | | 715827789 | China | | 715827838 | China | | 715827823 | China | | 858993391 | United Kingdom | +------------+----------------+ 12 rows in set (0.16 sec)
Mais le planificateur de requêtes n'utilise pas UNION :
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL 12 100,00 1 PRIMARY ipv4 ALL ipv4_start_int_UNIQUE,ipv4_last_int_UNIQUE,ipv4_v_geoname_id_fk_idx 325934 11,11 Range checked for each record (index map: 0x7) 1 PRIMARY country eq_ref PRIMARY PRIMARY 4 maxmind_country.ipv4.v_geoname_id 1 100,00 2 DERIVED No tables used
Question : Comment convertir le script final en jOOQ DSL ? Je ne trouve aucun moyen de le faire
WITH `cte` AS (VALUES ROW(1), ROW(2), ROW(n)) ... ?
