合并和排序两个雄辩收藏？ [英] Merge and Sort two Eloquent Collections?

问题描述

我有两个集合，我想将它合并到一个变量（当然，排序一个collumn - created_at ）。我可以这样做吗？

我的控制器看起来：

  $ replies = Ticket :: with（'replies'，'replies.user'） - > find（$ id）; 
 $ logs = DB :: table（'logs_ticket'）
  - > join（'users'，'users.id'，'='，'mod_id'）
  - >其中（'ticket_id'，'='，$ id）
  - > select（'users.username'，'logs_ticket.created_at'，'action'）
  - > get（）; 
  

我的输出为例：

回复：

  ID | ticket_id |用户名|消息| created_at 
 1 | 1 |有人| asdfghj | 2014-04-12 12:12:12 
 2 | 1 |有人| qwertyi | 2014-04-14 12:11:10 
  

日志：

  ID | ticket_id |用户名|行动| created_at 
 1 | 1 |有人|关闭| 2014-04-13 12:12:14 
 2 | 1 |有人|打开| 2014-04-14 14:15:10 
  

我想要这样的东西：

  ticket_id |表|用户名|消息| created_at 
 1 |回复|有人| asdfghj | 2014-04-12 12:12:12 
 1 |日志|有人|关闭| 2014-04-13 12:12:14 
 1 |日志|有人|打开| 2014-04-14 11:15:10 
 1 |回复|有人| qwertyi | 2014-04-14 12:11:10 
  

编辑：

我的票证模型看起来是：

 <？php 
 
 class Ticket扩展口述{
 
 protected $ table ='tickets'; 
 
 public function replies（）{
 return $ this-> hasMany（'TicketReply'） - > orderBy（'ticketreplies.created_at'，'desc'）; 
} 
 
 public function user（）
 {
 return $ this-> belongsTo（'User'）; 
} 
} 
？> 
  

解决方案

你不会得到准确的一般来说，通过 $ collection-> merge（$ otherCollection）; ，并按 $ collection-> sort（）; 进行排序。但是，由于没有唯一的ID以及所需的表列，合并将无法正常工作，您必须手动进行。

他们实际上都是不同类型的集合我认为（一个基于 Eloquent \Model 将是 Eloquent\Collection ，另一个是标准的集合），这可能会导致自己的问题。因此，我建议使用DB :: table（）两者，并使用可以控制的列增加结果。

至于实现这一点的代码，我不确定我在Laravel没有做很多低级数据库工作，所以不知道创建查询的最佳方式。无论哪种方式，只是因为看起来像是开始痛苦地用两个查询和一些PHP合并来管理这个问题，我建议在一个数据库查询中进行全部操作。它实际上看起来更整洁，可以说更可维护：

您需要的SQL是这样的：

  SELECT * FROM 
（
 SELECT 
`r`.`ticket_id`，
'replies 'AS`table`，
`u`.`username`，
`r`.`message`，
`r`.`created_at` 
 FROM`replies` AS `r` 
 LEFT JOIN`users` AS`u` 
 ON`r`.`user_id` =`u`.`id` 
 WHERE`r`.`ticket_id` =？ 
）UNION（
 SELECT 
`l`.`ticket_id`，
'logs'AS`table`，
`u`.`username`，
`l`.`action` AS`message`，
`l`.`created_at` 
 FROM`logs` AS`l` 
 LEFT JOIN`users` AS`u` 
 ON`l`.`user_id` =`u`.`id` 
 WHERE`l`.ticket_id` =？
）
 ORDER BY`created_at` DESC 
  

这很简单：做这两个查询，返回sa我的列， UNION 他们然后排序MySQL中的结果集。希望它（或类似的东西，就像我不得不猜测你的数据库结构）将为你工作。

至于将它翻译成Laravel DB :: -style查询，我想这取决于你。

I've two Collections and I want merge it to one variable (of course, with ordering by one collumn - created_at). How Can I do that?

My Controllers looks that:

$replies = Ticket::with('replies', 'replies.user')->find($id);
$logs = DB::table('logs_ticket')
        ->join('users', 'users.id', '=', 'mod_id')
        ->where('ticket_id', '=', $id)
        ->select('users.username', 'logs_ticket.created_at', 'action')
        ->get();

My Output looks for example:

Replies:

ID | ticket_id | username | message | created_at
1  | 1         | somebody | asdfghj | 2014-04-12 12:12:12
2  | 1         | somebody | qwertyi | 2014-04-14 12:11:10

Logs:

ID | ticket_id | username | action | created_at
1  | 1         | somebody | close  | 2014-04-13 12:12:14
2  | 1         | somebody |  open  | 2014-04-14 14:15:10

And I want something like this:

ticket_id | table | username | message | created_at
1         |replies| somebody | asdfghj | 2014-04-12 12:12:12
1         | logs  | somebody |  close  | 2014-04-13 12:12:14
1         | logs  | somebody |  open   | 2014-04-14 11:15:10
1         |replies| somebody | qwertyi | 2014-04-14 12:11:10

EDIT:

My Ticket Model looks that:

<?php

class Ticket extends Eloquent {

    protected $table = 'tickets';

    public function replies() {
        return $this->hasMany('TicketReply')->orderBy('ticketreplies.created_at', 'desc');
    }

    public function user()
    {
        return $this->belongsTo('User');
    }
}
?>

解决方案

You're not going to be able to get exactly what you want easily.

In general, merging should be easy with a $collection->merge($otherCollection);, and sort with $collection->sort();. However, the merge won't work the way you want it to due to not having unique IDs, and the 'table' column that you want, you'll have to make happen manually.

Also they are actually both going to be collections of different types I think (the one being based on an Eloquent\Model will be Eloquent\Collection, and the other being a standard Collection), which may cause its own issues. As such, I'd suggest using DB::table() for both, and augmenting your results with columns you can control.

As for the code to achieve that, I'm not sure as I don't do a lot of low-level DB work in Laravel, so don't know the best way to create the queries. Either way, just because it's looking like starting to be a pain to manage this with two queries and some PHP merging, I'd suggest doing it all in one DB query. It'll actually look neater and arguably be more maintainable:

The SQL you'll need is something like this:

SELECT * FROM
(
    SELECT
        `r`.`ticket_id`,
        'replies' AS `table`,
        `u`.`username`,
        `r`.`message`,
        `r`.`created_at`
    FROM `replies` AS `r`
    LEFT JOIN `users` AS `u`
        ON `r`.`user_id` = `u`.`id`
    WHERE `r`.`ticket_id` = ?
) UNION (
    SELECT
        `l`.`ticket_id`,
        'logs' AS `table`,
        `u`.`username`,
        `l`.`action` AS `message`,
        `l`.`created_at`
    FROM `logs` AS `l`
    LEFT JOIN `users` AS `u`
        ON `l`.`user_id` = `u`.`id`
    WHERE `l`.ticket_id` = ?
)
ORDER BY `created_at` DESC

It's pretty self-explanatory: do the two queries, returning the same columns, UNION them and then sort that result set in MySQL. Hopefully it (or something similar, as I've had to guess your database structure) will work for you.

As for translating that into a Laravel DB::-style query, I guess that's up to you.

这篇关于合并和排序两个雄辩收藏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！