add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE wp_posts (
ID bigint unsigned auto_increment
primary key,
post_title MEDIUMTEXT
);
INSERT INTO wp_posts(ID, post_title) VALUES (897, 'Lavender');
INSERT INTO wp_posts(ID, post_title) VALUES (899, 'Prince White Download');
INSERT INTO wp_posts(ID, post_title) VALUES (902, 'test');
INSERT INTO wp_posts(ID, post_title) VALUES (1019, 'تست');
INSERT INTO wp_posts(ID, post_title) VALUES (1034, 'kk');
INSERT INTO wp_posts(ID, post_title) VALUES (1035, 'کک');
create table wp_icl_translations
(
translation_id bigint auto_increment
primary key,
element_id bigint null,
trid bigint not null,
language_code varchar(7) not null,
source_language_code varchar(7) null
)
collate = utf8mb4_unicode_520_ci;

INSERT INTO wp_icl_translations (element_id, trid, language_code, source_language_code) VALUES (897, 897, 'en', null);
INSERT INTO wp_icl_translations (element_id, trid, language_code, source_language_code) VALUES (899, 899, 'en', null);
INSERT INTO wp_icl_translations (element_id, trid, language_code, source_language_code) VALUES (902, 902, 'en', null);
INSERT INTO wp_icl_translations (element_id, trid, language_code, source_language_code) VALUES (1019, 902, 'fa', 'en');
INSERT INTO wp_icl_translations (element_id, trid, language_code, source_language_code) VALUES (1034, 2844, 'en', null);
INSERT INTO wp_icl_translations (element_id, trid, language_code, source_language_code) VALUES (1035, 2844, 'fa', 'en');
select * from wp_posts;
select * from wp_icl_translations;
ID post_title
897 Lavender
899 Prince White Download
902 test
1019 تست
1034 kk
1035 کک
translation_id element_id trid language_code source_language_code
1 897 897 en null
2 899 899 en null
3 902 902 en null
4 1019 902 fa en
5 1034 2844 en null
6 1035 2844 fa en
SELECT COALESCE(p2.id, p1.id) id, COALESCE(p2.post_title, p1.post_title) post_title
FROM wp_icl_translations t1
JOIN wp_posts p1 ON t1.element_id = p1.id
LEFT JOIN wp_icl_translations t2 ON t1.trid = t2.trid AND t2.language_code = 'fa'
LEFT JOIN wp_posts p2 ON t2.element_id = p2.id
WHERE t1.language_code = 'en'
ORDER BY id
id post_title
897 Lavender
899 Prince White Download
1019 تست
1035 کک
SELECT p0.*, t0.*
FROM wp_icl_translations t1
JOIN wp_posts p1 ON t1.element_id = p1.id
LEFT JOIN wp_icl_translations t2 ON t1.trid = t2.trid AND t2.language_code = 'fa'
LEFT JOIN wp_posts p2 ON t2.element_id = p2.id
JOIN wp_icl_translations t0 ON t0.translation_id = COALESCE(t2.translation_id, t1.translation_id)
JOIN wp_posts p0 ON p0.id = COALESCE(p2.id, p1.id)
WHERE t1.language_code = 'en'
ORDER BY id
ID post_title translation_id element_id trid language_code source_language_code
897 Lavender 1 897 897 en null
899 Prince White Download 2 899 899 en null
1019 تست 4 1019 902 fa en
1035 کک 6 1035 2844 fa en