SELECT * FROM `txp_category` ORDER BY... latest entry in categoy?

mhulse · 2008-03-04 05:51:51

Hi,

I wanted to update a plugin I wrote… Long story short, I would like to sort the output of categories by the entry date of the latest article posting in said category.

I am kinda a noob when it comes to MySql… It would be great if someone could give me some pseudo-code to learn from… I can easily select * from `txp_category`, but I am not sure how to ORDER BY TOP 1 `textpattern`.LastMod?

It would be great to get some tips… I kinda need a push in the right direction.

Many thanks in advance!
Cheers,
Micky

Last edited by mhulse (2008-03-04 05:52:52)

Mary · 2008-03-04 06:49:56

You’ll need to do a table join. MySQL doesn’t understand top. You’ll have to use group by. You should be able to Google and get more details on usage of these.

mhulse · 2008-03-04 07:04:04

Hi Mary! Thanks for the quick reply, I really appreciate your pro help on this. :)

Good tips, and I think I have something that works, although I have not fully tested:

SELECT c.name, c.title, t.LastMod 
FROM `txp_category` AS c, `textpattern` AS t
WHERE (c.name = t.Category1 OR c.name = t.Category2) 
AND c.type = "article" 
GROUP BY c.name
ORDER BY t.LastMod DESC

The above appears to be working, although, I feel like the ORDER BY t.LastMod part was too easy of a solution… I am sure I am overlooking something here.

I will report back my findings… currently I am having problems with <txp:modified /> not returning any data. :(

Thanks again Mary!
Cheers,
Micky

Last edited by mhulse (2008-03-04 07:05:52)

mhulse · 2008-03-04 07:42:16

Hehe, ok, so I knew it was not going to be that easy… The articles appear as the first ones I posted for that category… not the last modified.

I will be back once I find a solution. ;)

Thanks for the tips Mary, much appreciated. :)

Cheers,
Micky

Last edited by mhulse (2008-03-04 08:07:48)

mhulse · 2008-03-05 08:56:02

Ahhh, I think I got it!

SELECT c.name, t.Category1, t.LastMod, t.Title
FROM `txp_category` AS c, (SELECT * FROM `textpattern` ORDER
BY LastMod DESC) AS t
WHERE (c.name = t.Category1 OR c.name = t.Category2) 
AND c.type = "article" 
GROUP BY c.name 
ORDER BY t.LastMod DESC

I have not fully tested, but it appears to be sorting the categories based on the last mod time of most recently modified article for that category… Niiiice… Although, I need to test more.

Any feedback for the query? Could it be more optimized? What about security?

Are there a few MySql utility/security functions that Textpattern provides in the core?

Thanks!
Micky

jm · 2008-03-05 09:31:08

mhulse wrote:

Are there a few MySql utility/security functions that Textpattern provides in the core?

/textpattern/lib/txplib_db.php – safe_query(), safe_pfx.

Last edited by jm (2008-03-05 09:50:56)

jsoo · 2008-03-05 12:17:18

I’m an SQL noob myself, so take this with a large grain of NaCl, but I think I would refine the subquery a bit. I don’t think the ORDER BY gets you anything, and I wouldn’t SELECT *, because textpattern can be a very large table.

Edit: For that matter, I don’t think you need the subquery at all. Just:

SELECT c.name, t.Category1, t.LastMod, t.Title
FROM `txp_category` AS c, `textpattern` AS t

Also not sure the GROUP BY gets you anything, nor do you need to include t.Category1 in your SELECT.

Last edited by jsoo (2008-03-05 12:24:11)

mhulse · 2008-03-05 21:03:18

Thanks all! Great info. :)

Thanks for the tips jsoo… That makes a lot of sense… I wish I were better at all this — Pretty fun stuff though! :)

I definitely will remove some of the items from my first select. I have been using PHPmyAdmin to run these queries, and I just wanted to see the tables in the results… Also, great point about the SELECT * in the sub-query.

This will be a modification for a library plugin/function that I wrote which returns all non-empty article, image, file, or link categories.

Thanks again!!! You folks have been super helpful! I hope I can pay ya’ll back one of these days. :)

mhulse · 2008-03-20 23:59:55

Hey all!

With the help of a guru MySql programmer, here is my updated MySql statement:

SELECT c.name
     , c.title
     , e.Category1
     , e.Category2
     , e.Section
     , e.ID
     , e.LastMod
     , e.Title 
  FROM category AS c
LEFT OUTER
  JOIN entries AS e
    ON c.name IN ( e.Category1 , e.Category2 ) 
   AND e.Section = 'blog'
   AND e.LastMod = 
       ( SELECT MAX(LastMod)
           FROM entries
          WHERE c.name IN ( Category1 , Category2 ) 
            AND Section = 'blog' )
 WHERE c.type = 'article' 
ORDER 
    BY e.LastMod DESC

It is working perfectly now, but for some reason I am returning “root”, which has all NULL values… Anyone have any tips on how I can best ignore root? Should I say something like AND c.Name NOT 'root', or maybe I should look into using NOT ISNULL()?

Thanks!
Cheers,
Micky

ruud · 2008-03-21 12:29:50

Try replacing WHERE c.type = 'article' with WHERE c.type = 'article' AND c.Name != 'root'

mhulse · 2008-03-26 05:42:41

Perfect! A billion thanks for the tip Ruud! :)

SELECT c.name
     , c.title
     , e.Category1
     , e.Category2
     , e.Section
     , e.ID
     , e.LastMod
     , e.Title 
  FROM txp_category AS c
LEFT OUTER
  JOIN textpattern AS e
    ON c.name IN ( e.Category1 , e.Category2 ) 
   AND e.Section = 'blog'
   AND e.LastMod = 
       ( SELECT MAX(LastMod)
           FROM textpattern
          WHERE c.name IN ( Category1 , Category2 ) 
            AND Section = 'blog' )
 WHERE c.type = 'article' AND c.Name != 'root'
ORDER 
    BY e.LastMod DESC

Onward! :D

Have a great night/day!
Cheers,
Micky

Textpattern CMS

Textpattern CMS support forum

#1 2008-03-04 05:51:51

SELECT * FROM `txp_category` ORDER BY... latest entry in categoy?

#2 2008-03-04 06:49:56

Re: SELECT * FROM `txp_category` ORDER BY... latest entry in categoy?

#3 2008-03-04 07:04:04

Re: SELECT * FROM `txp_category` ORDER BY... latest entry in categoy?

#4 2008-03-04 07:42:16

Re: SELECT * FROM `txp_category` ORDER BY... latest entry in categoy?

#5 2008-03-05 08:56:02

Re: SELECT * FROM `txp_category` ORDER BY... latest entry in categoy?

#6 2008-03-05 09:31:08

Re: SELECT * FROM `txp_category` ORDER BY... latest entry in categoy?

#7 2008-03-05 12:17:18

Re: SELECT * FROM `txp_category` ORDER BY... latest entry in categoy?

#8 2008-03-05 21:03:18

Re: SELECT * FROM `txp_category` ORDER BY... latest entry in categoy?

#9 2008-03-20 23:59:55

Re: SELECT * FROM `txp_category` ORDER BY... latest entry in categoy?

#10 2008-03-21 12:29:50

Re: SELECT * FROM `txp_category` ORDER BY... latest entry in categoy?

#11 2008-03-26 05:42:41

Re: SELECT * FROM `txp_category` ORDER BY... latest entry in categoy?

Board footer