Dear all,
easy exercise (I hope) for those in the know. I am in an activity, the URL shows a number, which I want to map to a record in the activity table. How can I do it? Ideally using SQL (or by eye, finding the record).
[In my case, I am in a forum; URL is giving me, say, 5368. I want to know in mdl_forum which record 5368 is refering to.]
In 1.8 I think I could work this one out, but now in 1.9 there is a 'path' in which I think what I desire may be hidden.
Any help would be greatly appreciated!
Michael
Michael,
Do you mean where the url gives .../discuss.php?d=114599 (using out current example)?
If so you only need to go to mdl_forum_discussions where the d-value is the id in the table, thus:
SELECT * FROM `mdl_forum_discussions` WHERE `id`=114599;
On the other hand, if you want the specific post within a discussion thread, then again using my post here as an example...
.../discuss.php?d=114599#p502811
then the table is mdl_forum_posts and the sql...
SELECT * FROM `mdl_forum_posts` WHERE `discussion`=114599 AND `id`=502811;
Regards,
John
Do you mean where the url gives .../discuss.php?d=114599 (using out current example)?
If so you only need to go to mdl_forum_discussions where the d-value is the id in the table, thus:
SELECT * FROM `mdl_forum_discussions` WHERE `id`=114599;
On the other hand, if you want the specific post within a discussion thread, then again using my post here as an example...
.../discuss.php?d=114599#p502811
then the table is mdl_forum_posts and the sql...
SELECT * FROM `mdl_forum_posts` WHERE `discussion`=114599 AND `id`=502811;
Regards,
John
Dear John,
thanks for replying. - Actually, I was after the principle behind the context/instance/path structure and perhaps chose with the forums a bad example. (In fact, I am looking at the third party scheduler module.) I assumed that the implementations are the same, but probably not.
My understanding was that I have to look at mdl_context to map an 'objectid' to an 'instanceid' depending on the context it is in. The path, however, confuses me, but, perhaps I am confused anyway.
[My trouble is that I do all my reporting in SQL which I know well and not in PHP which I don't know. I seem to be unable to find a good documentation of the tables anywhere, but this, surely must exist.]
Thanks anyway,
Michael
thanks for replying. - Actually, I was after the principle behind the context/instance/path structure and perhaps chose with the forums a bad example. (In fact, I am looking at the third party scheduler module.) I assumed that the implementations are the same, but probably not.
My understanding was that I have to look at mdl_context to map an 'objectid' to an 'instanceid' depending on the context it is in. The path, however, confuses me, but, perhaps I am confused anyway.
[My trouble is that I do all my reporting in SQL which I know well and not in PHP which I don't know. I seem to be unable to find a good documentation of the tables anywhere, but this, surely must exist.]
Thanks anyway,
Michael
What we have so far is Development:Database_schema_introduction. (It's a wiki, anyone is welcome to help improve it.) Also, if you go to Administration -> Programming -> XMLDB in a Moodle 2.0 dev install, you can get low-level documentation for each table build from the comments in the database definition files.
Contexts are mostly to do with permissions checking, and they form a tree structure as stored in the path column. Contexts can be of different types, so depending on contextlevel, instanceid joins to different tables. Look at some of the roles Development documentation in Moodle docs. (Note that the Developer: namespace is not included in searches by default.)
Contexts are mostly to do with permissions checking, and they form a tree structure as stored in the path column. Contexts can be of different types, so depending on contextlevel, instanceid joins to different tables. Look at some of the roles Development documentation in Moodle docs. (Note that the Developer: namespace is not included in searches by default.)
Michael,
It took me a while to follow your question (having got it wrong the first time) but I would still like to see an example of the URL info you specifically want to decode!
However, I can offer a little more on the table mdl_context...
This table clocks 'instances' appearing in a heirachical 'context' structure, and gives each appearance of each instance of an object a unique context id. But what are the objects themselves?
To understand this you need to understand what 'contexts' are...
// context definitions
define('CONTEXT_SYSTEM', 10);
define('CONTEXT_USER', 30);
define('CONTEXT_COURSECAT', 40);
define('CONTEXT_COURSE', 50);
define('CONTEXT_GROUP', 60);
define('CONTEXT_MODULE', 70);
define('CONTEXT_BLOCK', 80);
...taken from file lib/accesslib.php.
Hence if I look at a section of my mdl_context table, as attached I have...
...a context id=1 for the SYSTEM context, but there can only ever be one of those so its instance=0 (nothing to look up), its path is itself, and depth 1 means nothing to iterate through;
a context id=2 for a COURSE context, its instance=1 (so its course 1- the front page) and I could find more about it by looking at id=1 in mdl_course, this context is nested within context 1 as its path and depth show;
context id=3 is similar but its detail will be in mdl_course_catagories;
context id=4 is a block found in mdl_block where id=1, but this block is within course 1 which itself is within the system (unsurprisingly) hence the path /1/2/4 with depth 3;
finally, I show context id=1082 which is a context for a USER (who's id=109 in mdl_user) who must have been given a system role because in the path that's the only precedent context!
I think that should be plenty!
If you are going to handle this only in SQL looks like you will have to define a function to return the info about the instance of an object in any one of six tables that contextlevel might refer to.
Regards,
John
PS (Edit) Hi Tim, thanks for the links, we were writing at the same time!
It took me a while to follow your question (having got it wrong the first time) but I would still like to see an example of the URL info you specifically want to decode!
However, I can offer a little more on the table mdl_context...
This table clocks 'instances' appearing in a heirachical 'context' structure, and gives each appearance of each instance of an object a unique context id. But what are the objects themselves?
To understand this you need to understand what 'contexts' are...
// context definitions
define('CONTEXT_SYSTEM', 10);
define('CONTEXT_USER', 30);
define('CONTEXT_COURSECAT', 40);
define('CONTEXT_COURSE', 50);
define('CONTEXT_GROUP', 60);
define('CONTEXT_MODULE', 70);
define('CONTEXT_BLOCK', 80);
...taken from file lib/accesslib.php.
Hence if I look at a section of my mdl_context table, as attached I have...
...a context id=1 for the SYSTEM context, but there can only ever be one of those so its instance=0 (nothing to look up), its path is itself, and depth 1 means nothing to iterate through;
a context id=2 for a COURSE context, its instance=1 (so its course 1- the front page) and I could find more about it by looking at id=1 in mdl_course, this context is nested within context 1 as its path and depth show;
context id=3 is similar but its detail will be in mdl_course_catagories;
context id=4 is a block found in mdl_block where id=1, but this block is within course 1 which itself is within the system (unsurprisingly) hence the path /1/2/4 with depth 3;
finally, I show context id=1082 which is a context for a USER (who's id=109 in mdl_user) who must have been given a system role because in the path that's the only precedent context!
I think that should be plenty!
If you are going to handle this only in SQL looks like you will have to define a function to return the info about the instance of an object in any one of six tables that contextlevel might refer to.
Regards,
John
PS (Edit) Hi Tim, thanks for the links, we were writing at the same time!
