Consultar os Tópicos do Fórum mais Acessados no Moodle com Comando SQL

Se você trabalha na pate de TI já deve ter recebido alguma solicitação do tutor ou coordenador do curso para montar um gráfico ou tabela com os típicos mais acessados do fórum.

    Ao receber essa demanda, você fuça um pouco o Moodle e percebe que não existe essa opção de relatório. Mesmo assim a equipe pedagógica fica no seu cangote esperando uma solução.

    Neste caso, você não tem outra alternativa que não seja extrair esses dados diretamente da base de dados do Moodle com comando SQL. Para aliviar a sua barra,  facilitando algumas dias ou semanas  de pesquisa vai aí  o macete.
Para consultar os tópicos mais acessados é necessário fazer a junção das seguintes tabelas:

• mdl_forum – Tabela de fórum
• mdl_forum_discussions – Tabela de tópicos
• mdl_modules – Tabela de atividades do curso
• mdl_log – Tabela de log

SQL para MySql

SELECT d.id,d.name,COUNT(l.info) FROM  mdl_forum_discussions d INNER JOIN mdl_forum f on f.id=d.forum  INNER JOIN mdl_course_modules cm ON f.id=cm.instance INNER JOIN mdl_modules m  ON cm.module=m.id INNER JOIN mdl_log l ON l.cmid=cm.id WHERE f.id=? AND m.name='forum' AND l.module='forum' AND l.action LIKE '%discussion%' AND l.info=d.id GROUP BY d.id,d.name ORDER BY COUNT(l.cmid) DESC

SQL para PostgreSQL

SELECT d.id,d.name,COUNT(l.info) FROM  mdl_forum_discussions d INNER JOIN mdl_forum f on f.id=d.forum  INNER JOIN mdl_course_modules cm ON f.id=cm.instance INNER JOIN mdl_modules m  ON cm.module=m.id INNER JOIN mdl_log l ON l.cmid=cm.id WHERE f.id=? AND m.name='forum' AND l.module='forum' AND l.action LIKE '%discussion%' AND l.info=CAST(d.id as varchar) GROUP BY d.id,d.name ORDER BY COUNT(l.cmid) DESC

O que diferencie os dois comandos SQL é que na  consulta para PostgreSQL há conversão d.id para texto com comando CAST: l.info=CAST(d.id as varchar).  Isso  porque o PostgreSQL não compara campo numérico com texto. Já MySQL é mais tolerante. Tirando isso, o restante do comando é igual.

Essa consulta extrai uma lista com os seguintes campos:

• d.id – Id do tópico
• d.name – Nome do tópico
• COUNT(l.info) – Quantidade de acesso (click) no tópico

Você precisa especificar o id do fórum passando parâmetro em  f.id=? logo após o comando WHERE.
Caso não souber o id do fórum, localize na base de dados pelo nome do fórum com o seguinte comando SQL:

SELECT id FROM mdl_forum WHERE name='Nome do Fórum'

Bem, agora que você tem o comando SQL, só falta implementar isso numa linguagem de programação ou então executar a consulta no banco  e copiar o dados para excel para montar um gráfico bonito. Assim você dá solução à demanda de forma rápida e ninguém fica no seu cangote.

Apagar Nota, Atividades e Log do Aluno no Curso do Moodle com Comando SQL

Ao cancelar a inscrição de um aluno no curso no ambiente Moodle, os registros de na base de dados (atividades realizadas, nota e log) vinculado à matricula   não serão excluídos automaticamente, ou seja, em efeito cascata.  Uma das alternativas para remover os registros órfãos é executar o comando SQL diretamente na base de dados.
  
Para excluir os dados órfãos de uma matricula  no curso  é necessário dois parâmetros:

• id do curso – Chave de identificação do curso na tabela mdl_course

• id do usuário - Chave de identificação do curso na tabela mdl_user

Há uma postagem no blogue que explique isso. Clique aqui para ler.


Tendo os parâmetros da chave do curso e do usuário, só resta   apagar os registro com comando DELETE passando os parâmetros da chave do usuário e curso.
Passe o parâmetro da chave do curso em course=?   ou courseid =? e do usuário em userid=?


