2

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();
  • 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. – Brad Bell Oct 14 '15 at 18:07
  • It's a plugin which queries the social_media database and outputs into the template using {% set social = craft.plugin.getAllItems() %} – Jack Stenson Oct 15 '15 at 8:05
  • 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 Oct 15 '15 at 8:18
  • Can you update the original question with that info? Impossible to read in a comment. – Brad Bell Oct 15 '15 at 15:44
  • Is there an single way of doing this without writing a complex query to access all of crafts relative tables? – Jack Stenson Oct 22 '15 at 11:21
2

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.

| improve this answer | |
  • Thanks Brad. I will post my results on here so that other developers can reuse it for there projects. Much appreciated! – Jack Stenson Oct 23 '15 at 9:46
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;

}
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.