mysql order by from a different table breaks ranking

Question

I have a query that grabs tags for a list of articles and limits it to under 5 tags per article. This works pretty well.

Here's the query:

SET @rank=null, @val=null;
SELECT * FROM (
SELECT r.article_id, c.`category_name`, c.`category_id`, 
@rank := IF( @val = r.article_id, @rank +1, 1 ) AS rank, 
@val := r.article_id
FROM  `article_category_reference` r
INNER JOIN  `articles_categorys` c ON c.category_id = r.category_id
WHERE r.article_id
IN ( 1,2 )
ORDER BY r.`article_id` ASC
) AS a
WHERE rank < 5

However, I have specific tags I want to show up first which have a column of "show_first" 0/1 and I want them included first and be counted.

I've tried doing:

ORDER BY CASE WHEN (c.`show_first` = 1) THEN 0 ELSE 1 END, r.`article_id` ASC

Which breaks the rank counting, so all tags end up showing.

Any pointers would be appreciated.

The tables:

CREATE TABLE `article_category_reference` (
  `ref_id` int(11) NOT NULL,
  `article_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for table `article_category_reference`
--
ALTER TABLE `article_category_reference`
  ADD PRIMARY KEY (`ref_id`),
  ADD KEY `category_id` (`category_id`),
  ADD KEY `article_id` (`article_id`);

CREATE TABLE `articles_categorys` (
  `category_id` int(11) NOT NULL,
  `category_name` varchar(32) CHARACTER SET utf8 NOT NULL,
  `quick_nav` tinyint(1) NOT NULL DEFAULT '0',
  `is_genre` tinyint(1) NOT NULL DEFAULT '0',
  `show_first` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Indexes for table `articles_categorys`
--
ALTER TABLE `articles_categorys`
  ADD PRIMARY KEY (`category_id`);

-- And some data:

INSERT INTO `articles_categorys` (`category_id`, `category_name`, `quick_nav`, `is_genre`, `show_first`) VALUES
(1, 'one', 1, 0, 0),
(2, 'two', 1, 0, 0),
(3, 'three', 1, 0, 0),
(4, 'four', 0, 0, 0),
(5, 'five', 0, 0, 0),
(6, 'six', 0, 0, 0),
(7, 'seven', 0, 0, 1),
(8, 'eight', 0, 0, 1);

INSERT INTO `article_category_reference` (`ref_id`, `article_id`, `category_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4),
(5, 1, 5),
(6, 1, 6),
(7, 1, 7),
(8, 1, 8),
(9, 2, 1),
(10, 2, 2),
(11, 2, 3),
(12, 2, 4),
(13, 2, 5),
(14, 2, 6),
(15, 2, 7),
(16, 2, 8);

Fiddle of how it works: http://sqlfiddle.com/#!9/1de99/1/0

Fiddle of it not working with me wanting some to always show first: http://sqlfiddle.com/#!9/0d36b7/1 (adding in a second group seems to break the ranking system)

Answer 1

Your issue is not in the where condition, it's about the ranking that you are creating.

As you will see in my answer, I have created one inner query which will get that record in specific order and apply accurate ranking.

If you check your inner query, it's shows that all rows have the same rank and that is due to that ordering issue.

So I have added the ORDER BY clause in innermost query, and then filtered out records which have rank1 less than 5.

SET @rank1=null, @val=null;

SELECT * FROM (
    SELECT a.article_id, a.`category_name`, a.`category_id`, 
           @rank1 := IF( @val = a.article_id, @rank1 +1, 1 ) AS rank1, 
           @val := a.article_id
    FROM (
        SELECT r.article_id, c.`category_name`, c.`category_id`
        FROM  `article_category_reference` r
        INNER JOIN  `articles_categorys` c ON c.category_id = r.category_id
        GROUP BY r.article_id, c.`category_name`, c.`category_id`
        ORDER BY r.`article_id`,CASE WHEN (c.`show_first` = 1) THEN 0 ELSE 1 END ASC
    ) AS a
) Z
WHERE Z.rank1 < 5;

You can check here.