合并和排序两个 Eloquent 集合?

问题描述

我有两个集合，我想将它合并到一个变量中(当然，按一列排序 - created_at).我该怎么做?

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?

我的控制器看起来像:

$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();

我的输出查找例如:

回复:

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

日志:

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

我想要这样的东西:

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

我的票证模型看起来是这样的:

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.

通常，使用 $collection->merge($otherCollection); 合并应该很容易，并使用 $collection->sort(); 进行排序.但是，由于没有唯一 ID 和所需的表格"列，合并将无法按照您希望的方式进行，您必须手动进行.

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.

此外，它们实际上都是不同类型的集合我认为(基于 EloquentModel 的集合将是 EloquentCollection，另一个是标准的 Collection)，这可能会导致其自身的问题.因此，我建议对两者都使用 DB::table()，并使用您可以控制的列来扩充您的结果.

Also they are actually both going to be collections of different types I think (the one being based on an EloquentModel will be EloquentCollection, 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.

至于实现这一点的代码，我不确定，因为我没有在 Laravel 中做很多低级数据库工作，所以不知道创建查询的最佳方法.无论哪种方式，仅仅因为通过两个查询和一些 PHP 合并来管理它看起来开始很痛苦，我建议在一个数据库查询中完成所有操作.它实际上看起来更整洁，可以说更易于维护:

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:

你需要的 SQL 是这样的:

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

这是不言自明的:执行两个查询，返回相同的列，UNION 它们，然后在 MySQL 中对结果集进行排序.希望它(或类似的东西，因为我不得不猜测您的数据库结构)对您有用.

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.

至于将其转换为 Laravel DB:: 样式的查询，我想这取决于您.

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

这篇关于合并和排序两个 Eloquent 集合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！