Being DRY in a Sqlite query with CASE statement
up vote
0
down vote
favorite
In the following (working) query:
SELECT q.id, q.section_id, q.type, q.required, q.condition,
CASE WHEN (t1.text_1 IS NULL) THEN
CASE WHEN ((SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1) IS NULL) THEN
(SELECT t3.text_1 FROM translations t3 WHERE t3.item_id = q.id AND t3.item_model = 'questions' LIMIT 1)
ELSE
(SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1)
END
ELSE
t1.text_1
END
AS translation
FROM questions q
LEFT JOIN translations t1 ON t1.item_id = q.id
AND t1.item_model = 'questions'
AND t1.language = 'fr'
ORDER BY q.position
You can see that the part (SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1)
is repeated twice (the first to check if it's null, and the second to get the value).
Could the repeated same query a performance issue (I guess so)?
Is there a better way to rewrite this query, being DRY?
sqlite3 case dry
add a comment |
up vote
0
down vote
favorite
In the following (working) query:
SELECT q.id, q.section_id, q.type, q.required, q.condition,
CASE WHEN (t1.text_1 IS NULL) THEN
CASE WHEN ((SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1) IS NULL) THEN
(SELECT t3.text_1 FROM translations t3 WHERE t3.item_id = q.id AND t3.item_model = 'questions' LIMIT 1)
ELSE
(SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1)
END
ELSE
t1.text_1
END
AS translation
FROM questions q
LEFT JOIN translations t1 ON t1.item_id = q.id
AND t1.item_model = 'questions'
AND t1.language = 'fr'
ORDER BY q.position
You can see that the part (SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1)
is repeated twice (the first to check if it's null, and the second to get the value).
Could the repeated same query a performance issue (I guess so)?
Is there a better way to rewrite this query, being DRY?
sqlite3 case dry
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
In the following (working) query:
SELECT q.id, q.section_id, q.type, q.required, q.condition,
CASE WHEN (t1.text_1 IS NULL) THEN
CASE WHEN ((SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1) IS NULL) THEN
(SELECT t3.text_1 FROM translations t3 WHERE t3.item_id = q.id AND t3.item_model = 'questions' LIMIT 1)
ELSE
(SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1)
END
ELSE
t1.text_1
END
AS translation
FROM questions q
LEFT JOIN translations t1 ON t1.item_id = q.id
AND t1.item_model = 'questions'
AND t1.language = 'fr'
ORDER BY q.position
You can see that the part (SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1)
is repeated twice (the first to check if it's null, and the second to get the value).
Could the repeated same query a performance issue (I guess so)?
Is there a better way to rewrite this query, being DRY?
sqlite3 case dry
In the following (working) query:
SELECT q.id, q.section_id, q.type, q.required, q.condition,
CASE WHEN (t1.text_1 IS NULL) THEN
CASE WHEN ((SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1) IS NULL) THEN
(SELECT t3.text_1 FROM translations t3 WHERE t3.item_id = q.id AND t3.item_model = 'questions' LIMIT 1)
ELSE
(SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1)
END
ELSE
t1.text_1
END
AS translation
FROM questions q
LEFT JOIN translations t1 ON t1.item_id = q.id
AND t1.item_model = 'questions'
AND t1.language = 'fr'
ORDER BY q.position
You can see that the part (SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1)
is repeated twice (the first to check if it's null, and the second to get the value).
Could the repeated same query a performance issue (I guess so)?
Is there a better way to rewrite this query, being DRY?
sqlite3 case dry
sqlite3 case dry
asked Nov 7 at 12:23
Ivan
94352640
94352640
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
You can replace the inner CASE
statement with coalesce()
function:
coalesce(SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1,
SELECT t3.text_1 FROM translations t3 WHERE t3.item_id = q.id AND t3.item_model = 'questions' LIMIT 1)
From the documenation:
coalesce(X,Y,...)
The coalesce() function returns a copy of its first non-NULL argument,
or NULL if all arguments are NULL. Coalesce() must have at least 2
arguments.
Similar is the ifnull() function.
This is GREAT! You just saved my day also on another query with 3 coalesce argumenst! :-)
– Ivan
Nov 7 at 13:50
This is good news!
– forpas
Nov 7 at 13:50
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
You can replace the inner CASE
statement with coalesce()
function:
coalesce(SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1,
SELECT t3.text_1 FROM translations t3 WHERE t3.item_id = q.id AND t3.item_model = 'questions' LIMIT 1)
From the documenation:
coalesce(X,Y,...)
The coalesce() function returns a copy of its first non-NULL argument,
or NULL if all arguments are NULL. Coalesce() must have at least 2
arguments.
Similar is the ifnull() function.
This is GREAT! You just saved my day also on another query with 3 coalesce argumenst! :-)
– Ivan
Nov 7 at 13:50
This is good news!
– forpas
Nov 7 at 13:50
add a comment |
up vote
1
down vote
accepted
You can replace the inner CASE
statement with coalesce()
function:
coalesce(SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1,
SELECT t3.text_1 FROM translations t3 WHERE t3.item_id = q.id AND t3.item_model = 'questions' LIMIT 1)
From the documenation:
coalesce(X,Y,...)
The coalesce() function returns a copy of its first non-NULL argument,
or NULL if all arguments are NULL. Coalesce() must have at least 2
arguments.
Similar is the ifnull() function.
This is GREAT! You just saved my day also on another query with 3 coalesce argumenst! :-)
– Ivan
Nov 7 at 13:50
This is good news!
– forpas
Nov 7 at 13:50
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
You can replace the inner CASE
statement with coalesce()
function:
coalesce(SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1,
SELECT t3.text_1 FROM translations t3 WHERE t3.item_id = q.id AND t3.item_model = 'questions' LIMIT 1)
From the documenation:
coalesce(X,Y,...)
The coalesce() function returns a copy of its first non-NULL argument,
or NULL if all arguments are NULL. Coalesce() must have at least 2
arguments.
Similar is the ifnull() function.
You can replace the inner CASE
statement with coalesce()
function:
coalesce(SELECT t2.text_1 FROM translations t2 WHERE t2.item_id = q.id AND t2.item_model = 'questions' AND t2.language = 'en' LIMIT 1,
SELECT t3.text_1 FROM translations t3 WHERE t3.item_id = q.id AND t3.item_model = 'questions' LIMIT 1)
From the documenation:
coalesce(X,Y,...)
The coalesce() function returns a copy of its first non-NULL argument,
or NULL if all arguments are NULL. Coalesce() must have at least 2
arguments.
Similar is the ifnull() function.
answered Nov 7 at 12:39
forpas
3,7461214
3,7461214
This is GREAT! You just saved my day also on another query with 3 coalesce argumenst! :-)
– Ivan
Nov 7 at 13:50
This is good news!
– forpas
Nov 7 at 13:50
add a comment |
This is GREAT! You just saved my day also on another query with 3 coalesce argumenst! :-)
– Ivan
Nov 7 at 13:50
This is good news!
– forpas
Nov 7 at 13:50
This is GREAT! You just saved my day also on another query with 3 coalesce argumenst! :-)
– Ivan
Nov 7 at 13:50
This is GREAT! You just saved my day also on another query with 3 coalesce argumenst! :-)
– Ivan
Nov 7 at 13:50
This is good news!
– forpas
Nov 7 at 13:50
This is good news!
– forpas
Nov 7 at 13:50
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53189416%2fbeing-dry-in-a-sqlite-query-with-case-statement%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown