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!

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

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.

שימוש באופרטור IN ב-Data Explorer

קהילה נתונים ודיווח

JammyDodger 4 ביוני,‏ 2024,‏ 12:30pm 2

Hmm.

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?

נושא		תגובות	צפיות
Data Explorer: Posts by Category (Including Subcategories) Product Support data-explorer	1	1161	28 בנובמבר,‏ 2021
Query Topics with Category Expert posts? Data & reporting data-explorer , category-experts	2	153	22 באפריל,‏ 2025
Created a topic in a specific category with conditions Data & reporting sql-triggered-badge	0	213	26 בדצמבר,‏ 2022
User-posted code blocks in a category Data & reporting sql-query	3	405	13 במאי,‏ 2024
Category name params support Data & reporting sql-query	1	154	4 ביולי,‏ 2025

שימוש באופרטור IN ב-Data Explorer

נושאים קשורים