Extracting starred articles from database (postgresql, if it matters)

Hey all, just passing by to ask where the starred articles are located in the database.

I’ve run:
SELECT link FROM ttrss_entries WHERE id IN (SELECT int_id FROM ttrss_user_entries WHERE marked = TRUE);

Which seems to be what I want, but I’m not 100%, so I’d like some confirmation. I can’t check what is actually starred unfortunately, the database is barely usable and I’m trying to recover what I can.

Thanks!

it is, if you’re the only user. otherwise you’d also need to filter by owner_uid.

Thanks bud! :slight_smile:

Have a nice day!

I know what fox has posted is correct (he wrote the software) but I’m trying to understand a discrepancy I’m seeing in my own setup:

ttrssdb=> SELECT COUNT(*) FROM ttrss_user_entries WHERE marked = TRUE;
 count 
-------
  1037
(1 row)

ttrssdb=> SELECT count(link) FROM ttrss_entries WHERE id IN (SELECT int_id FROM ttrss_user_entries WHERE marked = TRUE);
 count 
-------
    74
(1 row)

ttrssdb=>

I can’t think of a reason why these results would be so wildly different. The UI says I have 1031 articles starred (I can only assume a friend of mine who has an account has starred a few things). I’m able to scroll the starred category pretty endlessly so I’m confident there’s more than 74 entries in it.

What am I missing here?

Platform:

  • CentOS 7.7.1908
  • PostgreSQL 9.2.24

Orphaned records? I tried the same queries on my dev machine and got the same results with both queries.

e: The dev environment is using Debian 9 with MariaDB.

I’m not sure how to figure out what that looks like. I can tell by scrolling the starred category that there aren’t 963 articles missing. Those would be the 963 articles that apparently have no link and aren’t in the ttrss_entries table, but are reflected in ttrss_user_entries.

This database has always been on PostgreSQL so there’s no MySQL shenanigans going on. There’s no obvious problem in using the application. Everything seems to work fine. Nothing appears to be missing so this doesn’t seem to be a “real” problem, but it does point to something being fundamentally wrong. I just don’t know what it is.

i’m afraid the problem here is you misreading the schema. ttrss_user_entries is linked to ttrss_entries via ref_id, not int_id. replace that and your second query will work correctly.

e: i guess i should’ve noticed that in your first post. :man_shrugging:

Ah, my bad then. Thanks for the pointer, fox. I was using the query the OP posted. Correcting the int_id to ref_id gives me the same results in both of the queries I asked about.

I guess this means my db is fine. :wink: