queries/translate.sql.tpl (9785e31) - fs-words.git

translate.sql.tpl

select
	`x`.`language_iso_639_2` as `from_language`,
	`x`.`lemma` as `from_lemma`,
	`x`.`pronunciation` as `from_pronunciation`,
	`x`.`strength` as `from_strength`,
	`y`.`language_iso_639_2` as `to_language`,
	`y`.`lemma` as `to_lemma`,
	`y`.`pronunciation` as `to_pronunciation`,
	`y`.`strength` as `to_strength`
from
	(
			(
				select
					`x3`.`word_id` as `word_id`,
					`x3`.`language_iso_639_1` as `language_iso_639_1`,
					`x3`.`language_iso_639_2` as `language_iso_639_2`,
					`x3`.`lemma` as `lemma`,
					`x3`.`pronunciation` as `pronunciation`,
					`x4`.`concept_id` as `concept_id`,
					`x4`.`strength` as `strength`
				from
					(
							(
								select
									`x2`.`id` as `word_id`,
									`x1`.`iso_639_1` as `language_iso_639_1`,
									`x1`.`iso_639_2` as `language_iso_639_2`,
									`x2`.`lemma` as `lemma`,
									`x2`.`pronunciation` as `pronunciation`
								from
									(
											(
												select
													`id`,
													`iso_639_1`,
													`iso_639_2`
												from
													`languages`
											) as `x1`
										inner join
											(
												select
													`id`,
													`language_id`,
													`lemma`,
													`pronunciation`
												from
													`words`
											) as `x2`
										on
											(`x1`.`id` = `x2`.`language_id`)
									)
							) as `x3`
						inner join
							`correlations` as `x4`
						on
							(`x3`.`word_id` = `x4`.`word_id`)
					)
			) as x
		inner join
			(
				select
					`y3`.`word_id` as `word_id`,
					`y3`.`language_iso_639_1` as `language_iso_639_1`,
					`y3`.`language_iso_639_2` as `language_iso_639_2`,
					`y3`.`lemma` as `lemma`,
					`y3`.`pronunciation` as `pronunciation`,
					`y4`.`concept_id` as `concept_id`,
					`y4`.`strength` as `strength`
				from
					(
							(
								select
									`y2`.`id` as `word_id`,
									`y1`.`iso_639_1` as `language_iso_639_1`,
									`y1`.`iso_639_2` as `language_iso_639_2`,
									`y2`.`lemma` as `lemma`,
									`y2`.`pronunciation` as `pronunciation`
								from
									(
											(
												select
													`id`,
													`iso_639_1`,
													`iso_639_2`
												from
													`languages`
											) as `y1`
										inner join
											(
												select
													`id`,
													`language_id`,
													`lemma`,
													`pronunciation`
												from
													`words`
											) as `y2`
										on
											(`y1`.`id` = `y2`.`language_id`)
									)
							) as `y3`
						inner join
							`correlations` as `y4`
						on
							(`y3`.`word_id` = `y4`.`word_id`)
					)
			) as `y`
		on
			(`x`.`concept_id` = `y`.`concept_id`)
	)
where
	(
		(
			(`x`.`language_iso_639_1` = :from_language)
			or
			(`x`.`language_iso_639_2` = :from_language)
		)
		and
		(`from_lemma` like ('%' || :from_lemma || '%'))
		and
		(
			(:to_language is null)
			or
			(`y`.`language_iso_639_1` = :to_language)
			or
			(`y`.`language_iso_639_2` = :to_language)
		)
		and
		not (
			(`x`.`language_iso_639_2` = `y`.`language_iso_639_2`)
			and
			(`x`.`lemma` = `y`.`lemma`)
		)
	)
;

fs-words.git

query scripts