Apagar Log
DELETE FROM mdl_log WHERE course=? AND userid=?
   
Apagar Notas
DELETE FROM mdl_grade_grades WHERE  userid=? and itemid IN  (SELECT id FROM mdl_grade_items WHERE courseid=?)

    A sub consulta apaga apenas as notas de um determinado curso.
A tabela mdl_grade_grades é o repositório final de notas.

Apagar Atividades

Apagar nota do fórum
DELETE FROM mdl_forum_ratings WHERE post IN (SELECT p.id FROM mdl_forum_posts p INNER JOIN mdl_forum_discussions d ON p.discussion=p.id  WHERE p.userid=? AND d.course=?)

Apagar os comentários
DELETE FROM mdl_forum_posts WHERE userid=?  AND  discussion IN (SELECT id FROM mdl_forum_discussions WHERE course=?)

Não é recomendável apagar os comentários já que os comentários aninhados ficarão órfãos, ou seja, as respostas das respostas.

Apagar tópicos
DELETE  FROM mdl_forum_discussions WHERE userid=? AND course=?

Não é recomendável apagar os tópicos já que os comentários aninhados ficarão órfãos.

Apagar Tarefas
DELETE FROM mdl_assignment_submissions WHERE userid =? AND assignment IN (SELECT id FROM mdl_assignment WHERE course =?)

Ao remover as tarefas, as notas serão removidas automaticamente.

Apagar questionário

Apagar nota final
DELETE FROM mdl_quiz_grades WHERE userid =?  AND quiz IN (SELECT id FROM mdl_quiz WHERE course =?)

Apagar Tentativas de Respostas
DELETE FROM mdl_question_states WHERE  attempt IN (SELECT t.id FROM mdl_quiz_attempts t INNER JOIN  mdl_quiz q  ON t.quiz=q.id WHERE t.userid= ? AND course =?)

Apagar cada tentativa (inclusive a nota final)
DELETE FROM mdl_quiz_attempts WHERE userid =?  AND quiz IN (SELECT id FROM mdl_quiz WHERE course =?)

Para fazer limpeza total, os dados do aluno devem ser removida de todas de todas as atividades instanciadas no curso. Os comandos SQL acima demostrados se restringiram em remover as atividades do fórum, tarefa e questionário.  Para as demais atividades, siga a mesma lógica. Identifica as tabelas e apague os dados.
Feito isso, todo o histórico do aluno será removida da base de dados. Ao ser reinscrito no curso,  não terá nenhuma nota e nem log de acesso.

Extrair id do Usuário e do Curso no Moodle com Comando SQL

Id é  o parâmetro de identificação do registro na base de dados.


Id do Usuário

Comandos SQL que  recuperam  o id do usuário
Por -Email:
SELECT id FROM mdl_user WHERE email='e-mail'

 
Por login:
SELECT id FROM mdl_user WHERE username='login'

Por login e senha:
SELECT id FROM mdl_user WHERE username='login' AND password=MD5('senha')

Parâmetro GET do URL do Moodle com Id do usuário
URL do perfil do usuário:
http://[enderco do domininio]/user/view.php?id=21&course=3
O parâmetro id=21  é  a chave de identificação do usuário


Id do Curso
Comandos SQL que  recuperam o id do curso

Por nome
SELECT id FROM mdl_course WHERE fullname='Nome do Curso'

Pela Abreviatura
SELECT id FROM mdl_course WHERE shortname='Abreviatura'

Parâmetro GET do URL do Moodle com id do curso
URL do curso
http://[endereco dominico ]/course/view.php?id=3
O parâmetro id=3 é  a chave de identificação do curso.

Criar Curso no Moodle com Comando SQL

    Se você estiver fazendo migração de dados ou integração de um sistema acadêmico com a  Plataforma  Moodle, certamente vai precisar criar curso no Moodle diretamente no banco de  dados sem usar a interface do Moodle.

      Isso implica conhecer a estrutura das tabelas da base de dados do Moodle e fazer INSERT nas tabelas certas. Bem, isso me custou muitas horas de pesquisa. No final descobri que é necessário fazer apenas 4 INSERT básicos. Bem, vamos lá. São  três passos. 

