CODEWITHSUNDEEP
phpmysqlsql
checkenginelight
checkenginelight

Reputation: 1146

Select tag with count from multiple table join containing multiple tags for each id

My table has 4 tags linked to the to each id. I want to select the position(rank) of that value(tag_name or tag_id) and the number of times the value(tag_name or tag_id) displays in that same position(rank).

Here's what it would look like in mysql:

> +--------+------------+--------+------+
> | id     | tag_name   | tag_id | rank |
> +--------+------------+--------+------+
> | 2345   | cookie     |    2   |   1  | 
> | 2345   | bar        |    1   |   2  |
> | 2345   | cereal     |    3   |   3  |
> | 2345   | milk       |    4   |   4  |
> | 2346   | cereal     |    3   |   1  |
> | 2346   | milk       |    4   |   2  |
> | 2346   | cookie     |    2   |   3  |
> | 2346   | hot dogs   |    5   |   4  |
> | 2347   | chocolate  |    6   |   1  | 
> | 2347   | bar        |    1   |   2  |
> +--------+------------+--------+------+

Here's what my current code looks like:

SELECT m.*, tr.tag_id, t.tag_name, @rownum:=@rownum + 1 AS rank 
FROM meals AS m
RIGHT JOIN tags_rel AS tr ON tr.meal_id = m.id 
JOIN tags AS t ON tr.tag_id = t.id
JOIN (SELECT @rownum:=0) AS r
ORDER BY m.id DESC

Please keep in mind that I'm actually using a pagination so my ORDER BY actually looks like this:

    ORDER BY id DESC LIMIT $start_from, 12

If I select either tag_name=bar or tag_id=1 and rank=2 I should get the bellow results. Plus I would like the count of rows returned for that value.

> +--------+------------+--------+------+
> | id     | tag_name   | tag_id | rank |
> +--------+------------+--------+------+
> | 2345   | bar        |    1   |   2  |
> | 2347   | bar        |    1   |   2  |  
> +--------+------------+--------+------+

Thanks!

Upvotes: 2

Views: 526

Answers (2)

Christopher
Christopher

Reputation: 704

  SELECT tag_name, tag_id, rank, COUNT(tag_id) AS rankcount 
    FROM table 
GROUP BY tag_id, rank;

results in:

+-----------+--------+------+-----------+
| tag_name  | tag_id | rank | rankcount |
+-----------+--------+------+-----------+
| bar       |      1 |    2 |         2 |
| cookie    |      2 |    1 |         1 |
| cookie    |      2 |    3 |         1 |
| cereal    |      3 |    1 |         1 |
| cereal    |      3 |    3 |         1 |
| milk      |      4 |    2 |         1 |
| milk      |      4 |    4 |         1 |
| hot dogs  |      5 |    4 |         1 |
| chocolate |      6 |    1 |         1 |
+-----------+--------+------+-----------+
9 rows in set (0.00 sec)

If you want your original table enhanced by a count of lines (although that seems to be quite senseless, as every sql result returns the number of rows) you'll have to do a subquery:

SELECT id, tag_name, tag_id as t_id, rank, 
       (SELECT count(tag_id) FROM table WHERE tag_id = t_id) as subqueryCount
  FROM table;

+------+-----------+------+------+---------------+
| id   | tag_name  | t_id | rank | subqueryCount |
+------+-----------+------+------+---------------+
| 2345 | cookie    |    2 |    1 |             2 |
| 2345 | bar       |    1 |    2 |             2 |
| 2345 | cereal    |    3 |    3 |             2 |
| 2345 | milk      |    4 |    4 |             2 |
| 2346 | cereal    |    3 |    1 |             2 |
| 2346 | milk      |    4 |    2 |             2 |
| 2346 | cookie    |    2 |    3 |             2 |
| 2346 | hot dogs  |    5 |    4 |             1 |
| 2347 | chocolate |    6 |    1 |             1 |
| 2347 | bar       |    1 |    2 |             2 |
+------+-----------+------+------+---------------+
10 rows in set (0.00 sec)

Extending that query would result in your desired result, extended by a column where the count of rows is inserted (which is IMO still rather senseless).

SELECT id, tag_name, tag_id as t_id, rank, 
       (SELECT count(tag_id) FROM table WHERE tag_id = t_id) as subqueryCount
  FROM table
 WHERE tag_id = 1;

results in 

+------+----------+------+------+---------------+
| id   | tag_name | t_id | rank | subqueryCount |
+------+----------+------+------+---------------+
| 2345 | bar      |    1 |    2 |             2 |
| 2347 | bar      |    1 |    2 |             2 |
+------+----------+------+------+---------------+
2 rows in set (0.00 sec)

Upvotes: 2

Martin
Martin

Reputation: 1458

how about this:

   SELECT DISTINCT meal_id as MealId, t2name as TagName, t2id as TagId, Rank
FROM 
  (SELECT t1id as t2id, t1name as t2name, rnk as rnk2, count(*) as Rank
    FROM 
    tags_rel AS r, 
    (SELECT m1id, t1id, t1name, 
      @rnk := CASE WHEN @id <> m1id THEN 1 ELSE @rnk + 1 END AS rnk, @id := m1id as idx
      FROM   
        tags_rel AS r, 
        (SELECT @id := 0) idx,
        (SELECT @rnk := 0) rxx,
        (SELECT m.id AS m1id, t.id AS t1id, name AS t1name
          FROM  tags_rel r
          JOIN (meals AS m, tags AS t ) ON ( m.id = r.meal_id AND t.id = r.tag_id )
        ) AS rn
      WHERE r.tag_id = t1id AND r.meal_id = m1id
      ORDER BY m1id, t1name
    ) AS rno
   WHERE r.tag_id = t1id AND r.meal_id = m1id
   group BY t1id, t1name, rnk
  ) as rn2
  join (meals, tags_rel) on (meals.id = tags_rel.meal_id and tags_rel.tag_id = t2id)
ORDER BY meal_id, t2id

which results in:

2345 bar      1 2
2345 cookie   2 1
2345 cereal   3 1
2345 milk     4 2
2346 cookie   2 1
2346 cereal   3 1
2346 milk     4 2
2346 hotdog   5 1
2347 bar      1 2
2347 chocolad 6 1

Upvotes: 1

Related Questions