Sql query for creating message list
1
I am creating a message service with Laravel.
I want to display recent messages from all based on time. This is what I am trying to implement 
The following are my tables
- message table
- message_user table
I tried the following code
SELECT DISTINCT user_id,sender
FROM (
SELECT DISTINCT message_user.user_id, messages.sender, messages.created_at
FROM messages
JOIN message_user ON message_user.message_id=messages.id AND message_user.user_id=225
UNION
SELECT DISTINCT message_user.user_id, messages.sender, messages.created_at FROM messages
JOIN message_user ON message_user.message_id=messages.id AND messages.sender=225 AND message_user.user_id NOT IN (
SELECT DISTINCT messages.sender
FROM messages
JOIN message_user ON message_user.message_id=messages.id AND message_user.user_id=225
)
) mytable
ORDER BY created_at
The problem is that the output is not in the expected format (that is not sorted based on created_at)
Following is the corresponding Laravel code I have tried
$id = $request->user()->id;
$user = User::find($id);
$buddylist = $user->messages()->select(DB::raw('message_user.user_id,messages.sender,messages.created_at'));
$subquery = Message::select(DB::raw(' messages.sender'))->join('message_user','messages.id','=','message_user.message_id') ->where("message_user.user_id",'=',$request->user()->id);
$mybuddy = Message::selectRaw('message_user.user_id,messages.sender,messages.created_at')
->join('message_user','messages.id','=','message_user.message_id')
->where("messages.sender",'=',$request->user()->id)
->whereNotIn('message_user.user_id', $subquery)
->union( $buddylist)
->groupBy('sender')
->groupBy('user_id')
->orderBy('created_at','desc')
->get();
php mysql laravel
edited Jan 1 at 13:19
Dharman
5,21662553
asked Jan 1 at 12:09
Reneesh KurianReneesh Kurian
408
add a comment |
1
I am creating a message service with Laravel.
I want to display recent messages from all based on time. This is what I am trying to implement
The following are my tables
- message table
- message_user table
I tried the following code
SELECT DISTINCT user_id,sender
FROM (
SELECT DISTINCT message_user.user_id, messages.sender, messages.created_at
FROM messages
JOIN message_user ON message_user.message_id=messages.id AND message_user.user_id=225
UNION
SELECT DISTINCT message_user.user_id, messages.sender, messages.created_at FROM messages
JOIN message_user ON message_user.message_id=messages.id AND messages.sender=225 AND message_user.user_id NOT IN (
SELECT DISTINCT messages.sender
FROM messages
JOIN message_user ON message_user.message_id=messages.id AND message_user.user_id=225
)
) mytable
ORDER BY created_at
The problem is that the output is not in the expected format (that is not sorted based on created_at)
Following is the corresponding Laravel code I have tried
$id = $request->user()->id;
$user = User::find($id);
$buddylist = $user->messages()->select(DB::raw('message_user.user_id,messages.sender,messages.created_at'));
$subquery = Message::select(DB::raw(' messages.sender'))->join('message_user','messages.id','=','message_user.message_id') ->where("message_user.user_id",'=',$request->user()->id);
$mybuddy = Message::selectRaw('message_user.user_id,messages.sender,messages.created_at')
->join('message_user','messages.id','=','message_user.message_id')
->where("messages.sender",'=',$request->user()->id)
->whereNotIn('message_user.user_id', $subquery)
->union( $buddylist)
->groupBy('sender')
->groupBy('user_id')
->orderBy('created_at','desc')
->get();
php mysql laravel
edited Jan 1 at 13:19
Dharman
5,21662553
asked Jan 1 at 12:09
Reneesh KurianReneesh Kurian
408
add a comment |
1
1
1
I am creating a message service with Laravel.
I want to display recent messages from all based on time. This is what I am trying to implement
The following are my tables
- message table
- message_user table
I tried the following code
SELECT DISTINCT user_id,sender
FROM (
SELECT DISTINCT message_user.user_id, messages.sender, messages.created_at
FROM messages
JOIN message_user ON message_user.message_id=messages.id AND message_user.user_id=225
UNION
SELECT DISTINCT message_user.user_id, messages.sender, messages.created_at FROM messages
JOIN message_user ON message_user.message_id=messages.id AND messages.sender=225 AND message_user.user_id NOT IN (
SELECT DISTINCT messages.sender
FROM messages
JOIN message_user ON message_user.message_id=messages.id AND message_user.user_id=225
)
) mytable
ORDER BY created_at
The problem is that the output is not in the expected format (that is not sorted based on created_at)
Following is the corresponding Laravel code I have tried
$id = $request->user()->id;
$user = User::find($id);
$buddylist = $user->messages()->select(DB::raw('message_user.user_id,messages.sender,messages.created_at'));
$subquery = Message::select(DB::raw(' messages.sender'))->join('message_user','messages.id','=','message_user.message_id') ->where("message_user.user_id",'=',$request->user()->id);
$mybuddy = Message::selectRaw('message_user.user_id,messages.sender,messages.created_at')
->join('message_user','messages.id','=','message_user.message_id')
->where("messages.sender",'=',$request->user()->id)
->whereNotIn('message_user.user_id', $subquery)
->union( $buddylist)
->groupBy('sender')
->groupBy('user_id')
->orderBy('created_at','desc')
->get();
php mysql laravel
edited Jan 1 at 13:19
Dharman
5,21662553
asked Jan 1 at 12:09
Reneesh KurianReneesh Kurian
408
I am creating a message service with Laravel.
I want to display recent messages from all based on time. This is what I am trying to implement
The following are my tables
- message table
- message_user table
I tried the following code
SELECT DISTINCT user_id,sender
FROM (
SELECT DISTINCT message_user.user_id, messages.sender, messages.created_at
FROM messages
JOIN message_user ON message_user.message_id=messages.id AND message_user.user_id=225
UNION
SELECT DISTINCT message_user.user_id, messages.sender, messages.created_at FROM messages
JOIN message_user ON message_user.message_id=messages.id AND messages.sender=225 AND message_user.user_id NOT IN (
SELECT DISTINCT messages.sender
FROM messages
JOIN message_user ON message_user.message_id=messages.id AND message_user.user_id=225
)
) mytable
ORDER BY created_at
The problem is that the output is not in the expected format (that is not sorted based on created_at)
Following is the corresponding Laravel code I have tried
$id = $request->user()->id;
$user = User::find($id);
$buddylist = $user->messages()->select(DB::raw('message_user.user_id,messages.sender,messages.created_at'));
$subquery = Message::select(DB::raw(' messages.sender'))->join('message_user','messages.id','=','message_user.message_id') ->where("message_user.user_id",'=',$request->user()->id);
$mybuddy = Message::selectRaw('message_user.user_id,messages.sender,messages.created_at')
->join('message_user','messages.id','=','message_user.message_id')
->where("messages.sender",'=',$request->user()->id)
->whereNotIn('message_user.user_id', $subquery)
->union( $buddylist)
->groupBy('sender')
->groupBy('user_id')
->orderBy('created_at','desc')
->get();
php mysql laravel
php mysql laravel
edited Jan 1 at 13:19
Dharman
5,21662553
asked Jan 1 at 12:09
Reneesh KurianReneesh Kurian
408
edited Jan 1 at 13:19
Dharman
5,21662553
asked Jan 1 at 12:09
Reneesh KurianReneesh Kurian
408
edited Jan 1 at 13:19
Dharman
5,21662553
edited Jan 1 at 13:19
Dharman
5,21662553
edited Jan 1 at 13:19
Dharman
5,21662553
5,21662553
asked Jan 1 at 12:09
Reneesh KurianReneesh Kurian
408
asked Jan 1 at 12:09
Reneesh KurianReneesh Kurian
408
asked Jan 1 at 12:09
Reneesh KurianReneesh Kurian
408
408
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
2
If you have another column named 'receiver_id' what you could do is:
$query="select * from messages WHERE sender_id='user_id' or receiver_id='user_id' ORDER BY created_at asc";
answered Jan 1 at 12:45
Bash JrBash Jr
214
im trying to create sender list not all messages.if i use your query i didn't get
– Reneesh Kurian
Jan 1 at 15:13
i want to get this sender list not all messages imgur.com/a/0UCj29M
– Reneesh Kurian
Jan 1 at 15:21
do you want to get list of user_id of users who sent message to admin?
– Bash Jr
Jan 1 at 15:40
yes and wise versa but not repeat the user_id sender combination
– Reneesh Kurian
Jan 1 at 15:47
select DISTINCT sender_id from messages receiver_id='admin_id' ORDER BY created_at asc
– Bash Jr
Jan 1 at 15:50
|
show 3 more comments
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
});
}
});
draft saved
draft discarded
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%2f53995311%2fsql-query-for-creating-message-list%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
2
If you have another column named 'receiver_id' what you could do is:
$query="select * from messages WHERE sender_id='user_id' or receiver_id='user_id' ORDER BY created_at asc";
answered Jan 1 at 12:45
Bash JrBash Jr
214
im trying to create sender list not all messages.if i use your query i didn't get
– Reneesh Kurian
Jan 1 at 15:13
i want to get this sender list not all messages imgur.com/a/0UCj29M
– Reneesh Kurian
Jan 1 at 15:21
do you want to get list of user_id of users who sent message to admin?
– Bash Jr
Jan 1 at 15:40
yes and wise versa but not repeat the user_id sender combination
– Reneesh Kurian
Jan 1 at 15:47
select DISTINCT sender_id from messages receiver_id='admin_id' ORDER BY created_at asc
– Bash Jr
Jan 1 at 15:50
|
show 3 more comments
2
If you have another column named 'receiver_id' what you could do is:
$query="select * from messages WHERE sender_id='user_id' or receiver_id='user_id' ORDER BY created_at asc";
answered Jan 1 at 12:45
Bash JrBash Jr
214
im trying to create sender list not all messages.if i use your query i didn't get
– Reneesh Kurian
Jan 1 at 15:13
i want to get this sender list not all messages imgur.com/a/0UCj29M
– Reneesh Kurian
Jan 1 at 15:21
do you want to get list of user_id of users who sent message to admin?
– Bash Jr
Jan 1 at 15:40
yes and wise versa but not repeat the user_id sender combination
– Reneesh Kurian
Jan 1 at 15:47
select DISTINCT sender_id from messages receiver_id='admin_id' ORDER BY created_at asc
– Bash Jr
Jan 1 at 15:50
|
show 3 more comments
2
2
2
If you have another column named 'receiver_id' what you could do is:
$query="select * from messages WHERE sender_id='user_id' or receiver_id='user_id' ORDER BY created_at asc";
answered Jan 1 at 12:45
Bash JrBash Jr
214
If you have another column named 'receiver_id' what you could do is:
$query="select * from messages WHERE sender_id='user_id' or receiver_id='user_id' ORDER BY created_at asc";
answered Jan 1 at 12:45
Bash JrBash Jr
214
answered Jan 1 at 12:45
Bash JrBash Jr
214
answered Jan 1 at 12:45
Bash JrBash Jr
214
answered Jan 1 at 12:45
Bash JrBash Jr
214
214
im trying to create sender list not all messages.if i use your query i didn't get
– Reneesh Kurian
Jan 1 at 15:13
i want to get this sender list not all messages imgur.com/a/0UCj29M
– Reneesh Kurian
Jan 1 at 15:21
do you want to get list of user_id of users who sent message to admin?
– Bash Jr
Jan 1 at 15:40
yes and wise versa but not repeat the user_id sender combination
– Reneesh Kurian
Jan 1 at 15:47
select DISTINCT sender_id from messages receiver_id='admin_id' ORDER BY created_at asc
– Bash Jr
Jan 1 at 15:50
|
show 3 more comments
im trying to create sender list not all messages.if i use your query i didn't get
– Reneesh Kurian
Jan 1 at 15:13
i want to get this sender list not all messages imgur.com/a/0UCj29M
– Reneesh Kurian
Jan 1 at 15:21
do you want to get list of user_id of users who sent message to admin?
– Bash Jr
Jan 1 at 15:40
yes and wise versa but not repeat the user_id sender combination
– Reneesh Kurian
Jan 1 at 15:47
select DISTINCT sender_id from messages receiver_id='admin_id' ORDER BY created_at asc
– Bash Jr
Jan 1 at 15:50
im trying to create sender list not all messages.if i use your query i didn't get
– Reneesh Kurian
Jan 1 at 15:13
im trying to create sender list not all messages.if i use your query i didn't get
– Reneesh Kurian
Jan 1 at 15:13
i want to get this sender list not all messages imgur.com/a/0UCj29M
– Reneesh Kurian
Jan 1 at 15:21
i want to get this sender list not all messages imgur.com/a/0UCj29M
– Reneesh Kurian
Jan 1 at 15:21
do you want to get list of user_id of users who sent message to admin?
– Bash Jr
Jan 1 at 15:40
do you want to get list of user_id of users who sent message to admin?
– Bash Jr
Jan 1 at 15:40
yes and wise versa but not repeat the user_id sender combination
– Reneesh Kurian
Jan 1 at 15:47
yes and wise versa but not repeat the user_id sender combination
– Reneesh Kurian
Jan 1 at 15:47
select DISTINCT sender_id from messages receiver_id='admin_id' ORDER BY created_at asc
– Bash Jr
Jan 1 at 15:50
select DISTINCT sender_id from messages receiver_id='admin_id' ORDER BY created_at asc
– Bash Jr
Jan 1 at 15:50
|
show 3 more comments
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53995311%2fsql-query-for-creating-message-list%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
