Tuesday, March 21, 2017

how to return something even if inner join doesnt match

i have two tables

Cursos

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| curso | varchar(255)     | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

Trienios

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| data_trienio | varchar(255)     | NO   |     | NULL    |                |
| curso_id     | int(11)          | NO   |     | NULL    |                |
| oe_id        | int(11)          | NO   |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

those tables are connected through a relationship (as seen through curso_id), and i want to make a query where i retrieve the curso records and the number of trienio records related to each one of them

so i've done this query in laravel

$curso = Curso::select([
            'cursos.curso',
            \DB::raw('count(trienios.curso_id) as count')
        ])->join('trienios', 'trienios.curso_id', '=', 'cursos.id')
        ->groupBy('trienios.curso_id');

which translates to this

select `cursos`.`curso`,
       count(trienios.curso_id) as count
from `cursos`
inner join `trienios`
    on `trienios`.`curso_id` = `cursos`.`id`
group by `trienios`.`curso_id`

and it gets me the number of cursos with trienios related to them. HOWEVER, it only gives those who have a number of trienios related to them. the other ones who dont have trienios related to them are not queried, and i want to query them. so, how do i solve this issue ?



via Bad Laravel Programmer

Advertisement