How to generate evergreens from articles

This article is based on a customer's idea to take advantage of Joocial's Evergreens and a big set of articles to republish them to social media channels.

Content items usually have a short life. In a common scenario, articles are written with great effort, published in the site front-page and quickly moved to some internal page. In spite of this, some articles have a permanent value and can be featured again, following a social media strategy.

I have several thousand articles (5,000+) I am looking to mark as evergreen so they can be posted in random order to Twitter on a schedule (a few per day, one every few hours).

This is a recipe to create requests and evergreen definitions for all articles in a specific category.

We recommend to test these SQL scripts on a development site and backup your site before apply them in production.

  • A general SQL knowledge to apply SQL statements in the database.
  • Replace the prefix and constants. All these SQL statements are written with the #__ prefix, please replace it with your Joomla database prefix. You can find it in Global Configuration/ Server/ Database Tables Prefix.
  • In this example, we have worked with category 64, replace the number according to your site requirement. If you have more than one category, you can change the condition with the IN operation. Example, catid IN (64, 65, 66, ...)
  • We have also assumed that content is shown in menu item 9999, replace the number according to your site requirement. You can find the menu item in the menu manager, or disable SEF and navigate the front-end site to find it. If you have more than one menu, for instance to access different categories, then you have to perform this procedure for each category to assign the right menu item.
  • Finally, in this example, we have defined the domain as WWW.MYSITE.COM, replace the domain according to your site requirement.

For a fresh start, these lines remove all previous records from the affected tables:

TRUNCATE `#__autotweet_advanced_attrs`;
TRUNCATE `#__autotweet_posts`;
TRUNCATE `#__autotweet_requests`;

Check the published articles to be processed in the category. In this example, category 64.

SELECT * FROM `#__content` where catid = 64 and state = 1

This SQL statement generates initial requests for each article. They are generated as processed records, no message is going to be generated to social channels.

INSERT INTO `#__autotweet_requests` (
    `ref_id`, 
    `plugin`, 
    `publish_up`, 
    `description`, 
    `typeinfo`, 
    `url`, 
    `image_url`, 
    `native_object`, 
    `created`, 
    `created_by`, 
    `modified`, 
    `modified_by`, 
    `checked_out`, 
    `checked_out_time`, 
    `ordering`, 
    `params`, 
    `published`) 
SELECT 
    a.id, 
    'autotweetcontent', 
    NOW(), 
    a.title, 
    1, 
    CONCAT(
        'http://WWW.MYSITE.COM/', 
        'index.php?option=com_content&view=article&id=',
        a.id,
        ':',
        a.alias,
        '&catid=',
        a.catid,
        ':',
        c.alias,
        '&Itemid=',
        9999
    ), 
    '', 
    CONCAT('{"catid":"', a.catid, '","title":"', a.title, '","introtext":"","fulltext":"","metakey":"","language":"', a.language,'","access":"', a.access, '","featured":"', a.featured, '"}'), 
    NOW(), 
    0, 
    NOW(), 
    0, 
    0, 
    '0000-00-00 00:00:00', 
    0, 
    '', 
    1 
FROM `#__content` a, `#__categories` c where a.catid = 64 AND a.state = 1 AND a.catid = c.id;   

This SQL statement generates the evergreen attribute for each inserted request.

INSERT INTO `#__autotweet_advanced_attrs` (
    `client_id`, 
    `option`, 
    `controller`, 
    `task`, 
    `view`, 
    `layout`, 
    `ref_id`, 
    `params`, 
    `request_id`, 
    `evergreentype_id`, 
    `created`, 
    `created_by`, 
    `modified`, 
    `modified_by`, 
    `checked_out`, 
    `checked_out_time`)
SELECT 
    1, 
    'com_content', 
    NULL, 
    'save', 
    NULL, 
    'edit', 
    c.id, 
    '{"description":"","hashtags":"","postthis":"1","evergreen":"3","agenda":[],"unix_mhdmd":"","image":"","image_url":"","channels":null,"channels_text":"","client_id":true,"option":"com_content","controller":null,"task":"save","view":null,"layout":"edit","ref_id":73}', 
    r.id, 
    3, 
    '0000-00-00 00:00:00', 
    0, 
    '0000-00-00 00:00:00', 
    0, 
    0, 
    '0000-00-00 00:00:00'
FROM `#__content` c, `#__autotweet_requests` r
where 
c.id = r.ref_id AND
c.catid = 64 AND c.state = 1;   

SQL statements generate evergreen content items to publish articles as Posts. They simulate the content requests and evergreen attribute. However, there is one limitation: post image.

In the regular Request creation, Post image can be inside the text or in several different fields. When a Post is defined by a user, a careful and thoughtful process detects the image. Alternatively, in Joocial, a user can manually define a different image. This process cannot be replicated at database level.

As a workaround, after you execute these SQL statements, images can be defined specifically for your site with more SQL statements or in Joomla article manager. Alternatively, if there is no image in a Post, Facebook and Twitter can take advantage of Facebook Open Graph Tags or Twitter Cards Tags to detect the right image associated with the Post. Social Tags are supported via Facebook Open Graph Tags plug-in or TwitterCards Graph Tags plug-in. We usually recommend to enable them to enhance your site content definition. In this business case, these plugins help to define the Post image.

Given said that, it is possible to extract the first image in the text based on 'img src' pattern.

This query allows checking what images can be retrieved from articles. It relays on "img src" to detect images in the text.

SELECT 
    CONCAT(
        'http://WWW.MYSITE.COM/', 
        SUBSTRING(
            a.introtext,
            LOCATE(' src', a.introtext) + 6,
            LOCATE('"', a.introtext, LOCATE(' src', a.introtext) + 6) - (LOCATE(' src', a.introtext) + 6)
        )
    )
FROM 
    `#__content` a, `#__categories` c 
WHERE 
    a.catid = 64 AND a.state = 1 AND a.catid = c.id AND
    a.introtext LIKE '% src=%' AND
    a.introtext NOT LIKE '% src="http%';

This is the query to define images, on the initial requests, based on this assumption:

UPDATE
    `#__autotweet_requests` r, `#__content` a, `#__categories` c
SET 
    `image_url` = CONCAT(
        'http://WWW.MYSITE.COM/', 
        SUBSTRING(
            a.introtext,
            LOCATE(' src', a.introtext) + 6,
            LOCATE('"', a.introtext, LOCATE(' src', a.introtext) + 6) - (LOCATE(' src', a.introtext) + 6)
        )
    )
WHERE          
    r.`ref_id` = a.id AND
    `plugin` = 'autotweetcontent' AND
    a.catid = 64 AND a.state = 1 AND a.catid = c.id AND
    a.introtext LIKE '% src%' AND
    a.introtext NOT LIKE '% src="http%'; 

Finally, the content items, requests and attributed are ready to be published according to an Evergreen Strategy. Please, visit the Virtual Manager configuration and define the strategy.

Remember, as you are going to have a big number of evergreen content items, it is recommended to enable the Cron job mode: https://www.extly.com/docs/autotweetng_joocial/installation/cronjob

This article has introduced a method to reshare content with great value from your business. Content that remain fresh and interesting. This method has been applied in Joomla! articles, but it can be easily modified to load content items from other extensions. If the content has never been published, it will be discovered by your current audience. If you have already shared it, you can also apply a couple of rules to modify title with a twist to feature a different angle.