Selecting posts where category is like multiple category IDs using REGEXP

Question

I have a string of category IDs that look like this 1;2;3;4;.

Now I want to fetch all the posts that contain each of these variables in the category column of the posts table.

The category column also have the content like this 1;2;3; depending on what categories have been attached to the post.

How can I set up a PDO query that checks if any of the value from the primary string is found within the category column in the post table?

I am looking for something which would look like category LIKE = IN (2;3;4;), also it has to work with double/triple ... digits like this: 2;44;23;.

Example:

Post 1: 1;2;
Post 2: 3;
Post 3: 1;

I use the string 1; to fetch from the post table, and the result I want back is Post 1 and Post 3 because they both contain 1;.

Is refactoring your table structure an option? Storing delimited lists in fields should (almost) always be avoided. — Uueerdo, May 23 '16 at 18:52
It is possible, but what is usually faster? Two separate queries or one query using REGEXP? Im trying to avoid using to many tables. — Gjert, May 23 '16 at 18:53
REGEXP and LIKE are some of the slowest operators, better to JOIN the related tables in one query. — Uueerdo, May 23 '16 at 18:53
How would you write the JOIN in this case to avoid double JOINS? — Gjert, May 23 '16 at 18:56
What do you mean by double joins? Getting the same post multiple times if it matches multiple categories? — Uueerdo, May 23 '16 at 19:00

Uueerdo · Accepted Answer · 2016-05-23T19:31:19.350

1

Best option is to refactor the table into a posts table (without the categories list field), and posts_categories table (with post_id, and category_id fields).

Then use a simple query like this:

SELECT DISTINCT p.*
FROM posts_categories AS pc
INNER JOIN posts AS p ON pc.post_id = p.post_id
WHERE pc.category_id IN ([your list of values])
;

Unfortunately, most database libraries do not support arbitrary lists of parameters, so you may need to generate the exact series of ?,?,?,... in code; but I am unfamiliar with pdo and am only marginally acquainted with php.

Edit: Tweaked query to only show data from posts, and only once per post.

If you want the list of categories as well....

SELECT DISTINCT p.*
     , GROUP_CONCAT(assoc_pc.category_id SEPARATOR ';') AS catList
FROM posts_categories AS filtering_pc
INNER JOIN posts AS p ON filtering_pc.post_id = p.post_id
INNER JOIN posts_categories AS assoc_pc ON p.post_id = assoc_pc.post_id
WHERE filtering_pc.category_id IN ([your list of values])
GROUP BY p.post_id
;

GROUP_CONCAT is MySQL specific; if you want a bit more platform independence, you may want to SELECT p.*, assoc_pc.caetgory_id and just ORDER BY p.post_id and build the catList in code when processing the ungrouped results.

Edit: fixed typo, incorrect alias, in second query example.

edited May 23 '16 at 19:31

answered May 23 '16 at 18:59

Uueerdo

15,723
1
16
21

I had a problem with this query. Is there a way for me to fetch ALL posts if no category string is given? – Gjert May 23 '16 at 19:21
Not with the same query; you can take out the `WHERE` completely, 'post_categories AS filtering_pc INNER JOIN` and the corresponding `ON filtering_oc.post_id = p.post_id` to get a result that looks like the original table. ....and I noticed a typo in the join condition I pointed out, fixing above now. – Uueerdo May 23 '16 at 19:30
I came across a weird problem, and I haven't been able to verify it yet... It seems like the result is being doubled in my query. Is this somehow incorrect? `$sql = "SELECT DISTINCT COUNT(n.news_id) FROM news_attributes na INNER JOIN news n ON na.attribute_news = n.news_id LEFT JOIN languages l ON n.news_lang = l.language_id WHERE ((l.language_val = :language) OR (n.news_lang_all IS NOT NULL)) AND n.news_deleted IS NULL AND n.news_published = 1";` – Gjert May 23 '16 at 22:31
@GjertIngarGjersund not sure how that relates to this one, but I am guessing you want `COUNT(DISTINCT n.news_id)` as `DISTINCT COUNT(n.news_id)` makes little sense without a `GROUP BY` (_and even when it does make sense, is still a very unlikely intent_). – Uueerdo May 24 '16 at 00:21

Selecting posts where category is like multiple category IDs using REGEXP

1 Answers1