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
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
