代码之家 › 专栏 › 技术社区 › handsome

雄辩的群信息

eloquent sql

handsome · 技术社区 · 6 年前

我在玩Laravels雄辩和一个MYSQL数据库

我需要显示对话列表

messages:
- id
- from_id
- to_id
- message
- read

|----|-----------|---------|-------|------|
| id | message   | from_id | to_id | read |  
|----|---------------------|--------------|
| 1  | hello     | 1       | 2     | 1    |
| 2  | hey       | 2       | 1     | 0    |
| 3  | there     | 2       | 1     | 0    |
| 4  | hi!       | 3       | 1     | 1    |
|----|-----------|---------|-------|------|

users:
- id
- name

|-----------|
| id | name |  
|-----------|
| 1  | john |
| 2  | mary | 
| 3  | jack | 
|-----------|

|----|------|---------|--------|
| id | name | message | unread |  
|----|------|---------|--------|
| 3  | mary | there   | 2      |  
| 4  | jack | hi!     | 0      |   
|----|------|-------- |--------|

我不确定这是否是我想要做的正确的表结构,但是SQL应该是

SELECT users.name, messages.message
FROM users, messages
WHERE messages.from_id = 1 OR messages.to_id = 1
GROUP BY ??
ORDER BY messages.id DESC

如何处理读取状态?请确保read属性类似于to\u id将设置为1。如何在查询中检查它?

0 回复 | 直到 6 年前

arcee123 6 年前

我在这里看到了一些东西。 1在聚合数据以计算未读电子邮件的数量时,您仍至少显示一条指示消息文本的消息。在预期的答复中:

|----|------|---------|--------|
| id | name | message | unread |  
|----|------|---------|--------|
| 3  | mary | there   | 2      |  
| 4  | jack | hi!     | 0      |   
|----|------|-------- |--------|

there ,这只是两封未读邮件中的一封。
我只是想知道这是不是故意的。

但是,为了让你去你想去的地方, 你有 read 本质上显示为二进制的列:1表示已读消息,0表示未读消息。

也就是说,您可以利用该逻辑通过使用算法来计算未读消息的数量: count(read) - sum(read) .

第二件事是你的加入。您必须将这些表连接在一起,才能建立用户名。在您的例子中,您根本没有使用联接,而是按字段过滤。如果你想要一份 from 用户,按 to from_id 致 user_id 这样你就能知道名字了。

在那里你可以按名字分组,至少 id, name, unread

SELECT users.name, count(read) - sum(read) as unread
FROM users, messages
WHERE users.id = messages.from_id and messages.to_id = 1 --to me
GROUP BY users.name
ORDER BY messages.from_id ASC

在laravel ORM中,试试这个(我知道我很接近,但我是移动的,而且还没能测试它,所以就试试看):

$users = DB::table('messages')
                 ->select(DB::raw('users.name, count(read) - sum(read) as unread'))

                 ->join('users', 'users.id', '=', 'messages.from_id')
                 ->where('to_id', '=', 1)
                 ->orWhere('from_id', '=', '1')
                 ->groupBy('users.name')
                 ->orderBy('messages.from_id','ASC')
                 ->get();

试试这个。

更新1:

select u.name, fm.message, count(m.unread) - sum(m.unread) as unread, 
  case when m.from_id = 1 then 'sent'
  when m.to_id = 1 then 'received'
  else null
  end status
from messages m
join users u on u.id = m.from_id
left join (
    Select max(id) as id, from_id, message from messages group by from_id
) fm on m.from_id = fm.from_id
where m.from_id = 1 or m.to_id = 1
group by u.name;

雄辩的:

DB::table('messages')
    ->select('u.name', 'fm.message', 'count(m.read) - sum(m.read) as unread', 'case when m.from_id = 1 then "sent" when m.to_id = 1 then "received" else null end status')
    ->join(users u', 'u.id', '=', 'u.from_id')
    ->leftjoin(DB::raw('Select max(id) as id, from_id, message from messages group by from_id', 'm.from_id', 'fm.from_id'))
    ->groupBy('users.name')
    ->orderBy('messages.from_id','ASC')
    ->get();