Please login or register.

Login with username, password and session length
Advanced search  

News:

Latest sNews - sNews 1.7 - with its own forums - for discussion and user mods.

Author Topic: Advanced SQL problems  (Read 4782 times)

centered

  • Guest
Advanced SQL problems
« on: February 13, 2008, 01:06:48 am »

I am looking for a way to do this:
Code: [Select]
id -- name -- seftitle -- subcatSEF -- subnum_rows -- art_num -- art_title -- art_seftitle
1 -- catName -- catname -- NULL -- NULL -- 2 -- My article -- my_article
1 -- catName -- catname -- NULL -- NULL -- 2 -- My second article -- my_second_article
1 -- subcatName -- catname -- subcatname -- 1 -- 1 -- My subarticle -- my_subarticle

I have everything but the article title and article seftitle

Code
Code: [Select]
select cat.id     
, cat.name
, cat.seftitle
, null as subcatSEF
, null as subnum_rows
, (select count(*) from articles where category = cat.id) as art_num
from categories as cat
where cat.subcat = 0
union all
select subcat.id     
, subcat.name
, cat.seftitle
, subcat.seftitle as subcatSEF
, (select count(*) from categories where subcat = cat.id) as subnum_rows
, (select count(*) from articles where category = subcat.id) as art_num
from categories as cat
inner
join categories as subcat
on cat.id = subcat.subcat
where cat.subcat = 0
order by id;
Logged

Joost

  • Guest
Re: Advanced SQL problems
« Reply #1 on: February 13, 2008, 10:12:57 am »

@jason

Unlike you I am not good at multi targeting. But maybe this helps:
Using the following query I can retrieve literal all fields from a category and the article.  It uses an index and a primary key. So only two rows are scrolled.
Maybe it is possible to extend the query.
Keep in mind that when a wrong index is triggered, you might be better of using two queries.

SELECT articles.id AS ID, title, text, categories.id AS catID, name, categories.seftitle AS catSEF etc.
FROM articles, categories
WHERE articles.seftitle = "sef-page-title-6" AND articles.published = 1
AND category = categories.id
Logged

Joost

  • Guest
Re: Advanced SQL problems
« Reply #2 on: March 11, 2008, 01:50:25 am »

In sNews there is not a consistent relationship between articles and categories as cat (defined by Jason).

There is a relationship between articles and categories in general (column category = categories id). If a category is a subcategory, is verified only after collecting data from the corresponding category (subcat =  ?).

In pseudo code:

article (category = id) category (subcat = id) category

Query (the red corresponds with the pseudo code)

SELECT -- something that is not important here
FROM articles AS a
LEFT OUTER JOIN categories AS c ON  category = c.id
LEFT OUTER JOIN categories AS x ON c.subcat =  x.id

This is a outer join, so any article, is listed with or without one or two categories.
Bottomline: You never know if a category is a subcategory or not.

The output:
articlecategory (c)category (x)
articlecategory (c)null
articlenullnull

- Row 1 represents an article, a subcategory and its main category
- Row 2 represents an article and its main category
- Row 3 Is probably a page. I forgot to add a WHERE clause (position = 1)  ;D

Here is how to use it  for function archive. One query only. Works great with mysql 5+.
But I am not sure whether mysql 4 is well optimized for handling huge numbers, so I built in a limitation for 900 articles.

Code: [Select]
<?php
// ARCHIVE
function archive($start =0$size =900) {
echo '<h2>'.l('archive').'</h2>';
$query 'SELECT id FROM '.db('prefix').'articles WHERE position = 1 AND published = 1 ORDER BY date DESC LIMIT '."$start$size";
$result mysql_query($query);
$count mysql_num_rows($result);
if ( $count ===0) {echo '<li>'.l('no_articles').'</li>';}
else {
while ($r mysql_fetch_array($result)) { $Or_id[] = 'a.id ='.$r['id'];}
$Or_id implode(' OR ',$Or_id);
$query 'SELECT
title,
a.seftitle AS asef,
a.date AS date,
c.name AS name,
c.seftitle AS csef,
x.name AS xname,
x.seftitle AS xsef
FROM articles AS a
LEFT OUTER JOIN categories as c ON  category = c.id
LEFT OUTER JOIN categories as x ON c.subcat =  x.id
WHERE '
.$Or_id;
$result mysql_query($query);
$month_names explode(', 'l('month_names'));
$dotl('divider');
echo'<p>';
while ($r mysql_fetch_array($result)) {
$year substr($r['date'], 04);
$month substr($r['date'], 52) -1;
$month_name = (substr($month01) == 0) ? $month_names[substr($month11)] : $month_names[$month];
if ($last <> $year.$month) {echo '<strong>'.$month_name.', '.$year.'</strong><br />';}
$last $year.$month;
$link = isset($r['xsef']) ? $r['xsef'].'/'.$r['csef'] : $r['csef'];
echo  $dot.'<a href="'.db('website').$link.'/'.$r['asef'].'/">'.$r['title'].' ('.$r['name'].')</a><br />';
}}
echo'</p>';
}

?>
« Last Edit: March 11, 2008, 06:34:19 am by Joost »
Logged

centered

  • Guest
Re: Advanced SQL problems
« Reply #3 on: March 11, 2008, 11:39:28 am »

Joost,

Your code is very interesting, I will try it out soon as I get a chance to.  The first query, i still think we can merge with the second one... just not sure how atm

Quote
In sNews there is not a consistent relationship between articles and categories as cat (defined by Jason).

There is a relationship between articles and categories in general (column category = categories id). If a category is a subcategory, is verified only after collecting data from the corresponding category (subcat =  ?).
Your first two lines conflict with each other (as the first line is wrong). As an article or page will always need a category to be based upon. The articles.category(table.column) will never be NULL so it needs a numermical value. The articles.category cannot base whether a category is a subcat or not, and yes you are correct that a subcat is only a subcat after being verified by the cat first.

Also categories as cat is just an alias, I could name categories AS aDumbPOS.  As long as it corresponds with the column like aDumbPOS.name, aDumbPOS.seftitle, etc.  It can be defined by anyone of us.   


Logged