Here's the code I use at the moment. It works fine for the following process: -ticket event created -ticket replie created -ticket closed (ticket event created)

When I make some more ticket_replies between open & close process it fails. Any idea why?

code:

class ticketHelper
{

   var $ticket_replies;
   var $ticket_events;

   function orderReplies()
   {
      $ticket_replies = $this->ticket_replies;
      $ticket_events = $this->ticket_events;

      $events_replies = $ticket_events->merge($ticket_replies)->sortByDesc('created_at');

      $filtered = $events_replies->sort(function($itemA, $itemB)
      {
         echo $itemA->created_at . "<br>";
        if($itemA->created_at == $itemB->created_at)
        {
            if(is_a($itemA,'ticket_event'))
            {
              return true;
            }
            else
            {
              return false;
            }
        } 
            else
            {
                return (($itemA->created_at >= $itemB->created_at) && ($itemA->created_at <= $itemB->created_at));
            }
      });
      dd($filtered);
      return $filtered;

  }

}

call:

$ticket_replies = Ticket_replie::where('ticket_id',$id)->get();
   $ticket_events = Ticket_event::where('ticket_id',$id)->get();

   $ticketHelper = new \ticketHelper();
   $ticketHelper->ticket_replies = $ticket_replies;
   $ticketHelper->ticket_events = $ticket_events;


   $ticket_replies = $ticketHelper->orderReplies();

Don't get this line,

return (($itemA->created_at >= $itemB->created_at) && ($itemA->created_at <= $itemB->created_at));

each timestamp should be unique, shouldn't be any duplicate timestamps.

what are you trying to achieve by re-sorting the records?

@jlrdw timestamp only has a granularity of one second so if an event and a replie are created on the same call, they could have the same timestamp

@Snapey

Did you see an fault in my code? It works fine, if a ticket is created (ticket_event & ticket_replie), a second replie is created and the ticket is closed. In case there are more ticket_replies before it's closed my code just return the ticket_replies.

@Snapey as I see it that is so far the biggest problem with all not working, is there a way to prevent duplicate timestamps? This problem has become intriging (probably spelled wrong). Either a union or your solution works the same, but neither can work with duplicate timestamps. Have to always have reply following I'm guessing. Maybe a re-construct in an if else block to make a reply come after a ticket (event) with equal timestamps, a sort inside a sort. But this is good stuff.

Well I could not see a fault because I can't see what it is trying to do. Why don't you explain please.

Just use natural language to describe how you want the entries to be sorted when there are two with the same timestamp.

If this were an audit trail and they occurred at the same time, does it actually matter which order they are in?

@Snapey I got it, I got it. R comes after E, so an order by timestamp, eventtype would work. A reply would auto come after an event. Maybe each table needs an added field of eventtype just to get it sorted right. EDIT: And the order probably matters.

@Snapey

Okay in natural language. I would like to display the ticket_replies & ticket_events in a view. The event should be always "on the top". But only the oldest one. For example if the ticket_event and the ticket_replie are created at the same time (when a ticket is opened) I would like the event be on the top, after this there should be the replie. Let's say a ticket is created, 3 answers are posted after the first ticket_replie and now the ticket is closed. So I would like to have this order in my view.

-ticket_event (ticket created)
-ticket_replie (1st question from user)
-ticket_replie (answer from supporter)
-ticket_replie (answer from user)
-ticket_replie(answer from supporter)
-ticket_event (ticket closed by supporter)

For this I would like to have the ticket_replie & ticket_event combined in a collection in the right order, so I just need to loop them in my view.

See latest reply of order by.

So, my code posted many moons ago should work. As each entry will have a different created_at timestamp, they will be listed in the correct order. The only consideration is if two are created in the same second then do you care which is on top.

If you are creating all these event, reply, reply, reply, event in a test harness or by database seeding then they could indeed all have the same value but this is only an issue for testing.

@Snapey

using your code results in problems:

http://puu.sh/nSwCw/1935c7d294.png

code:

$ticket_replies = Ticket_replie::where('ticket_id',$id)->get();
   $ticket_events = Ticket_event::where('ticket_id',$id)->get();

   $events_replies = $ticket_events->merge($ticket_replies);

   dd($events_replies->sortBy('created_at'));

Just a thought, do you have a $dates entry in your model?

On that dd you just screenshot, please expand all the branches so we can see the created_at time.

@Snapey

Here's a list of created_at of each entry in order displayed before.

ticket_replie "created_at" => "2016-03-21 14:24:34"
ticket_event "created_at" => "2016-03-21 14:24:34"
ticket_replie "created_at" => "2016-03-21 18:23:57"
ticket_event "created_at" => "2016-03-22 14:00:41"

So the only issue is that the first two are the wrong way around, presumably because you create the event at the same time as the reply?

If this is the case, and you actually care that the event is shown first, then these are the options I would suggest.

1. Include a column on each table for timestamp which includes milliseconds and then use that instead of created_at

2. Include a milliseconds field and then sort the records $events_replies->sortBy('created_at','milliseconds'));

3. Add a column replie_id on the event table. Create the reply, then create an event, and populate the replie_id field with the id of the replie just created. Create a relationship in the event model

    public function replie()
    {
        return $this->hasOne(TicketReplie::class);
    }

Then in the controller, load the ticket_events with('replie') Then in the view, list the events and if the event has a reply, show that instead of the event

4. Append a value to each model and then use that as an additional sort value

    public function getTypeAttribute()
    {
        return 1;   //return 0 in the other model
    }

    dd($events_replies->sortBy('created_at','type'));

A few ideas to go on?

@Snapey

Ohh that's all just a dirty workaround ;). Seems like your solution has the same problem. I closed the ticket at the end and now the latest event is not listed in the merged collection:

http://puu.sh/nSzFg/31ffe5c50f.png

Thats a different problem with your poor design. Oh Well.

@jaheller a few replies above I told you the solution, I just tested your ticket system on my computer with duplicate typestamps, and it works.

SELECT ticketid, sometext, created_at as tu, eventtype FROM ticket  // event type e here
UNION ALL
SELECT ticketid, sometext, created_at as tu, eventtype FROM reply  //eventtype r in this table
Where ticket.id = 1  // however you get ticket no here
ORDER BY tu, eventtype;  //ordering by tu, eventtype puts them in order you want.

Again not your database schema, just done quickly for testing. This works 100% Why do you refuse to add an event type so it will sort correctly.
And with an eventtype the solution that @Snapey gave you using collections would work. Me I'd use the
union all query, and no matter what someone else says, union all is needed, not just union.
But as @Snapey said oh well, we tried to help.

See my post and solution:

https://laracasts.com/discuss/channels/eloquent/unions-and-pagination