Os comandos SQL foram testados na versão 1.9.3 do Moodle. Devem funcionar para qualquer versão 1.9.x e não para versão inferior a 1.9.
1º Passo – Criar o Curso
    Essa parte é mais moleza. Basta fazer um INSERT na tabela mdl_course com o seguinte comando SQL:

INSERT INTO mdl_course (category,fullname,shortname) VALUES (1,'Curso1','C1')   

Embora a tabela  mdl_course tenha muitas colunas, nesse exemplo foram usadas os mais importantes:

• category -  Chave estrangeira da tabela de categoria de curso - mdl_course_categories 

• fullname – Nome complete do curso 

• shortname – Abreviatura do curso

No comando INSERT, foi criado um curso denominado Curso1 na categoria de curso padrão do Moodle, cujo id é 1. 
Após executar o INSERT, recupera o id do curso gerado automaticamente pelo banco de dados. No MySQL use o comando LAST_INSERT_ID().

2º Passo – Criar Contexto do Curso
    Com o curso criado, agora é necessário criar contexto do curso na tabela mdl_context com o seguinte comando SQL:

INSERT INTO mdl_context (contextlevel,instanceid) VALUES (50,?)
O campo contextlevel define o nível do contexto. Para curso 50 é o valor padrão da tabela de domínio. O campo instanceid é a instância do curso. Preencha esse campo com o valor do Id do curso gerado automaticamente no 1º passo.

    Ao executar o comando SQL, recupere o id do contexto gerado automaticamente. Use esse id para atualizar duas colunas:  path e depth. Para isso, execute o seguinte comando SQL:

UPDATE  mdl_context SET path='/1/3/$ID_CONTEXT', depth=3 WHERE id=$ID_CONTEXT

    Você precisa substituir a variável $ID_CONTEXT pelo id gerado automaticamente do INSERT anterior, ao executar o comando  para criar o contexto do curso. Se o id for 10,  o comando SQL de atualização será:

UPDATE  mdl_context SET path='/1/3/10', depth=3 WHERE id=10
Nesse momento você deve estar perguntando por que o campo path recebe  '/1/3/$ID_CONTEXT' como valor padrão  e o  campo  depth o número 3. Ainda não encontrei a resposta. Assim como você, eu também estou perguntando. Só sei que demorei três semanas mudando de variável até que funcionou. Se não preencher esses campos,  nenhum usuário consegue entrar no curso.  Se você encontrar alguma explicação  não esqueça de me avisar.

3º Passo – Criar tópico/seção padrão do curso 
Ao criar o curso no 1º passo, não foi definido o formato. Por padrão, será criado curso em formato de tópico.  Assim, é necessário cria um primeiro tópico, o tópico número zero,  do curso. Para isso, execute o seguinte comando SQL:

 INSERT INTO mdl_course_sections (course,section) VALUES (?,0)
Na coluna course o valor do parâmetro é o id do curso gerado automaticamente no 1º passo. A segunda coluna, a section define a ordem do tópico. Por se tratar do primeiro tópico, deixe zero como está.
    Há outras colunas na tabela mdl_course_sections. No entanto, para efeito de simplificação, se restringiu aos mais importantes.
    Ao finalizar o 3º passo, o curso já está criado no Moodle. Há muitas configurações do curso que não foram abordado nesse poste. Você pode fazer isso preenchendo todos as outras colunas das tabelas mdl_course ,  mdl_course_sections  e entre outros. Agora que o curso já está criado, acesse o ambiente Moodle com a senha do admin,  ative a edição e adicione o bloco de administração. Daí, faça o restante das configurações.

    Os comandos SQL foram testados apenas na versão 1.9.3. Deve funcionar em qualquer versão 1.9.x  já a estrutura das tabelas são as mesmas.  Não deve funcionar para as versões anteriores a 1.9.  Mas lógica é muito parecida. Não custa testar e fazer os ajustes necessários.  Agora boa programação, ou melhor, boa sorte na jornada de integração do Moodle com o seu sistema acadêmico.


