Feature request: Hide content from RSS for entire category

[judebert]

Okay, I played with this for a while, and I even tried it in phpMyAdmin. As long as I use SUM(ctpass.hide_rss) in the SELECT clause of the SQL statement, it works. If I put it anywhere else (the ON clause of the JOIN, the WHERE clause of the whole statement, etc), MySQL says "Invalid use of group function".

But I can't find any way to add the necessary text to the SELECT. And it doesn't look like serendipity_fetchEntries() supports it, in my reverse-engineering.

Of course, I only want the rows where NO category has been hidden, so I'm making my WHERE clause check for (SUM(ctpass.hide_rss) < 1). That's what's giving me the "Invalid use of group function" message.

[garvinhicking]

Hi!

I believe you need to alias it:

SELECT SUM(ctpass.hide_rss) AS sumcol
FROM ... JOIN ...
WHERE sumcol > 0

?

The fetchEntry call should actually allow to inject all SQL parts: Columnnames, joins, where conditions, order by conditions and group conditions?

HTH,
Garvin

[judebert]

(D*mn, you're fast.)

Yeah, but how does it inject in SELECT?

The query before I get there looks like:

Code: Select all

SELECT 
  e.isdraft AS orderkey,
  e.id,
  blah, blah,

  a.realname AS author,
  a.username AS loginname,
  a.email
FROM
  serendipity_entries AS e
  LEFT JOIN serendipity_authors a
    ON e.authorid = a.authorid
  LEFT JOIN serendipity_entrycat ec
    ON e.id = ec.entryid
  LEFT JOIN serendipity_category c
    ON ec.categoryid = c.categoryid
  LEFT OUTER JOIN serendipity_entryproperties ep_no_frontpage
    ON (e.id = ep_no_frontpage.entryid AND ep_no_frontpage.property = 'ep_no_frontpage')
  LEFT OUTER JOIN serendipity_entryproperties ep_access
    ON (e.id = ep_access.entryid AND ep_access.property = 'ep_access')
WHERE isdraft = 'false' AND e.timestamp <= 1210611300 AND  (ep_access.property IS NULL OR ep_access.value = 'member' OR ep_access.value = 'public' OR (ep_access.value = 'private' AND e.authorid = 1))  AND  (ep_no_frontpage.property IS NULL OR ep_no_frontpage.value != 'true') 

GROUP BY e.id
ORDER BY orderkey DESC, last_modified DESC
LIMIT 1

I don't know what the "LIMIT 1" is about. It must go away eventually, since we can return multiple entries in an RSS feed.

I know I can add conditions and joins; I add to the joins:

Code: Select all

  LEFT OUTER JOIN serendipity_categorytemplates AS ctpass
    ON (ec.categoryid = ctpass.categoryid)

and to the conditions:

Code: Select all

(SUM(ctpass.hide_rss) < 1)

and I get the "Invalid group function" error. I assume it's because the SUM(ctpass.hide_rss) isn't in the SELECT. But when I try to grok serendipity_fetchEntries(), I don't see any way to set it in there.

Oh, wait... would that be the 'addKey' key of the eventData array?

[judebert]

Forgive me for being so dense. It was, in fact, the "addkey" key of the eventData array.

Unfortunately, even though I've aliased it, I'm not allowed to use it!

Code: Select all

SELECT SUM(ctpass.hide_rss) AS cat_hide_rss,
  other stuff
WHERE
  other stuff
  AND (cat_hide_rss = 0)

Produces "#1054 - Unknown column 'cat_hide_rss' in 'where clause'".

Arrgh!

[judebert]

Okay, I've found it. For this, I need to use "HAVING (cat_hide_rss < 1)" after the GROUP BY.

Now I'll just figure out how to do that...

[judebert]

As far as I can tell, there is no way to include a HAVING or GROUP_BY in the serendipity_fetchEntries() call. It might be nice to include those things at some point; if we do, I'll be happy to rewrite my query.

Meanwhile, I solved it with an uncorrelated subquery.

The file has been checked into CVS, and so should be available within the next 24 hours.

[garvinhicking]

Hi!

True, GROUP BY cannot be overriden by plugins.

But I've just committed a patch that should allow us to do so in the future?

Meanwhile, I solved it with an uncorrelated subquery.

Subqueries might impose trouble for postgresql and sqlite compatibility...?

Regards,
Garvin

[judebert]

Oh, you're kidding. What do they use if not subqueries? Separate queries? I don't want to hit the database twice, but I will if I have to.

[sonichouse]

Oh, you're kidding. What do they use if not subqueries? Separate queries? I don't want to hit the database twice, but I will if I have to.

They do support inline subqueries.

I ran into this and posted some details on my blog

<edit>
URL now works on port 80
</edit>

[judebert]

Love to look, but my connection only supports port 80. Got it mirrored elsewhere?

[sonichouse]

Love to look, but my connection only supports port 80. Got it mirrored elsewhere?

extract ...

I was having a problem with a query in SQLite ...

SELECT 'Albums Played' AS Item, count(DISTINCT Album) AS DESC FROM songs WHERE play_count>0

This was failing in SQLite2 with an error near distinct.

The revised SQL is ...

SELECT 'Albums Played' AS Item, count(Album) AS DESC FROM (SELECT DISTINCT Album FROM songs WHERE play_count>0)