112 return get_recordset_sql("SELECT p.id, p.subject, p.discussion, p.message, 113 p.deleted, d.groupid, u.firstname, u.lastname 114 FROM {$CFG->prefix}forum_discussions d 115 JOIN {$CFG->prefix}forum_posts p ON p.discussion = d.id 116 JOIN {$CFG->prefix}user u ON p.userid = u.id 117 WHERE d.forum = '$forum' 118 AND p.parent = 0 119 $timelimit 120 ORDER BY d.timemodified DESC");
I'm just making a new block and trying to use get_recordset_sql and have noticed that in other places, the SQL it uses has single letter prefixes to field names e.g.
Hi Matt,
When you submit a SQL query you can define aliases for tables by writing the alias after the table name in the FROM list like:
FROM {$CFG->prefix}forum_discussions d
Now, the fields of the table (e.g. groupid) can be referenced with the d prefix (e.g. d.groupid).
You must use table aliases if you include a table twice in the FROM list or you want to refference a field with the same name as other fields in the query (e.g. in this query the name id is used by three fields, in the tables user, forum_posts, and forum_discussions).
Hope this helps,
Andrei.
When you submit a SQL query you can define aliases for tables by writing the alias after the table name in the FROM list like:
FROM {$CFG->prefix}forum_discussions d
Now, the fields of the table (e.g. groupid) can be referenced with the d prefix (e.g. d.groupid).
You must use table aliases if you include a table twice in the FROM list or you want to refference a field with the same name as other fields in the query (e.g. in this query the name id is used by three fields, in the tables user, forum_posts, and forum_discussions).
Hope this helps,
Andrei.
Hi Matt,
If I understood your question correctly, the single-letter prefix is a common SQL shorthand statement called a "table alias" that benefits the programmer by not having to repeat the full table name each time a field is called. The prefix has less to do with Moodle and more to do with the programmer's manner of evoking SQL.
In the example above, "d" is the alias for the table "{prefix}forum_discussions" and "p" is the alias for the table "{prefix}forum_posts."
To illustrate, assuming the global Moodle table prefix is "mdl_" and without using table aliases, line 112 would instead read:
Hope that helps,
Jim
If I understood your question correctly, the single-letter prefix is a common SQL shorthand statement called a "table alias" that benefits the programmer by not having to repeat the full table name each time a field is called. The prefix has less to do with Moodle and more to do with the programmer's manner of evoking SQL.
In the example above, "d" is the alias for the table "{prefix}forum_discussions" and "p" is the alias for the table "{prefix}forum_posts."
To illustrate, assuming the global Moodle table prefix is "mdl_" and without using table aliases, line 112 would instead read:
SELECT mdl_forum_posts.id, mdl_forum_posts.subject, mdl_forum_posts.discussion, mdl_forum_posts.message,etc. You can see how much space and time is saved using table aliases.
Hope that helps,
Jim
Thanks guys!
Clear and informative, both.
Matt
Clear and informative, both.
Matt