How to UNION Craft blog articles array with Social Media plugin (non craft table)

Question

I'm looking for a way to merge blog article entries with social media entries (non craft table). The idea is to output both entry types together from the same array, this will allow me to order all entries by date.

The social media plugin that I have put together queries the non-craft social media table but I need a solution to UNION the two tables together.

How would I go about doing this within Craft?

My table structure for craft_archived_social_items

• id int
• unique_id varchar(255) NOT NULL,
• account varchar(255) NOT NULL,
• category varchar(255) NOT NULL,
• title text NOT NULL,
• content text NOT NULL,
• image varchar(255) NOT NULL,
• link text NOT NULL,
• date datetime NOT NULL,
• status varchar(255) NOT NULL DEFAULT 'inactive',

• date_cached datetime NOT NULL,

  PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

My service file is currently using the db->createCommand to query the table

    $social_items = craft()->db->createCommand()
        ->select('title, content, date, category, id, account, unique_id, image, link')
        ->from('archived_social_items') 
        ->offset($offset)
        ->limit($counter)
        ->order('date desc')
        ->queryAll();

Answer 1

More of general guidance than an answer, but I'd look into creating a custom ElementType and overriding the modifyElementsQuery in your ElementType to latch onto the main query Craft uses to grab elements. From there you'd be able to join in whatever other data you need.

Answer 2

Simple solution

This was my first Craft/Yii build so it was rather straightforward once I knew the basics.

public function getAllItems($offset, $counter)
{
    $social_items = craft()->db->createCommand()
        ->select('title, content, date, category, id, account, unique_id, image, link')
        ->from('social_tablename') 
        ->offset($offset)
        ->limit($counter)
        ->order('date desc')
        ->queryAll();

$criteria = craft()->elements->getCriteria(ElementType::Entry);
$criteria->section = 'blog';
$criteria->order   = 'postDate desc';
//$criteria->limit   = 5; // Limit if required

$blog_items = array();
$i = 0;
foreach ($criteria as $entry)
{
  $blog_items[$i]['title'] = $entry->title;
  $blog_items[$i]['content'] = $entry->shortDescription;
  $blog_items[$i]['postImage'] = $entry->postImage;
  $blog_items[$i]['category'] = 'blog'; /* Channel name - news/posts */
  $blog_items[$i]['link'] = $entry->url;
  $blog_items[$i]['url'] = $entry->url;
  $blog_items[$i]['date'] = $entry->postDate;
  $i++;
}

$social_items = array_merge($social_items, $blog_items);

/* Sorts multidimentional array by 'column' */
$this->aasort($social_items,"date");

return $social_items;

}