Query specific posts by tag

Developer help
1

I am trying to create a query that picks up all articles that do not have a specific tag i.e. #fr

I am currently working on this query, it is not working as well as I would like. As a result I get all the articles even the French ones, but in tag_name is removed #fr:

SELECT pm.meta_description,p.feature_image, GROUP_CONCAT(t.name) AS tag_name, p.html, p.title, p.id 
FROM posts_meta AS pm 
  JOIN posts AS p ON pm.post_id=p.id 
  JOIN posts_tags AS pt ON pt.post_id=p.id 
  JOIN tags AS t ON t.id=pt.tag_id 
    WHERE type="post" AND t.name != "#fr"
GROUP BY p.id
2

What’s the use-case here and what’s causing you to work at the database level?

3

I have to edit and/or translate hundreds of articles, instead of proceeding manually I will use APIs, so I think the only way is to proceed like this

4

Can you use Ghost’s API rather than dropping down to the database layer? There are examples of using the API for bulk selects/edits here https://github.com/TryGhost/api-demos

5

It is certainly an interesting solution that I will not hesitate to study. At the moment, however, I would like to be able to solve my problem since I already have the script ready to do what I need to

6

If anyone is interested I was able to fix the problem, this is a simplified version of the query that does the job:

 SELECT p.id, p.title 
 FROM   posts AS p
 WHERE  NOT EXISTS 
        (   SELECT  1 
            FROM    posts_tags AS pt 
            WHERE   pt.post_id= p.id
            AND     pt.tag_id= "id_#fr_tag"
        );
7

Hi @giacomosilli, I wanted to make sure it’s clear here that editing the DB is very much not recommended.

The Ghost API does a lot more than just manage DB records, there are events that fire other operations, internal caches that are maintained and a lot more. If you’re editing the DB directly, you’d definitely need to restart Ghost afterwards to make sure things are in sync as they can possibly be.

The Ghost API is always the correct way to interact with your Ghost data - it’s literally what its there for.

1 Like
8

Thanks for the further clarification.

Maybe I haven’t been very clear.
With my query I just want to extrapolate the data, modify it, and then using the Ghost API I make the changes and insertions.

I don’t modify the database directly.

However I’m also interested in using the API for direct extrapolation, is there a ready-to-use api-demo for extrapolating all posts with a specific tag? Could you help me?

Thank you

9

Yes of course, again this is exactly what the API is for!

Here’s a working demo URL on the content API:

https://demo.ghost.io/ghost/api/content/posts/?key=22444f78447824223cefc48062&include=tags&filter=tags%3Agetting-started

If you’re using the SDK it’d be

posts.browse({include: 'tags', filter: 'tags:getting-started'})

If you’re using the admin API instead of content, tags are included by default so you don’t need to add that directly.

10

Thank you,
I use the Admin API for updates and entries.

At the moment I didn’t understand how to exclude all posts that have a specific tag, in my case “#fr”

I could also use the Content API is not a problem.

EDIT:
This works:
.browse({ limit: 5, include: 'tags', filter: 'tags:-hash-fr' })