Veja Também
Matricular Usuário no Curso do Moodle com Comando SQL

Verificar a Senha do Usuário do Moodle com Comando SQL

    É muito comum no Moodle a senha do usuário não funcionar. Isso pode ocorrer após a importação da base de dados de usuário, cadastro de novo usuário, recuperação de curso  etc. O fato é que, de repente,  as senhas deixam de funcionar. Ao logar, mesmo usando o login e a senha corretos não funciona.  

    Quando isso acontece, as causas podem ser diversas. Para eliminar a hipótese que você esteja passando o login e a senha erradas ou que a tabela do banco que armazena a senha não foi corrompida,  é necessário fazer uma consulta SQL diretamente na base de dados. Nessa consulta, verifique se a combinação de login e senha existem. Para isso, execute o seguinte comando SQL:

SELECT COUNT(id) FROM   mdl_user WHERE username='joao' AND password=MD5('silva')
Se o resultado da consulta retornar 0 (zero)  significa que não existe nenhum usuário cadastrado com login João e senha silva. Caso retorna 1 (um) significa que o cadastro.

    Se a combinação do login e da senha funcionarem no comando SQL deve funcionar também ao logar no Moodle.  Se não funcionar no Moodle, é sinal que problema é outro e não da senha. Neste caso, você eliminou uma hipótese. Então, passe para próxima hipótese da lista e boa sorte.

Veja Também:
Recuperar Senha do Administrador do Moodle com Comando SQL

Padronização das tabelas do Banco de Dados do Moodle

    A Plataforma Moodle é um sistema modular, ou seja, é um ambiente de gerenciamento de vários módulos voltado para gerenciamento de cursos.  A estrutura da base de dados reflete muito bem isso.

Padrão de Nome das  Tabelas
    As tabelas no banco de dados são compostas pelo prefixo e nome do módulo. mdl_ é o prefixo padrão. Isso pode ser alterado no momento de instalação.  Por exemplo, a tabela do módulo fórum é mdl_forum. Sendo mdl_ é o prefixo e forum é o nome do módulo. Todos os módulos seguem esse padrão.

Módulos que não são do Núcleo do Sistema
Os módulos que não compões o núcleo do sistema  ficam registradas na tabela mdl_modules. Para visualizar esses módulos,  basta fazer uma consulta na  tabela mdl_modules, usando o seguinte comando SQL:

SELECT id,name FROM mdl_modules
Resultado da pesquisa:
Id    name
1      assignment
2      chat
3      choice
4      data
5      forum
6      glossary
7      hotpot
8      journal
9      label
10      lams
11      lesson
12      quiz
13      resource
14      scorm
15      survey
16      wiki
17      workshop  

    Essa consulta foi feita nas versões 1.9.3 e 1.9.7 do Moodle. Isso já é padrão da versão 1.9+ A consulta lista os módulos que vêm na distribuição padrão do Moodle. A consulta traz os campos id (chave de identificação) e name (nome do módulo).

Tabela Principal e Secundária do Módulo
    A tabela principal de cada modulo é  prefixo + nome do módulo. Em cada módulo há outras tabelas, ou seja, tabelas secundárias. Por exemplo, a tabela principal do fórum é mdl_forum (prefixo + nome do módulo). O fórum é  composta por tópicos de discussões de comentários. Pois, as tabelas secundárias são:

• mdl_forum_discussions – Tabela dos tópicos de discussão do fórum
• mdl_forum_posts    - Tabela de comentários do fórum
• mdl_forum_ratings    - Tabela de nota do fórum

    Nas tabelas secundárias dá para notar que o padrão do nome é prefixo + nome do módulo + funcionalidade do módulo.
    Embora tomamos como exemplo a tabela do fórum, esse padrão se aplica a todos os módulos.

Colunas Padrão nas Principais  Tabelas que não são do Núcleo do Sistema
    Até agora deu para entender a estrutura das tabelas dos módulos. Em cada tabela do módulo que não seja do núcleo do sistema, por padrão, deve as seguintes colunas:

