Using the IN operator in Data Explorer

data & reporting
1

Hi all, I noticed a strange behavior when editing and running a query in Data Explorer. I am trying to use the WHERE and IN operators to select only posts from topics in certain categories:

WHERE
    t.category_id IN (420, 421, 426)

However, I am not sure that the WHERE condition works as expected: if I run the query for periods antecedent the creation of the categories in question, it simply lists all topics, regardless of the category. Additionally, the IN operator does not change colour, and I wonder if Data Explorer recognizes is as an operator:

The image shows a few lines of SQL code, performing a JOIN operation on a table and filtering the results based on category IDs being part of a specific set of values. (Captioned by AI)

Any idea what is going on? Huge thanks!

2

Hmm. :thinking:

I used it in a very similar query just last night and it worked as expected for me.

Could you share your full query so we can see if there might be something else at play?

1 Like
3

Sure, here goes. The query is used to retrieve the info necessary to generate a custom log file that can be digested by Gource. See: Custom Log Format · acaudwell/Gource Wiki · GitHub

-- This query retrieves a list of the latest posts,
-- along with associated user information and topic details.
-- The result is sorted by creation date in ascending order.

-- The selected fields are as follows:
-- timestamp: An ISO 8601 or unix timestamp of when the post was created.
-- username: The username of the user who created the post.
-- type: A hardcoded value 'A' indicating the type of action (in this case, 'Added').
-- topic: The ID of the topic to which the post belongs.
-- post_number: The unique number of the post within its topic.
-- reply_to_post_number: The post number to which this post is a reply (if any).
-- raw: The content of the post, limited to the first 30 characters.
-- category_id: The ID of the category to which the topic belongs.

SELECT 
    p.created_at as timestamp, 
    u.username, 
    'A' as type, 
    p.topic_id as topic, 
    p.post_number as post_number, 
    p.reply_to_post_number as reply_to_post_number, 
    SUBSTRING(p.raw, 1, 30) AS raw,
    t.category_id
FROM
    posts p
JOIN
    users u ON p.user_id = u.id
JOIN
    topics t ON p.topic_id = t.id
WHERE
    t.category_id IN (420, 421, 426)
    AND t.created_at > '2022-01-01'
ORDER BY
    p.created_at ASC
LIMIT 1000
1 Like
4

It all seems in order.

When I run it against our three new docs categories (created around 10 days ago) using the same AND t.created_at > '2022-01-01' I’m not seeing any data from outside of those categories being pulled in (lot of small action posts though, which you may want to filter out depending on what your use case is). Running it without the AND t.created_at > '2022-01-01' entirely also only pulls in data from those categories.

2 Likes