問題描述

我有兩個集合，我想將它合并到一個變量中(當然，按一列排序 - 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模板網！