• id – Chave de identificação de cada registro da instância do módulo.
• name – Nome  do registro da instância do módulo.
• course – Id do curso  em que o módulo está vinculado. É a chave estrangeira da tabela mdl_course. Isso significa que cada registro da instancia de um módulo deve estar obrigatoriamente vinculado a um determinado curso.

Com esse padrão, torna possível montar uma rotina que faz leitura automática de todos os módulos. Para tornar isso mais claro, vamos ver um exemplo.
O comando SQL  abaixo faz uma consulta dos campos padrões do módulo fórum.

SELECT id, name, course FROM mdl_forumResultado da pesquisa:
id    name        course
1    Fórum Teste I     2
2    Fórum Teste II    2
3    Dúvidas Gerais    3
    A consulta retorna três registros de fórum. A coluna course indica que os fóruns registrados pertencem aos cursos cujo id são 2 e 3. A  mesma pesquisa pode ser aplicada a qualquer módulo, basta substituir a parte do nome da tabela após o prefixo pelo nome do outro módulo. Para pesquisar no módulo do questionário, o comando SQL seria:

SELECT id, name, course FROM mdl_quiz
    Isso não se aplica aos módulos que compões ao núcleo  do sistema tais como usuário, curso, log etc.

Tabelas dos módulos do sistema
mdl_user – Tabela principal do módulo do usuário
mdl_course  - Tabela principal do módulo do curso
mdl_log - Tabela principal do módulo de log
etc.

 
    Bem, você já deve ter sacado como é o padrão e a estrutura das tabelas do Moodle. Caso queira  explorar mais afundo isso, clique aqui, e acesse um arquivo com dump, ou seja, um backup da estrutura de todas as tabelas do Moodle 1.9.3 e com dados reais sobre curso. Estudar banco de dados não é uma tarefa muito mole, por isso lhe desejo boa sorte e muita paciência.

Extrair Lista de Usuários não Cadastrados no Curso do Moodle com Comando SQL

    Para extrair uma lista de usuários que não estão cadastrados em nenhum curso do Moodle ou em um determinado curso, é necessário fazer a junção por exclusão da tabela usuário na tabela matrícula. 

mdl_user – Tabela de usuário
mdl_role_assignments – Tabela de matrícula a partir da versão 1.7
    A junção por exclusão consiste em mapear todos os registros na tabela usuário que não tenham correspondência na tabela da matrícula. No comando SQL  isso é implementado por meio da sub consulta, como segue a abaixo.

Lista de usuários que não estão cadastrados em nenhum curso
SELECT  id,firstname, lastname FROM mdl_user    WHERE id NOT IN (SELECT userid FROM mdl_role_assignments )
Contar a quantidade de  usuários que não estão cadastrados em nenhum curso
SELECT  COUNT(id) FROM mdl_user    WHERE id NOT IN (SELECT userid FROM mdl_role_assignments )
Lista de usuários que não estão cadastrados em um determinado curso
SELECT  id,firstname, lastname  FROM mdl_user    WHERE id NOT IN (SELECT userid FROM mdl_role_assignments rs INNER JOIN mdl_context c ON rs.contextid=c.id WHERE c.instanceid=?)
Passe o parâmetro id do curso em c.instanceid=?

Contar a quantidade de  usuários que não estão cadastrados em um determinado curso
SELECT COUNT(id)   FROM mdl_user    WHERE id NOT IN (SELECT userid FROM mdl_role_assignments rs INNER JOIN mdl_context c ON rs.contextid=c.id WHERE c.instanceid=?)
Passe o parâmetro id do curso em c.instanceid=?

    Todos os filtros utilizam sub-select, uma consulta dentro da outra. O primeiro SELECT extrai a lista de usuário da tabela do usuário. O segundo SELECT extrai a lista de usuários da tabela matrícula. O camando NOT IN exclui da primeira lista, os usuários que tem correspondência na segunda lista. Assim, sobra apenas os que não estão cadastrados no curso.

Veja também:
Matricular Usuário no Curso do Moodle com Comando SQL
Cancelar Matricula no Moodle com Comando SQL
Data de inscrição do aluno no curso do Moodle