Page 1 of 3

feedbrowser (popularfeeds etc..) issue - resolved

Posted: 16 May 2015, 18:17
by rainclouds
Hi,

First of all thank you to the developers for a great system, it installed perfect, runs perfect and i just really like it.

So everything runs perfect (except one thing) , i added a few users, added for each some feeds (so they have a set of feeds), everything updates also perfectly with update-daemon2 ., however i now create one new user and i would expect (i might be wrong of course) when i click on subscribe feed and then on "more feeds" to see in that popup allot of the feeds the others users subscribed to, so it is easy to quick select a set of feeds.
The popup shows no feeds at all, it is empty.
The tt-rss error log is empty no errors, the update log looks normal and everything is updating correctly (except that popup)

Did i do something wrong ? (probably :) ) . how can i fix this or what do i have to do to get this window populated.

Thank you to anyone for your kind assistance. (and sorry if i somehow somewhere overlooked a doc or something obvious its brandnew to me)

E.
p.s. : It would make it easy for users to quick subscribe to a set of feeds without having to enter all the urls again. ( a feature request would be (i will make a request) to allow admin to create a set of subscriptions which automatically appear for all users ? )

Re: feedbrowser (popularfeeds etc..) issue

Posted: 16 May 2015, 18:23
by fox
it has to be generated, i think update.php has a parameter for this

Re: feedbrowser (popularfeeds etc..) issue

Posted: 16 May 2015, 20:11
by rainclouds
hi,

According to the wiki either use update.php to manually update or the update_daemon which takes care of everything automatically.
Just to be sure i already tried the manual way also. Still no popular feeds. (anything else to try or settings to change ?)
I double-checked all logs no errors mentioned.
i also checked if all the feeds do not have hide from popular feeds setting set but thats not the case, it does look like tt-rss thinks they may not be published ?

using fresh git clone.

thank you for helping.

Re: feedbrowser (popularfeeds etc..) issue

Posted: 17 May 2015, 22:57
by rainclouds
Hi all,

Been doing a little testing debugging on a duplicate system. This is what i found :

