<p>More of general guidance than an answer, but I'd look into creating a custom <a href="http://buildwithcraft.com/docs/plugins/working-with-elements" rel="nofollow">ElementType</a> and overriding the <a href="http://buildwithcraft.com/classreference/elementtypes/BaseElementType#modifyElementsQuery-detail" rel="nofollow">modifyElementsQuery</a> 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.</p>

CC BY-SA 3.0
2015-10-23T09:46:16.017Z
Thanks Brad. I will post my results on here so that other developers can reuse it for there projects. Much appreciated!
Jack Stenson
CC BY-SA 3.0
2015-10-22T18:33:17.483Z
2015-10-22T18:33:17.483Z
Brad Bell
<p><strong>Simple solution</strong></p>

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

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

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

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

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

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

return $social_items;

}
</code></pre>

CC BY-SA 3.0
2015-11-04T10:51:24.137Z
2015-11-04T10:51:24.137Z
Jack Stenson
<p>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.</p>

<p>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.</p>

<p>How would I go about doing this within Craft?</p>

<p>My table structure for <code>craft_archived_social_items</code></p>

<ul>
<li><code>id</code> int  </li>
<li><code>unique_id</code> varchar(255) NOT NULL,</li>
<li><code>account</code> varchar(255) NOT NULL,</li>
<li><code>category</code> varchar(255) NOT NULL,  </li>
<li><code>title</code> text NOT NULL,</li>
<li><code>content</code> text NOT NULL,</li>
<li><code>image</code> varchar(255) NOT NULL,</li>
<li><code>link</code> text NOT NULL,</li>
<li><code>date</code> datetime NOT NULL,</li>
<li><code>status</code> varchar(255) NOT NULL DEFAULT 'inactive',</li>
<li><p><code>date_cached</code> datetime NOT NULL,</p>

<p>PRIMARY KEY (<code>id</code>)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;</p></li>
</ul>

<p>My service file is currently using the <code>db-&gt;createCommand</code> to query the table</p>

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

CC BY-SA 3.0
2015-10-14T18:07:10.503Z
Is this from a template?  Or PHP/a plugin? If it's from the latter, can you share the table schema for both? There will need to be some common data to join on.
Brad Bell
CC BY-SA 3.0
2015-10-15T08:05:57.930Z
It's a plugin which queries the social_media database and outputs into the template using {% set social = craft.plugin.getAllItems() %}
Jack Stenson
CC BY-SA 3.0
2015-10-15T08:18:15.727Z
The other table is using crafts own channel structure listing blog articles. The only common columns would be title and postDate / date but the data stored is all unique.
Jack Stenson
CC BY-SA 3.0
2015-10-15T15:44:57.270Z
Can you update the original question with that info?  Impossible to read in a comment.
Brad Bell
CC BY-SA 3.0
2015-10-22T11:21:58.727Z
Is there an single way of doing this without writing a complex query to access all of crafts relative tables?
Jack Stenson
<database><mysql>
How to UNION Craft blog articles array with Social Media plugin (non craft table)
# How to UNION Craft blog articles array with Social Media plugin (non craft table)

(Asked by: username_0 on 2015-10-14)

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();

**Tags:** database, mysql
> **Comments:**
> **username_1**: Is this from a template?  Or PHP/a plugin? If it's from the latter, can you share the table schema for both? There will need to be some common data to join on.  
> **username_0**: It's a plugin which queries the social_media database and outputs into the template using {% set social = craft.plugin.getAllItems() %}  
> **username_0**: The other table is using crafts own channel structure listing blog articles. The only common columns would be title and postDate / date but the data stored is all unique.  
> **username_1**: Can you update the original question with that info?  Impossible to read in a comment.  
> **username_0**: Is there an single way of doing this without writing a complex query to access all of crafts relative tables?  

---

## 2 answers

### Answer by username_1 on 2015-10-22. Score: 2

More of general guidance than an answer, but I'd look into creating a custom [ElementType](http://buildwithcraft.com/docs/plugins/working-with-elements) and overriding the [modifyElementsQuery](http://buildwithcraft.com/classreference/elementtypes/BaseElementType#modifyElementsQuery-detail) 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.

> **Comments:**
> **username_0**: Thanks Brad. I will post my results on here so that other developers can reuse it for there projects. Much appreciated!  

---

### Answer by username_0 on 2015-11-04. Score: 1

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;

}


---

