Consultation in a Relationship Many to Many in Laravel 4

Question

Guys, I’m "stuck" on a problem concerning a customer report.

I have the following relationship:

• Module (id, name)
• Associate (id, name, superior_id)
• Association_modulos (modulo_id, associado_id, class, enrolled, graduated, dropped out)

What I need is: list all module relative to a "superior_id", with the following information:

• how many classes belong to this module = here the first problem: class is a varchar.. just a description, for example: Class 45 from 08:00h to 21:00h.
• how many members for that module are present
• how many associates for that module are registered
• how many members of that module have given up

In short: I don’t know the best way to make this relationship (Many to Many? ), and what better way to do this query, mainly by class be a varchar and not any ḿodulo (which I could do a Count through the standard relationship).

Any information you need let me know.

Answer 1

It would go something like this: This is the best way to use, ie with Query Builder since the Eloquent is half-cast.

DB::table('associados_modulos')            
        ->join('modulo', 'associados_modulos.modulo_id', '=', 'modulo.id')
        ->join('associado', 'associados_modulos.associado_id', '=', 'associado.id')
        ->where('superior_id', $codigoSuperior_id)
        ->groupBy('modulo_id')            
        ->select(DB::raw('count(turma) qtdeturmas, sum(matriculado) qtdematriculados, count(desistiu) qtdedesistiu, modulo_id'))
        ->get();