Need to 'convert' an MySQL-query to Eloquent; where to start / how to think?

I'd like to 'convert' a raw SQL-query to Eloquent, so I can have eager loaded models attached too, so I don't have to edit some templates I got. Problem is, the query got some subqueries and I do not know how to 'convert' the query into Eloquent's format. The query in question is:

SELECT e_eh.id, s.name as serie, s.id as serie_id, e_eh.season, e_eh.episode, e_eh.name, eh1.prog_trans, eh1.prog_check, eh1.prog_sync, eh1.avi FROM ( SELECT e.* , ( SELECT eh.id FROM episode_histories AS eh WHERE 1 AND eh.episode_id = e.id ORDER BY eh.id DESC LIMIT 1 ) AS eh_id FROM episodes AS e WHERE 1 AND e.completed = 0 AND e.deleted_at IS NULL ) AS e_eh INNER JOIN episode_histories AS eh1 ON e_eh.eh_id = eh1.id INNER JOIN series as s ON s.id = e_eh.serie_id ORDER BY prog_trans DESC, prog_check DESC, prog_sync DESC

I've tried a few things already, but none have worked. I'm a bit stuck in how to "think" this into Laravel / Eloquent. Documentation from Laravel itself is also not much helpful.

In a nutshell: I've got two models, one is episodes, other is episode_histories, whichs stores some history on related episode. A third model is the show model, the related show for it. I need to get an episode, with related show model (is a relation in my model already). but I also need to get the latest episode_histories model for given episode.

What I currently have in my models:

Episode: `class Episode extends Model { use SoftDeletes; use App\History; // The history model

protected $table        = 'episodes';
protected $primaryKey   = 'id';
public    $timestamps   = true;

/**
 * The attributes that should be mutated to dates.
 *
 * @var array
 */
protected $dates = ['deleted_at'];

/* Eloquent relations */
public function show() {
    return $this->belongsTo('App\Serie', 'serie_id', 'id');
}

public function history() {
    return $this->hasMany('App\History', 'episode_id', 'id')->orderBy('id', 'desc');
}

public static function getEpisodes2() {
    return DB::select();
}

}And my history model looks like this: class History extends Model { use SoftDeletes;

protected $table        = 'episode_histories';
protected $primaryKey   = 'id';
public    $timestamps   = true;

/**
 * The attributes that should be mutated to dates.
 *
 * @var array
 */
protected $dates = ['deleted_at'];

/* Eloquent relations */
public function episode() {
    return $this->belongsTo('App\Episode');
}

public function user() {
    return $this->belongsTo('App\User', 'user_id', 'id');
}

/* Custom functions */

}` I hope someone can help me out on this.

via Chebli Mohamed