使用雄辩的复杂联接

Question

I have three models: Vehicles:

+--------------------+------------------+------+-----+---------------------+----------------+
| Field              | Type             | Null | Key | Default             | Extra          |
+--------------------+------------------+------+-----+---------------------+----------------+
| id                 | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| registration       | varchar(255)     | NO   |     | NULL                |                |
| vin                | varchar(255)     | NO   |     | NULL                |                |
| titular_id         | int(10) unsigned | NO   |     | NULL                |                |
| titular_type       | varchar(255)     | NO   |     | NULL                |                |
| renter_id          | int(10) unsigned | NO   |     | NULL                |                |
| renter_type        | varchar(255)     | NO   |     | NULL                |                |
+--------------------+------------------+------+-----+---------------------+----------------+

SiretCompanies:

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

SirenCompany:

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

A vehicle can be related to the SirenCompany either through a titular, or a renter. What I want is to get all related vehicles for a SirenCompany.

In raw MySQL, this is my query:

select count(*) FROM `vehicles` 
inner join `siret_companies` 
    on (
        (`vehicles`.`titular_type` = 'SiretCompany' and `vehicles`.`titular_id` = `siret_companies`.`id`) 
        OR
        (`vehicles`.`renter_type` = 'SiretCompany' and `vehicles`.`renter_id` = `siret_companies`.`id`)
    )
inner join `siren_companies` 
    on `siren_companies`.`id` = `siret_companies`.`siren_company_id` 
where `siren_companies`.`id` = 410

Now what I would like to do is run that as an Eloquent query, but I cannot seem to figure it out.

If I only consider the titular, I have this:

return Vehicle::join('siret_companies',function($join) {
    $join
        ->where('vehicles.titular_type', '=',  'SiretCompany')
        ->where('vehicles.titular_id','=', 'siret_companies.id');
})
->join('siren_companies', function($join) {
    $join->on('siren_companies.id', '=', 'siret_companies.siren_company_id');
})
->where('siren_companies.id','=',$this->id)
->count();

But I cannot seem to figure out how to write the join so that it corresponds to the above query.

Answer 1

This is what I've come up with

Vehicle::join('siret_companies', function ($q) {

            $q->on('vehicles.titular_type', '=',  'SiretCompany');
            $q->orOn('vehicles.titular_id','=', 'siret_companies.id');
        })->join('siret_companies', function ($q) {

            $q->on('siren_companies.id', '=', 'siret_companies.siren_company_id');
        })->where('siren_companies.id','=', $id);