Is it possible to translate this "join" and "as" query to Laravel PDO functions?

Question

I am struggling to make this SQL query to a Laravel PDO query. Does anyone know how I can do this? (Especially the 'AS' statements and the naming in the joins).

Query:

SELECT models.name         as model_name,
       models.brand        as model_brand,
       t.name              as trim_name,
       t.extra_information as trim_extra_information,
       t.price             as trim_price,
       t.popular           as trim_is_popular,
       s.type              as specification_type,
       s.value             as specification_value,
       o.name              as option_name,
       o.default           as option_default,
       o.remaining         as option_remaining,
       c.name              as color_name,
       c.hex_code          as color_hex_code,
       c.price_extra       as color_price_extra,
       ll.months           as lease_length_months,
       ll.default          as lease_length_default,
       ll.price_extra      as lease_length_price_extra,
       eo.name             as extra_option_name,
       eo.description      as extra_option_description,
       eo.price_total      as extra_option_price_total,
       eo.price_extra      as extra_option_price_extra,
       m.kilometers        as mileage_kilometers,
       m.default           as mileage_default,
       m.price_extra       as mileage_price_extra,
       m.price_extra_km    as mileage_price_extra_km

FROM `models`
         INNER JOIN trims t on models.id = t.model_id
         INNER JOIN specifications s on t.id = s.trim_id
         INNER JOIN options o on t.id = o.trim_id
         INNER JOIN colors c on t.id = c.trim_id
         INNER JOIN lease_lengths ll on t.id = ll.trim_id
         INNER JOIN extra_options eo on ll.id = eo.lease_length_id
         INNER JOIN mileages m on ll.id = m.lease_length_id

Just use a raw query. But `'AS'` is your least problem - There is nothing special about it in laravel. — Paul Spiegel, Feb 27 '20 at 14:15
@PaulSpiegel Raw Query is not that good if I want it to be maintainable by my team. So you are saying this is possible with Laravel functions? — Niels Bosman, Feb 27 '20 at 14:17
directly use `select('models.name as model_name', '....', '...')` and use `join()` — TsaiKoga, Feb 27 '20 at 14:18

score 2 · Accepted Answer · answered Feb 27 '20 at 14:24

Directly use join() and select() method like this:

Model::join('trims t', 'models.id', '=', 't.model_id')
     ->join('specifications s', 't.id', '=', 's.trim_id')
     ...
     ->select(
       'models.name         as model_name',
       'models.brand        as model_brand',
       't.name              as trim_name',
       't.extra_information as trim_extra_information',
       't.price             as trim_price',
       't.popular           as trim_is_popular',
       ...
     )
     ->get()

score 0 · Answer 2 · answered Feb 27 '20 at 14:25

If you're using an eloquent model class called Model it is automatically set to query the models table.

Then you can override the default query in your Model class using:

public function newQuery()
{
    return parent::newQuery()
        ->select(
            'models.name as model_name',
            ...
            'mileages.price_extra_km as mileage_price_extra_km'
        )
        ->join('trims', 'models.id', 'trims.model_id')
        ...
        ->join('mileages', 'lease_lengths.id', 'mileages.lease_length_id');

Fill in the extra lines where I've left triple dots and you should be on your way.

Notice I've dropped your table aliases. You can alias your tables as in the SQL query code but it's simpler and more readable not to bother when using the eloquent query builder.

Is it possible to translate this "join" and "as" query to Laravel PDO functions?

2 Answers2