sábado, 7 de agosto de 2010

Listar Alunos que Ainda não Acessaram o Curso no Moodle com Comando SQL


Para listar os alunos que ainda não acessaram o curso no Moodle é necessário fazer uma consulta nas  tabelas  mdl_role_assignments e mdl_log.

A tabela mdl_role_assignments registra matricula dos alunos no curso. A tabela mdl_log registra histórico de acesso.   Temos que fazer uma consulta unindo as duas tabelas.  Vamos lá, posso por passo.

1º Passo – Listar alunos matriculados no curso.
    Primeiro vamos montar  comando SQL que extrai a lista de alunos matriculados no curso.
 
SELECT u.id, u.firstname,u.lastname FROM mdl_role_assignments rs INNER JOIN mdl_user u ON u.id=rs.userid INNER JOIN mdl_context e ON rs.contextid=e.id WHERE e.contextlevel=50 AND rs.roleid=5 AND e.instanceid=?

Nesse comando, você só precisa passar parâmetro  e.instanceid que deve ser o id do curso. Caso  queira entender um pouco mais sobre esse comando clique aqui
Executando esse comando você terá uma lista com id e  nome dos alunos vinculados ao curso.
 
2º Passo – Listar alunos matriculados no curso que já acessaram
Nesse momento vamos adicionar na consulta a tabela mdl_log.  Vamos montar comando SQL que retorna a lista de usuários matriculados acompanhado da data do último acesso ao curso.
 
SELECT u.id,u.firstname,u.lastname, MAX(l.time) FROM mdl_role_assignments rs INNER JOIN mdl_user u ON u.id=rs.userid INNER JOIN mdl_context e ON rs.contextid=e.id  INNER JOIN mdl_log l ON rs.userid=l.userid WHERE e.contextlevel=50 AND rs.roleid=5 AND e.instanceid=? AND l.course=? GROUP BY u.id,u.firstname,u.lastname

Nesse novo comando, o campo MAX(l.time) retorna o último acesso do aluno no curso. É uma consulta agregada com JOIN na tabela mdl_log. Novamente você precisa passar parâmetro, ou seja, o id do curso  para  e.instanceid e l.course.  Se o id do curso for 2, deve ficar assim e.instanceid=2 AND l.course=2.
Observação, essa consulta só retorna o os alunos que já acessaram.
 
3º Passo – Listar alunos que ainda não acessaram o curso
Bem, essa parte é o  que realmente interessa. Agora já temos duas listas de alunos montando no passo 1 e passo 2. Vamos tirar da primeira lista os alunos que estão presentes na segunda lista. O que sobrar são alunos que ainda não acessaram.   Isso porque na primeira lista consta todos os alunos. Já na segunda consta apenas os que acessaram.
 Podemos excluir a segunda lista da primeira pela programação ou também via comando SQL Pela programação manda a ver aí no PHP, JAVA, ASP e.t.c. Use o que for do seu gosto. Agora, via o SQL, use o comando abaixo:
 
SELECT u.id, u.firstname,u.lastname FROM mdl_role_assignments rs INNER JOIN mdl_user u ON u.id=rs.userid INNER JOIN mdl_context e ON rs.contextid=e.id WHERE e.contextlevel=50 AND rs.roleid=5 AND e.instanceid=? AND u.id NOT IN (?,?,?)

 Novamente você precisa passar parâmetro, ou seja, o id do curso  para  e.instanceid  e lista de id  do usuário retornado no segundo passo em u.id NOT IN (?,?,?).  Preencha NOT IN com  lista id  de usuário.
Os usuários retornos são usuários que ainda não acessaram.  Agora não deve ter ficado dúvidas sobre a junção de tabelas mdl_role_assignments e mdl_log. Então manda a ver aí na programação e bom relatório final do curso.



Até aqui tudo parece que está ok. No meio do silêncio, você volta e questiona:
- Pô moleque, não tem um jeito mais simples de fazer tudo isso sem dar tantas voltas?

Pensando exatamente nisso, vim pesquisando encontrei uma solução mais simples. Tanto que estou atualizando esse poste com novo comando SQL.

Comando SQL para extrair os alunos que não acessaram o curso em um única tacada:

