CODEWITHSUNDEEP
mysql
Farid Rn
Farid Rn

Reputation: 3207

Select latest updated categories based on their items

I have a MySQL database and there's a categories table like this:

id  name            parent
--------------------------
1   News            0
2   Analysis        0
3   Europe          1
4   Middle East     1
5   Asia            1
6   Americas        1
7   Commentaries    2
8   Interviews      2
9   Articles        2
10  Reports         2

And an items table like this:

id  created                 catid   title
---------------------------------------------
1   2013-08-12 20:15:00     3       Foo
2   2013-08-12 19:15:00     3       Bar
3   2013-08-12 18:15:00     4       Foobar
4   2013-08-12 17:15:00     4       Barfoor
5   2013-08-12 16:15:00     8       Boofar
6   2013-08-12 15:15:00     9       Farfar
7   2013-08-11 16:45:00     10      Farfarbar
8   2013-08-11 16:15:00     5       Foofoobar
10  2013-08-10 16:15:00     7       Foobarbar

What I want is to list to categories which are children of a specified parent and have latest items in them. For example if I want latest updated categories of News (catid=1) section, the result would be:

3   Europe
4   Middle East
5   Asia

Note that the results are ordered by their last update time.

Please consider that due to large amount of records, performance of the query is so important.

Upvotes: 0

Views: 112

Answers (3)

Marty McVry
Marty McVry

Reputation: 2856

A join works pretty fast. Then use a group by to enable the aggregate MAX()-function to sort the output.

In the WHERE-clause you can choose the parent-id that you want to search for.

SELECT c.id, c.name
FROM categories c
INNER JOIN items i
ON c.id = i.catid
WHERE c.parent = 1
GROUP BY c.id
ORDER BY MAX(i.created) DESC

SQL-Fiddle

EDIT

In the event of only single nestings, you can change the query as follows:

SELECT c.id, c.name
FROM categories c
INNER JOIN items i
ON c.id = i.catid
WHERE c.parent = 1
OR c.parent IN (SELECT id FROM categories WHERE c.parent = 1)
GROUP BY c.id
ORDER BY MAX(i.created) DESC

SQL-Fiddle

If you need more nestings, you'd need to create stored procedures. More information about this can be found here.

Upvotes: 2

Naveen Kumar Alone
Naveen Kumar Alone

Reputation: 7678

Here is the SQLFiddle

SELECT i.catid, c.name FROM items i 
  JOIN categories c ON i.catid=c.id 
  WHERE c.parent=1
  GROUP BY i.catid
  ORDER BY MAX(i.created) DESC;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You seem to only want the children of a particular category. It seems that you are asking which rows in categories have a parent of 1 and have rows in the items table:

select c.id, c.name
from categories c
where c.parent = 1 and
      exists (select 1 from items i where i.catid = c.id);

EDIT:

I have no idea what you mean by "latest" items. But you can check for the 10 most recent in the items table by doing:

select c.id, c.name
from categories c
where c.parent = 1 and
      exists (select 1
              from (select i.*
                    from items i
                    order by created desc
                    limit 10
                   ) i
              where i.catid = c.id)
             );

Or using a join:

select c.id, c.name
from categories c join
     (select i.*
      from items i
      order by created desc
      limit 10
     ) i10
     on i.catid = c.id
where c.parent = 1
group by c.id, c.name
order by max(created) desc;

Upvotes: 1

Related Questions