There should be a linkage between the two. No different from this Forum someone asked a question and someone else replies it is still all linked. You cannot possibly have a reply to something without the question so somewhere there's a question that should be linked to a reply. Or am I totally off of what kind of tickets we are talking about here it sounds like the trouble tickets that you submit on shared hosting like in A Cpanel.
Put another way it is impossible to have a reply unless there's an initial question.

@jlrdw - I give up. Not sure why I bother, I'm obviously very crap at explaining things.

Its NOT like this forum. This forum has posts and replies. A parent/child relationship. The OP has two types of children. They don't need to be related to each other except for the fact that they have the same parent.

@Snapey if they have the same parent why don't you just show him how to do a simple join query to accomplish this instead all that in and out of a collection thing, this is no different than a simple pedigree program.
You said They don't need to be related to each other except for the fact that they have the same parent.
Think about what you just said in that statement, not related and same parent really?

@jlrdw why don't you just explain how to do it then? Hard when you don't appreciate the problem.

On the otherhand I showed how to do it with one merge statement.

@Snapey your answer is good I'm confused on why the OP hasn't used it and marked as solved. You showed him what's equivalent to a union query.
Edit: wasn't on main pc earlier. I would have parent child child, this was just done quick.
ticket table, a ticket in table (tickin) and reply

SELECT ticketid, sometext, updated as tu FROM tickin
UNION ALL
SELECT ticketid, sometext, updated as tu from reply
Where ticketid = 1   //if stored procedure pass in the ticket param otherwise from session.
ORDER BY tu

Just a quick example showing how to query it, you could do it with just the two tables also, I just prefer not to have the parent and child in one table . But for small amounts of data, @Snapey gave a good answer. But again, to others you need sql if it is a large resultset. The query can be paginated, where as the collection has to have all inserted right away. For example you don't want 10,000 in an array (collection)
This is one of the only things I don't like about laravel, things like collections makes data easy to work with. But folks forget that as the data grows, these easy techniques don't work anymore.
Now don't jump on me here, I like laravel a lot, just saying sometimes it's best just to write a query long hand and be done with it.

@Snapey understand my problem, big thanks. Sorry but I needed to go and get some sleep yesterday. I will make some tests and mark it as solved. I just tested it once yesterday, it worked fine. I will not forget go mark it as solved. -Thanks.

@jaheller I was thinking about it again this morning, you know as you mindlessly brush your teeth, and I was thinking that why is a reply not just a type of ticket event?

So you have your ticket;

• Someone changes the priority (event)
• Someone assigns the ticket to snapey (event)
• snapey leaves a reply (event) and (replie)
• snapey reassigns ticket to jaheller (event)
• jaheller leaves a reply (event) and (replie)
• jaheller closes the ticket (event)

So in your event model you store the type of event, and if the event is a reply then there is a reply id.

Then you can just get all events for a ticket, in event order or date order, and then also get all the replies related to those events. You can then easily display like an audit trail.

@Snapey

I wouldn't like to change my whole system (a ticket_replie for example has a template feature etc.). Answering to a ticket isn't always an event (just when ticket was opened).

Using your code atm won't work. When a ticket is opened, first of all a new ticket is stored in database, after this the ticket_event and at least the ticket_replie.

Your code wouldn't work I think because not for each ticket_replie is a ticket_event created (events are only created for certain actions). Did you understand me? Merge will need same amount of ticket_events as ticket_replies I think?!

Opening a freshly created ticket using your 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'));