The issue (i think of course, as i don't know that well the code and am just new and not good in SQL) is it seems to in the SQL statement in rssfuncs.php.

i have 28 feeds on 3 people 11 feeds are unique. i made a new account to test on the server with no subscriptions.

The original clause
<--- code quote --
# SELECT feed_url, site_url, title, COUNT(id) AS subscribers
# FROM ttrss_feeds WHERE (SELECT COUNT(id) = 0 FROM ttrss_feeds AS tf
# WHERE tf.feed_url = ttrss_feeds.feed_url
# AND (private IS true OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%'))
# GROUP BY feed_url, site_url, title ORDER BY subscribers DESC LIMIT 1000;
--- end code quote --->
Only will return 1 record. (1 feed) nothing else .

However the clause below with a minor change (probably not the way to go ?
<------ code quote
# SELECT feed_url, site_url, title, COUNT(id) AS subscribers
# FROM ttrss_feeds WHERE (SELECT COUNT(id) = 0 FROM ttrss_feeds AS tf
# WHERE ttrss_feeds.feed_url
# AND (private IS true OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%'))
# GROUP BY feed_url, site_url, title ORDER BY subscribers DESC LIMIT 1000;
---- end code quote ---->
returns the correct 11 unique feeds.

Worth looking into and have it correctly fixed in a proper way ?

I dont know that well the code so whoever wrote the original part can maybe assist/sched a light ?
(i can do additional testing of whatever correct fix)

Thank you all for helping out.

Re: feedbrowser (popularfeeds etc..) issue

Posted: 17 May 2015, 23:34
by fox
can you please show what you've changed because i'm really not going to compare two blocks of text manually

Re: feedbrowser (popularfeeds etc..) issue

Posted: 18 May 2015, 00:14
by HeikoAdams

Code: Select all

WHERE ttrss_feeds.feed_url

That's not a good idea but maybe you wanted someting like

Code: Select all

# SELECT feed_url, site_url, title, COUNT(id) AS subscribers
# FROM ttrss_feeds WHERE (SELECT COUNT(id) = 0 FROM ttrss_feeds AS tf
# WHERE (private IS true OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%'))
# GROUP BY feed_url, site_url, title ORDER BY subscribers DESC LIMIT 1000;

Re: feedbrowser (popularfeeds etc..) issue

Posted: 18 May 2015, 00:47
by fox
btw it would be cool if someone rewrote that shit query to something less terrible and maybe not even having a nested select

Re: feedbrowser (popularfeeds etc..) issue

Posted: 18 May 2015, 05:22
by rainclouds
hi,

sorry forgot to quote that one but like heiko mentioned it already.

ill try heiko's suggestion for now then.

I will go catch up on my SQL and see what i can do, meanwhile thank you all for looking at it.


Regards,

Re: feedbrowser (popularfeeds etc..) issue

Posted: 18 May 2015, 14:03
by rainclouds
Hi again,

@ Heiko : Funny thing i ran that query it returns : 0 rows. Seems the only way i get the correct response is my current version of the query :

# WHERE tf.feed_url = ttrss_feeds.feed_url replaced by # WHERE ttrss_feeds.feed_url which i know isn't the right way.
Anyone around with superSQL skills? (ill look around too). It is odd.

Heiko, you have any idea why it fails ?

thanks for looking at it,

Re: feedbrowser (popularfeeds etc..) issue

Posted: 18 May 2015, 15:12
by virgo
Why this query cannot be written:

Code: Select all

SELECT feed_url, site_url, title, COUNT(id) AS subscribers
FROM ttrss_feeds
WHERE
  NOT (private IS true OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%')
GROUP BY feed_url, site_url, title ORDER BY subscribers DESC LIMIT 1000;

?
Am I misunderstanding, what it is supposed to do? From what I understand, it is supposed to exclude private or authenticated feeds. And this should work also (unless I am seriously misunderstanding something).

Re: feedbrowser (popularfeeds etc..) issue

Posted: 18 May 2015, 15:15
by virgo
Ok, I just understood the difference. Original version excludes feeds also, if for same feed some subscriptions are private/password protected and some are not.

Re: feedbrowser (popularfeeds etc..) issue

Posted: 18 May 2015, 15:28
by virgo
rainclouds wrote:# WHERE tf.feed_url = ttrss_feeds.feed_url replaced by # WHERE ttrss_feeds.feed_url which i know isn't the right way.


It seems, that mysql treats non-empty text fields as False when used as Boolean value. At the same time, non-empty varchar fields are treated as true. So the count is always 0 then.

Are existing subscriptions to the feeds private? Because that is the main question.

Re: feedbrowser (popularfeeds etc..) issue

Posted: 18 May 2015, 15:35
by fox
lol mysql never fails to disappoint

i guess it could be changed to != '' or something

Re: feedbrowser (popularfeeds etc..) issue

Posted: 18 May 2015, 15:46
by virgo
fox wrote:i guess it could be changed to != '' or something

Well, the original actually used feed_url to check all feeds with same URL. I was change that rainclouds made, that ended up in this weirdness.
Anyway, I am not sure, if it is necessary to exclude feed, if some subscriptions to same url are private/protected and some not.

Re: feedbrowser (popularfeeds etc..) issue

Posted: 18 May 2015, 16:40
by rainclouds
Hi,

The following does work and does not have the nested where's and returns exactly the desired results.


#SELECT DISTINCT title, feed_url, site_url, COUNT(id) AS subscribers FROM ttrss_feeds
#WHERE (private IS TRUE OR auth_login != '' OR auth_pass != '' OR feed_url LIKE '%:%@%/%')
#GROUP BY title, feed_url, site_url
#ORDER BY subscribers DESC LIMIT 1000;

Might be wrong? but it does return exactly whats needed.

Thanks for everyone's time.
Regards,

@virgo : the change was a test that worked as the original query does not work at all and returns nothing. The above sql query does the correct checks and returns unique(which is what we want) feeds and subscribers count (and checks for private, auth needed etc..to exclude them) without the mess (before or after change). I might oversee something but then i hope an sql expert can tell us. (just in case you wondered i am using clean installed and most recent os/sql versions however as i got curious and wanted to know the what and why's i build quickly (just got a long set of kickstarts) a few different os's flavors and sql flavors and all resulted into failed query))