SQL Query with inner join of Select to Laravel Eloquent

Is it possible to convert to Eloquent a INNER JOIN of a SELECT or will I have to use DB Builder? Seems hard enough for me ...

The query I'm trying to convert

SELECT V.idStart, F.id, F.nome
FROM familias F
INNER JOIN (
     SELECT CA.idStart, SUBSTRING(idStart, 1, LENGTH(idStart)-3 ) AS idFam
    FROM afirmacoes CA
    WHERE CA.idStart IS NOT NULL
) AS V
ON F.id = V.idFam

My models are Afirmacao and Familia

I was trying to start with

   $results = Afirmacao::whereNotNull('idStart')->pluck('idStart');

And now I was going to do a foreach to the Familia model but that would result in more than 200 queries whn this can be done with just one (I guess)

I don't know if this is even possible. Any help is greatly appreciated.

mvd

7 years ago

Level 48

@penaf

You can create joins in the model. https://laravel.com/docs/5.8/eloquent-relationships#one-to-many

Something like in your familias model

public function afirmacoes()
    {
        return $this->hasMany('App\Afirmacoes', 'idFam', 'id')->where('idStart', '<>', '');
    }

And your query.

$result = \App\familias->with('afirmacoes');

Not tested but hope you know how to make joins

Please or to participate in this conversation.