[SOLVED] Sort the merged result set from two CodeIgniter queries by the mid-string number

Sort the merged result set from two CodeIgniter queries by the mid-string number

I have an array that consists of two merged arrays, which I have sorted using array_multisort(). The problem is that it seem to be displayind data from one array at a time, when I need it to display data depending on high-to-low values from two separate keys.

Right now it looks something like this...

[ Topic posted 10 minutes ago ]
[ Topic posted 20 minutes ago ]
[ Topic posted 30 minutes ago ]
[ Reply by user, 10 minutes ago ]
[ Reply by user,  20 minutes ago ]
[ Reply by user,  30 minutes ago ]

When in fact I need to display it in this order

[ Topic posted 10 minutes ago ]
[ Reply by user, 10 minutes ago ]
[ Topic posted 20 minutes ago ]
[ Reply by user, 20 minutes ago ]
[ Topic posted 30 minutes ago ]
[ Reply by user, 30 minutes ago ]

How can this be achieved?

Here is my PHP code:

$topics = $this->db->select("*")
    ->order_by("id", "desc")
    ->limit(10)
    ->get("forum_topics")
    ->result();

$replies = $this->db->select("*")
    ->order_by("id", "desc")
    ->limit(10)
    ->get("forum_topics_replies")
    ->result();

$merged = array_merge($topics, $replies);

$sort = array();
foreach($merged as $k => $v) {
    $sort['reply_date'][$k] = $v;
    $sort['topic_date_made'][$k] = $v;
}

array_multisort($sort['topic_date_made'], SORT_DESC, $sort['reply_date'], SORT_DESC, $merged);

return $merged;

Edit: If using join queries, how can that be accomplished?

$topics = $this->db->select("*")
 ->from("forum_topics")
 ->limit($limit)
 ->join('forum_topics_replies', 'forum_topics.id = forum_topics_replies.id')
 ->order_by("forum_topics.id", "desc")
 ->order_by("forum_topics_replies.id", "desc")
 ->get()
 ->result();

Edit 2: I need to be able to use the following fields:

topic table: topic_name, id, parent, topic_date_made

reply table: parent, author , reply_date, id

Solution

i think an union statement could solve your problem - what about this ?

$query = $this->db->query('
    SELECT id, 1 as is_topic, parent, "" as author, topic_name, "Topic posted" AS praefix, topic_made_date as union_date FROM forum_topics
    UNION
    SELECT id, 0 as is_topic, parent, author, "" as topic_name, "Reply by user" AS praefix, reply_date as union_date FROM forum_topics_replies
    ORDER BY union_date DESC
    LIMIT 0,10
');