Gossamer Forum
Home : Products : Gossamer Forum : Discussion :

sql statements...

Quote Reply
sql statements...
Ok, i'm a little stumped :P

How do I do this....

Code:


SELECT post_root_id, post_subject, ..., count(post_id) as num_new_replies
FROM gforum_Post
WHERE post_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 DAY))
GROUP BY post_root_id
ORDER BY num_new_replies DESC
LIMIT 100;


What I can't figgure out how to use Gossamer's SQL object to do the "SELECT *, count(post_id) as num_new_replies" part above.

What I'm trying to do is to write a global that will return a list of discussions that have had the most replies in the past 24 hours.

Thanks,

Joe
--
Christianity.com Forums
Quote Reply
Re: [joet] sql statements... In reply to
Try something like (I assume you know how to translate the GROUP and WHERE statements):
Code:
$table->select( ['post_root_id', ....., 'COUNT(*) AS num_new_replies'], $cond);

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [yogi] sql statements... In reply to
Thanks. That works great. Do you know if there a way to join a table to itself and/or alias a table? For example,

Code:
SELECT p1.post_id, p1.post_subject, count(p2.post_id) as num_new_replies
FROM gforum_Post p1, gforum_Post p2
WHERE p1.post_id=p2.post_root_id AND p2.post_time > UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 1 DAY)) AND p2.post_root_id > 0
GROUP BY p2.post_root_id
ORDER BY num_new_replies DESC
LIMIT 100;


The problem is that when I do the group by post_root_id, the aggregated columns are not necessarily correct.



One more question sql related question, do you know how to do left joins? Something along the lines of...

Code:


SELECT a.fieldX, a.fieldY, b.fieldZ

FROM foo a LEFT JOIN bar b ON a.pk = b.fk

WHERE ...


Doing the following in the perl code does a complete join, which is not what I want.

Code:


my $q = $DB->table('foo', 'bar');


Thanks. (This would be much easier if mysql had views ;p )

Joe
--
Christianity.com Forums
Quote Reply
Re: [joet] sql statements... In reply to
The table join to itself isn't possible, due to limitations in the table relation handling of GT::SQL. We've talked about writing a much more generic and cleanly laid out database interface, which will make it much easier to handle this sort of thing, and clean up the way it internally handles data (for example, right now it passes some raw SQL around, and the drivers are responsible for converting MySQL syntax into syntax for the other SQL servers, where there are differences. Ideally, this should be changed to always passing around something representing a query, and the driver portion should generate all of the SQL required, thus allowing for non-SQL drivers in the future).

Joining a table with itself isn't supported, simply because there is no way to use a table alias ("gforum_Post" vs. "gforum_Post p1").

Left joins are partially support - the syntax (which I fully admit is quite messy) is:

my $table = $DB->table(TABLES);
$table->select(left_join => NORMAL_SELECT_ARGUMENTS);

The reason I say "partially" is two-fold. First, there is no way to specify what columns are being left-joined. For example, without the GForum libraries, this query cannot be done:

SELECT * FROM gforum_Post LEFT OUTER JOIN gforum_User ON gforum_Post.user_id_fk = gforum_User.user_id

The GForum libraries provide a subclass of GT::SQL::Relation which specify the 'gforum_Post.user_id_fk = gforum_User.user_id' part. This only works, however, for Post-User, Message-User, and SentMessage-User left join queries.

The second reason is partially based on the first - there simply is no way to arbitrarily join tables. The left join code _only_ allows for two and only two tables to be joined, and only left joined; for example, this query cannot be performed:

SELECT post_subject, user_id_fk, forum_name, cat_name
FROM gforum_Post left outer join gforum_User on gforum_Post.user_id_fk = gforum_User.user_id, gforum_Forum, gforum_Category
WHERE gforum_Post.forum_id_fk = gforum_Forum.forum_id AND gforum_Forum.cat_id_fk = gforum_Category.cat_id;

(On a side note, you can select from 3 or more tables, you just can't have any left joins in there). Our idea is to clean up both the interface and the functionality to make these queries possible. That is quite a long way off in the future, however.


But what should you do now? What I'd advise is for you to look at the various GForum/*.pm file (in particular, given what you seem to be doing, GForum/Forum.pm) to see how it is done now. Although it may seem to make less sense, due to SQL optimizations and indices, it often makes more sense to split up one large query into multiple smaller queries, especially when you can reduce the overall number of rows examined.

Jason Rhinelander
Gossamer Threads
jason@gossamer-threads.com