Results in a collection, but not in the correct order:

    Collection {#381 ▼
#items: array:2 [▼
 1 => ticket_replie {#384 ▶}
    0 => ticket_event {#389 ▶}
]
}

The ticket_event and ticket_replie are created at same timestamp (2016-03-23 14:54:51).

You have created_at and Updated_at, how can you sort anything with 2 timestamp columns, Just use one timestamp column and read https://laravel.com/docs/5.1/queries#unions
A union query with an order by works period. Quit making this harder than it has to be.

@jlrdw

Both will need the created_at. Let me explain it, an event is created for example when a user starts a ticket. Event will be created for example too when a supporter or the user closes the ticket. So an event is not related directly to a ticket_replie. The are independently. The amount of ticket_replies will be not the same as the amount of ticket_events. I think @Snapey understand the functionality.

You said you wanted something like this

-ticket event
-ticket_replie
-ticket event
-ticket_replie

First record would be the earliest timestamp, closed would be last timestamp. You cannot possibly sort by timestamp with four timestamp fields , I guess there might be away but that sure does make it extremely hard I would only have one timestamp field in each table and some common sense says top most is the created the last timestamp closed. Then @Snapey answer or a union would get you exactly what you are after everything flowing in order. Regardless whether it's a -ticket event or -ticket_replie. Did you read https://laravel.com/docs/5.1/queries#unions In the documentation?

Thought of something else if you absolutely needed a created at and a closed simply copy the first records timestamp to that field but don't attempt to use it in the sort again too many columns to mess with when trying to sort.

@jlrdw

Yep this order is correct. Using the code of @Snapey won't work, I think because the merge expects the same amount of each side it will merge, so using merge won't work if for example I've got 1 ticket_replie (first question of ticket made by user) and 2 ticket_events (one for example ticket opened & one for ticket closed).

Then try a union query it doesn't have to be the same amount in a case like that. I kind of know what you mean you can have one for example a question and four replies before you have another question that sort of thing.

@jlrdw

Any suggestion how to built the query? Yes you've got it, the amount of ticket_replies and ticket_events can be different.

@jaheller Solution came to me.

• get 1st record
• do union as above
• get last record
• display first record
• loop foreach through union DON'T display first or last record // may need a dummy if statement to skip first / last
• display last (closed) record. Kinda a hack, but it will work.
  As far as building the query, draw on the example from docs and experiment, surely you'll get it.

@jlrdw

How you mean this, get 1st record? Which code should I use to retrieve? The code @Snapey posted?

The first record is a created at not updated at, so retrieve it using regular eloquent or query builder techniques. You will be passing to the view 3 results, first record, results of the union query, and last record. Using the ->with technique. kinda like

return view('pet/petslist2')->with('data', $data['pets'])->with('data2', $data['page_links']);

Why don't you just use the below query

select * from (

    select id, ticket_id, template_id,user_id, old_status, new_status, hidden, text, created_at, updated_at, type as 'event' from ticket_events where ticket_id = ? 

    UNION

    select id, ticket_id, template_id as null,  user_id,old_status as null, new_status as null,hidden as null,  text, created_at, updated_at, type as 'reply' from ticket_replies where ticket_id = ? 
    
) t order by created_at

@premsaurav I believe I suggested a union already but it needs to be a union all not just a union. And I think I already mentioned the first record created at can't be part are the resultset because the results it is dealing with updated and not created at if I understood the original question.

@jlrdw

Oh my gosh, this is so confusing. Could you gave me a detailed step by step hint how to do so? I never used UNIONs before, just eloquent.

@jlrdw Union, Union All should not really make a difference here ?

@jaheller 50 comments already, may be its time to refactor something. Have you explored polymorphic relation ?

@jaheller Rather than give code. State the problem. What are the 3 tables. What is the driving table? The ticket table? Does every ticket have an event? Replies can be linked to a ticket or a tickets specific event. Can you edit an existing ticket or reply, meaning you care about created_at and/or updated_at. You may not even need a UNION, just a simple join.

@premsaurav

I read about it right now, but I have no idea how this would be able to help me?

@jimmck

I store all "global" ticket details in my "ticket" table. A ticket has a oneToMany relation to the "ticket_replies". The "ticket_events" table is just connected to the ticket by referring to the ticket_id. A polymorphic relation is used in case you need the model to belong to more than one other model. How could I use this in the case described above?

A polymorphic relation is used in case you need the model to belong to more than one other model. How could I use this in the case described above?

Think beyond the obvious.

I will call this as ticket_actions (may be) which is polymorphically related to ticket_events and ticket_replies. A ticket then can have many ticket_actions. You can sort them, limit them , all eloquently.

@premsaurav

I think I get a bit what you try to say. What information should I store in ticket_actions? I just would like to be able to display the ticket_events and ticket_replies in the order I described before.

ticket_action : id, ticket_id, actionable_id, actionable_type, created_at, updated_at ... that it

Once you create a ticket_event or ticket_replie ( why not ticket_reply) , just create an action for the same and associate the action to the ticket.

One would say, why create extra table, I would argue that writes are rare than reads, so even if it takes two db writes to create convenience for my reads i will go with it.

Your code wouldn't work I think because not for each ticket_replie is a ticket_event created (events are only created for certain actions). Did you understand me? Merge will need same amount of ticket_events as ticket_replies I think?!

No, absolutely not.

There is no need to have the same number of events and replies with my method.

What is definitely required is some differentiation between the records. You cannot hope to sort them correctly if they have the same created_at timestamps. So, if this is the case then you should choose my second suggestion and make every reply a type of event.

Then you can just list all events and link to the reply if relevant (creating a link from the event to the reply).

@Snapey

Could you show me how the table design might have to look in case I make every reply an event too? Would you suggest this solution ore the one of @premsaurav ?

@premsaurav

using your code, how will I be able to order the related tables (ticket_replies and ticket_events) in the order I described?

You have had many people offer advice, at this point, could you try to write some of the code yourself? And if still having trouble, there is ready made code try https://github.com/thekordy/ticketit or https://github.com/ladybirdweb/faveo-helpdesk or www.faveohelpdesk.com
But really with the code offered already you should be able to put it together yourself, dig into the docs if having trouble on the way to structure a query.