how to join 3 tables count and subtract

i need to join 3 tables

table 1 bus - bus_type, seats_max, departure_time, arrival_time, bus_id(fk)
table 2 trips - departure, destination
table 3 passengers - *

my query

    $sql = "SELECT *
        FROM bus
        INNER JOIN trips WHERE trips.id = bus.bus_id
        AND trips.departure = :departure 
        AND trips.destination = :destination 
        ";

how i join third table passengers and count() all records, i need to subtract bus.seats_max with passengers total rows. I hope someone has solutions i failed on this for last hour!


Source: stackoverflow-php

Laravel/Eloquent mutate model on join

I’m trying to mutate a model to display an array of strings and not an array of objects.

Current Output

{
  "id": 1,
  "company_name": "blah"
  "language": [
    {
      "name": "English"
    },
    {
      "name": "French"
    }
  ] 
}

Desired Output

 {
  "id": 1,
  "company_name": "blah"
  "language": ["English","French"]
}

Partner Model

class Partner extends Eloquent
{

   protected $table = 'property_managers';
   protected $visible = array('id','company_name','language');

    public function language(){
       return $this->belongsToMany('Language', 'property_manager_language', 'property_manager_id', 'language_id');
    }

}

Language Model

class Language extends Eloquent
{
    protected $table = 'languages';
    protected $visible = array('name');

    public function propertyManager()
    {
      return $this->belongsToMany('PropertyManager');
    }
}

I’m accessing it via the code snippet below

$pm = Partner::with('language')->get()->first();

I looked into mutators but it doesn’t seem to hit that attribute within the partner model.

public function getLanguageAttribute(){
   //run my code to flatten to array
}

So I’m curious as to if mutators don’t work on joins or how you would be able to modify the return on the join. I can add a mutator to the Language model but that will only modify the attributes of that specific model. Any insight would be helpful. Thanks


Source: stackoverflow-php