How To Run Mysql Query In Laravel

I want to transform my MySql query into a Query in Laravel but I really don't know how to do this. I don't know how to rename in FROM like in SQL

My query is the following one :

SELECT f2.* FROM formation f2 WHERE f2.theme_id IN 
(SELECT f.theme_id FROM user_formation uf JOIN formation f ON uf.formation_id = f.id WHERE uf.user_id = 2) 
AND f2.id NOT IN 
(SELECT formation_id FROM user_formation WHERE user_id = 2);

I tried something like this but ...

$q = Formation::query()
                ->from('formation AS f2')
                ->whereIn('f2.theme_id', function($r)
                {
                    $r->select('f.theme_id')->from('user_formation AS uf')
                    ->join('formation', function($join)
                    {
                        $join->on('uf.formation_id', '=', 'f.id')
                        ->where ('uf.user_id', '=', $id)
                    });
                });

            ->whereNotIn('f2.id', function($s){
                $s->select('formation.id')
                ->from('user_formation')
                ->where('user_id', '=', $id)
            })->get();

thanks for help.

If you want to run this raw query you can run:

$res = DB::select('
  SELECT f2.* 
  FROM formation f2 
  WHERE f2.theme_id IN 
    (SELECT f.theme_id FROM user_formation uf JOIN formation f ON uf.formation_id = f.id WHERE uf.user_id = 2) 
    AND f2.id NOT IN 
    (SELECT formation_id FROM user_formation WHERE user_id = 2)');

Or you can rewrite this query in laravel query builder Eloquent ORM:

Formations::query()
  ->whereIn('formations.theme_id', function($q){
      $user_formations_table = (new UserFormation)->getTable();
      $formation_table = (new Formation)->getTable();
      $q->select('paper_type_id')
        ->from($user_formations_table)
        ->join($formation_table, "$user_formations_table.formation_id", '=', "$formation_table.id")
        ->where("$user_formations_table.user_id", 2);
  })->whereNotIn('formations.id', function($q){
      $user_formations_table = (new UserFormation)->getTable();
      $q->select('formation_id')
        ->where("$user_formations_table.user_id", 2);
  })
  ->get();

Note that I have used models Formations, UserFormation, Formation Because you have used 3 different tables, you should add this models and specify tables to run ORM query

I advice to run first RAW query if there is no another need to run it with Eloquent

Hope this helps you