Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-23T09:46:16.017Z
Answers.Text: Thanks Brad. I will post my results on here so that other developers can reuse it for there projects. Much appreciated!
Answers.UserDisplayName: Jack Stenson
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-10-22T18:33:17.483Z
Answers.LastActivityDate: 2015-10-22T18:33:17.483Z
Answers.UserDisplayName: Brad Bell
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-11-04T10:51:24.137Z
Answers.LastActivityDate: 2015-11-04T10:51:24.137Z
Answers.UserDisplayName: Jack Stenson
Body: <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>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-10-14T18:07:10.503Z
Comments.Text: 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.
Comments.UserDisplayName: Brad Bell
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-10-15T08:05:57.930Z
Comments.Text: It's a plugin which queries the social_media database and outputs into the template using {% set social = craft.plugin.getAllItems() %}
Comments.UserDisplayName: Jack Stenson
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-10-15T08:18:15.727Z
Comments.Text: 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.
Comments.UserDisplayName: Jack Stenson
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-10-15T15:44:57.270Z
Comments.Text: Can you update the original question with that info?  Impossible to read in a comment.
Comments.UserDisplayName: Brad Bell
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-10-22T11:21:58.727Z
Comments.Text: Is there an single way of doing this without writing a complex query to access all of crafts relative tables?
Comments.UserDisplayName: Jack Stenson
Tags: <database><mysql>
Title: How to UNION Craft blog articles array with Social Media plugin (non craft table)
ThreadText: # 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 username_1. 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;
  
  }
  
  ```
  
  
  ---
  
  