Is it possible to merge two simple DB queries to get a single reply containing a hash
in my blog-like app, i have two separate queries;
Select id, title, content from posts where id ='$a_post_id'
and
Select tagname, tagscore from tags where postid ='$a_post_id'
Since my db is hosted on a different server from my app, my major performance issue is with the server request roudabout for both the queries.
I was wondering if I can merge the two queries into a single query so that the expected output is
postdetails for '$a_post_id'= id, title, content, tags (hash of tagname, tagscore)
Something similar to a join but where the rows are asymmetrical. Or join returning hashes...
Also, this sounds like a great fix to a simple issue, but am I missing something here? Assuming that this solution is possible, are there any obvioud cons associated with it?
postgresql query-optimization key-value
edited Nov 19 '18 at 14:28
a_horse_with_no_name
296k46451546
asked Nov 19 '18 at 12:46
Rishav SharanRishav Sharan
83152237
add a comment |
in my blog-like app, i have two separate queries;
Select id, title, content from posts where id ='$a_post_id'
and
Select tagname, tagscore from tags where postid ='$a_post_id'
Since my db is hosted on a different server from my app, my major performance issue is with the server request roudabout for both the queries.
I was wondering if I can merge the two queries into a single query so that the expected output is
postdetails for '$a_post_id'= id, title, content, tags (hash of tagname, tagscore)
Something similar to a join but where the rows are asymmetrical. Or join returning hashes...
Also, this sounds like a great fix to a simple issue, but am I missing something here? Assuming that this solution is possible, are there any obvioud cons associated with it?
postgresql query-optimization key-value
edited Nov 19 '18 at 14:28
a_horse_with_no_name
296k46451546
asked Nov 19 '18 at 12:46
Rishav SharanRishav Sharan
83152237
I meant a key value pair/object I thought that was the terminology used in PG (HStore).
– Rishav Sharan
Nov 19 '18 at 14:27
You could use json functions to do that.
– a_horse_with_no_name
Nov 19 '18 at 14:29
add a comment |
in my blog-like app, i have two separate queries;
Select id, title, content from posts where id ='$a_post_id'
and
Select tagname, tagscore from tags where postid ='$a_post_id'
Since my db is hosted on a different server from my app, my major performance issue is with the server request roudabout for both the queries.
I was wondering if I can merge the two queries into a single query so that the expected output is
postdetails for '$a_post_id'= id, title, content, tags (hash of tagname, tagscore)
Something similar to a join but where the rows are asymmetrical. Or join returning hashes...
Also, this sounds like a great fix to a simple issue, but am I missing something here? Assuming that this solution is possible, are there any obvioud cons associated with it?
postgresql query-optimization key-value
edited Nov 19 '18 at 14:28
a_horse_with_no_name
296k46451546
asked Nov 19 '18 at 12:46
Rishav SharanRishav Sharan
83152237
in my blog-like app, i have two separate queries;
Select id, title, content from posts where id ='$a_post_id'
and
Select tagname, tagscore from tags where postid ='$a_post_id'
Since my db is hosted on a different server from my app, my major performance issue is with the server request roudabout for both the queries.
I was wondering if I can merge the two queries into a single query so that the expected output is
postdetails for '$a_post_id'= id, title, content, tags (hash of tagname, tagscore)
Something similar to a join but where the rows are asymmetrical. Or join returning hashes...
Also, this sounds like a great fix to a simple issue, but am I missing something here? Assuming that this solution is possible, are there any obvioud cons associated with it?
postgresql query-optimization key-value
postgresql query-optimization key-value
edited Nov 19 '18 at 14:28
a_horse_with_no_name
296k46451546
asked Nov 19 '18 at 12:46
Rishav SharanRishav Sharan
83152237
edited Nov 19 '18 at 14:28
a_horse_with_no_name
296k46451546
asked Nov 19 '18 at 12:46
Rishav SharanRishav Sharan
83152237
edited Nov 19 '18 at 14:28
a_horse_with_no_name
296k46451546
edited Nov 19 '18 at 14:28
a_horse_with_no_name
296k46451546
edited Nov 19 '18 at 14:28
a_horse_with_no_name
296k46451546
296k46451546
asked Nov 19 '18 at 12:46
Rishav SharanRishav Sharan
83152237
asked Nov 19 '18 at 12:46
Rishav SharanRishav Sharan
83152237
asked Nov 19 '18 at 12:46
Rishav SharanRishav Sharan
83152237
83152237
I meant a key value pair/object I thought that was the terminology used in PG (HStore).
– Rishav Sharan
Nov 19 '18 at 14:27
You could use json functions to do that.
– a_horse_with_no_name
Nov 19 '18 at 14:29
add a comment |
I meant a key value pair/object I thought that was the terminology used in PG (HStore).
– Rishav Sharan
Nov 19 '18 at 14:27
You could use json functions to do that.
– a_horse_with_no_name
Nov 19 '18 at 14:29
I meant a key value pair/object I thought that was the terminology used in PG (HStore).
– Rishav Sharan
Nov 19 '18 at 14:27
I meant a key value pair/object I thought that was the terminology used in PG (HStore).
– Rishav Sharan
Nov 19 '18 at 14:27
You could use json functions to do that.
– a_horse_with_no_name
Nov 19 '18 at 14:29
You could use json functions to do that.
– a_horse_with_no_name
Nov 19 '18 at 14:29
add a comment |
1 Answer
1
active
oldest
votes
If a JSON key/value pair is OK, you can something like this:
select p.id, p.title, p.content,
jsonb_object_agg(t.tagname, t.tagscore) as tags
from posts p
left join tags t on t.postid = p.id
where p.id ='$a_post_id'
group by p.id;
The above assumes that posts.id is defined as the primary key
Online example: https://rextester.com/RDQX27041
answered Nov 19 '18 at 14:39
a_horse_with_no_namea_horse_with_no_name
296k46451546
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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%2f53374960%2fis-it-possible-to-merge-two-simple-db-queries-to-get-a-single-reply-containing-a%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If a JSON key/value pair is OK, you can something like this:
select p.id, p.title, p.content,
jsonb_object_agg(t.tagname, t.tagscore) as tags
from posts p
left join tags t on t.postid = p.id
where p.id ='$a_post_id'
group by p.id;
The above assumes that posts.id is defined as the primary key
Online example: https://rextester.com/RDQX27041
answered Nov 19 '18 at 14:39
a_horse_with_no_namea_horse_with_no_name
296k46451546
add a comment |
If a JSON key/value pair is OK, you can something like this:
select p.id, p.title, p.content,
jsonb_object_agg(t.tagname, t.tagscore) as tags
from posts p
left join tags t on t.postid = p.id
where p.id ='$a_post_id'
group by p.id;
The above assumes that posts.id is defined as the primary key
Online example: https://rextester.com/RDQX27041
answered Nov 19 '18 at 14:39
a_horse_with_no_namea_horse_with_no_name
296k46451546
add a comment |
If a JSON key/value pair is OK, you can something like this:
select p.id, p.title, p.content,
jsonb_object_agg(t.tagname, t.tagscore) as tags
from posts p
left join tags t on t.postid = p.id
where p.id ='$a_post_id'
group by p.id;
The above assumes that posts.id is defined as the primary key
Online example: https://rextester.com/RDQX27041
answered Nov 19 '18 at 14:39
a_horse_with_no_namea_horse_with_no_name
296k46451546
If a JSON key/value pair is OK, you can something like this:
select p.id, p.title, p.content,
jsonb_object_agg(t.tagname, t.tagscore) as tags
from posts p
left join tags t on t.postid = p.id
where p.id ='$a_post_id'
group by p.id;
The above assumes that posts.id is defined as the primary key
Online example: https://rextester.com/RDQX27041
answered Nov 19 '18 at 14:39
a_horse_with_no_namea_horse_with_no_name
296k46451546
answered Nov 19 '18 at 14:39
a_horse_with_no_namea_horse_with_no_name
296k46451546
answered Nov 19 '18 at 14:39
a_horse_with_no_namea_horse_with_no_name
296k46451546
answered Nov 19 '18 at 14:39
a_horse_with_no_namea_horse_with_no_name
296k46451546
296k46451546
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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%2f53374960%2fis-it-possible-to-merge-two-simple-db-queries-to-get-a-single-reply-containing-a%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
I meant a key value pair/object I thought that was the terminology used in PG (HStore).
– Rishav Sharan
Nov 19 '18 at 14:27
You could use json functions to do that.
– a_horse_with_no_name
Nov 19 '18 at 14:29