SPIP API SQL, left join on a custom table

Question

Using SPIP, I'm trying to have a system, which will select a random article every day. I need to store the article of the day for two main reasons:

• ensure that everybody has the same article of the day
• avoid the same article to be picked a second time an other day

---

To be able to store the selected items, I created a generic table:

CREATE TABLE IF NOT EXISTS `spip_random` (
  `id_random` bigint(21) NOT NULL AUTO_INCREMENT,
  `object` varchar(25) NOT NULL,
  `id_object` bigint(21) NOT NULL DEFAULT '0',
  `type` text NOT NULL,
  `date_picked` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id_random`),
  KEY `object` (`object`,`id_object`),
  KEY `id_random` (`id_random`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Here's an bunch of lines which could be stored:

(1, 'article', 54, 'article_of_the_day', '2014-11-29 00:03:54')
(2, 'article', 198, 'article_of_the_day', '2014-11-30 09:32:03')
(3, 'article', 113, 'article_of_the_day', '2014-12-01 14:11:04')
(4, 'article', 3, 'article_of_the_day', '2014-12-02 11:52:28')

The script to select the article of the day works well.

---

Now, I'm facing a problem when I try to retrieve this article in a SPIP loop. I was thinking to use this kind of loop:

<BOUCLE_day(ARTICLES spip_random) {par date_picked} {inverse} {0,1}>
    #ID_ARTICLE
</BOUCLE_day>

As described in SPIP documentation(EN / FR), the jonction should be done between spip_articles and spip_random as I've got these two fields:

• object
• id_object

But nothing is returned.

If I try this simple loop, it displays the right #ID_OBJECT:

<BOUCLE_day(spip_random) {par date_picked} {inverse} {0,1}>
    #ID_OBJECT
</BOUCLE_day>

I simply can't join my custom table (spip_random) and ARTICLES (spip_articles). Am I missing something?

Answer 1

I finally find the answer.

SPIP use the french as its default language, including for functions, keywords and... SQL columns!

I needed to use objet and id_objet to get this code to works (notice the missing c):

CREATE TABLE IF NOT EXISTS `spip_random` (
  `id_random` bigint(21) NOT NULL AUTO_INCREMENT,
  `objet` varchar(25) NOT NULL,
  `id_objet` bigint(21) NOT NULL DEFAULT '0',
  `type` text NOT NULL,
  `date_picked` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id_random`),
  KEY `objet` (`objet`,`id_objet`),
  KEY `id_random` (`id_random`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Answer 2

Why don't you use the publication date ? and then just display the article of the day.