SELECT u.id, u.firstname,u.lastname FROM mdl_role_assignments rs INNER JOIN mdl_user u ON u.id=rs.userid INNER JOIN mdl_context e ON rs.contextid=e.id WHERE e.contextlevel=50 AND rs.roleid=5 AND e.instanceid=? AND u.id NOT IN (SELECT DISTINCT userid FROM mdl_log WHERE course=?)

Passe o parâmetro id do curso  em e.instanceid=? da consulta principal e course=? da subconsulta.

A única diferença nessa consulta é que foi adicionada a subconsulta. Esta substitui os passos e 1 e 2. Sendo assim você pode extrair uma lista de alunos que não acessaram o curso sem a necessidade de executar o código dentro de uma linguagem de programação.

Espero agora que esteja satisfeito.


Veja Também
Listar Alunos que Ainda não Acessaram o Curso no Moodle num Determinado Período com Programação PHP

9 comentários:

  1. Primeiramente parabéns pelo blog, grande referência.
    Agora uma dúvida, como seria para listar os alunos que não está associado a nenhum curso?

    []'s

    ResponderExcluir
  2. Oi Carlos, só alegria!
    Valeu por ser um seguidor do meu Blog. Quanto a sua dúvida de listar os alunos que não estão associados a nenhum curso, acabei de publicar um post sobre isso. Clique aqui para ler.

    ResponderExcluir
  3. Seu blog é muito bom para quem trabalha com o moodle. Já utilizei diversos SQLs seus para relatórios e também quando precisei matricular alunos e etc.

    Muito obrigado pelo seu trabalho e esforço em dividir este conhecimento.

    Abraço.

    ResponderExcluir
  4. Valeu Marcus!
    É uma grande satisfação saber que meu Blog está sendo útil para desenvolvedores do Moodle. Isso me encoraja cada vez mais.
    Um grande abraço .

    ResponderExcluir
  5. Olá Lino,

    Eu testei esse comando SQL:

    SELECT u.id, u.firstname,u.lastname FROM mdl_role_assignments rs INNER JOIN mdl_user u ON u.id=rs.userid INNER JOIN mdl_context e ON rs.contextid=e.id WHERE e.contextlevel=50 AND rs.roleid=5 AND e.instanceid=? AND u.id NOT IN (SELECT DISTINCT userid FROM mdl_log WHERE course=?)

    Tenho dois cursos na minha base de teste, mas não obtive resultado... Nada retornou.

    Você pode me ajudar?

    ResponderExcluir
    Respostas
    1. Professora,
      O comando que você executou é para mapear os alunos que não acessaram um determinado curso.
      Se todos os parâmetros estão ok, significa que todos os alunos inscritos n curso já acessaram. Quando refiro aos parâmetros ok estou referindo a:

      a) rs.roleid=5 O perfil dos alunos inscrito no curso tem id 5, ou seja, não houve customização do perfil do aluno;

      b) Tanto na e.instanceid=? e course=? passou o parâmetro do id do curso quer efetuar o filtro

      Se você quer mapear apenas as inscrições nos cursos, execute esse comando:
      SELECT u.id, u.firstname,u.lastname FROM mdl_role_assignments rs INNER JOIN mdl_user u ON u.id=rs.userid INNER JOIN mdl_context e ON rs.contextid=e.id WHERE e.contextlevel=50 AND rs.roleid=5 AND e.instanceid=?

      Excluir
  6. Badiu, como sei quando o curso esta ativo no moodle atreves do comando sql?

    ResponderExcluir
  7. Badiu, como sei quando o curso esta ativo no moodle através do comando sql?

    ResponderExcluir
  8. Pessoal uma versão mais recente do item postado "2º Passo – Listar alunos matriculados no curso que já acessaram":

    SELECT
    u.id,
    u.firstname as "Nome",
    u.lastname as "Sobrenome",
    from_unixtime(MAX(l.timecreated),'%d/%m/%Y %H:%m:%i') as "Data e Hora"

    FROM mdl_role_assignments rs

    INNER JOIN mdl_user u ON u.id=rs.userid
    INNER JOIN mdl_context e ON rs.contextid=e.id
    INNER JOIN mdl_logstore_standard_log l ON rs.userid=l.userid

    WHERE

    e.contextlevel=50
    AND rs.roleid=5
    AND e.instanceid=l.courseid
    AND l.courseid=23

    GROUP BY u.id,u.firstname,u.lastname

    ResponderExcluir