commandos — 2010-07-03T08:24:11-04:00 — #1
Hi guys ,
I dont know if anyone can help this .. even if it's not 100% joomla ...
i want to know if it's possible to order by then group by in the same query ?
i have 2 tables (jos_content and jos_categories) with a complex sql :
$query = 'SELECT a.*,' .
' CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug,'.
' CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug'.
' FROM #__content AS a' .
' INNER JOIN #__categories AS cc ON cc.id = a.catid' .
' INNER JOIN #__sections AS s ON s.id = a.sectionid' .
' WHERE a.state = 1 ' .
($noauth ? ' AND a.access <= ' .(int) $aid. ' AND cc.access <= ' .(int) $aid. ' AND s.access <= ' .(int) $aid : '').
' AND (a.publish_up = '.$db->Quote($nullDate).' OR a.publish_up <= '.$db->Quote($now).' ) ' .
' AND (a.publish_down = '.$db->Quote($nullDate).' OR a.publish_down >= '.$db->Quote($now).' )' .
' AND cc.section = '.(int) $secid .
' AND cc.published = 1' .
' AND s.published = 1' .
' ORDER BY a.catid ASC , a.created DESC' ;
// ' GROUP BY a.catid' ;
i want to get a list of articles grouped by category id (sorted by last date)
i also tried to use the keyword "DISTINCT (a.catid)" but it dident work , i think distinct will compare the whole rows instead of a specific column .
any idea how i could accomplish this ?
r937 — 2010-07-03T16:10:31-04:00 — #2
you have three tables there, #content, #categories, and #__sections
what are the one-to-many relationships between these tables?
and can i assume that "one article per category" means the articles are in the #__content table?
finally, may i ask you to echo (print out) the SELECT statement after php variable values have been substituted?
guelphdad — 2010-07-03T14:30:21-04:00 — #3
list of articles grouped by category id (sorted by last date)
So you want one article per category, the most recent article per category only?
commandos — 2010-07-03T09:29:11-04:00 — #4
nop ... i meant group by
guelphdad — 2010-07-03T09:22:38-04:00 — #5
I think a lot of the time when people say GROUPED BY they mean order by.
commandos — 2010-07-03T09:23:50-04:00 — #6
Thank's r937 !
ok here is what I'm trying to do ...
get the list of all articles from a specific section and then only get 1 article / category (latest article published)
currently with this sql I'm getting the articles but getting more than 1 article / category .
r937 — 2010-07-03T09:14:16-04:00 — #7
no, it doesn't work that way
GROUP BY executes first, and then ORDER BY sorts the results of the grouping
i'm not sure what "grouped by" means in this context
you are correct
you didn't really explain what you are actually trying to do