Translating Raw sql query to laravel eloquent query
<p>I have a raw query which works fine, but I can't translate it to laravel eloquent...</p>

<p>Here are my tables :</p>

<h2><strong>users</strong> table</h2>

<pre><code>Schema::create('users', function(Blueprint $table)
    {
        $table-&gt;increments('id');
        $table-&gt;string('username', 30)-&gt;unique();
        $table-&gt;string('email')-&gt;unique();
        $table-&gt;string('password', 60);
        $table-&gt;integer('role_id')-&gt;unsigned();
        $table-&gt;boolean('seen')-&gt;default(false);
        $table-&gt;boolean('valid')-&gt;default(false);
        $table-&gt;boolean('confirmed')-&gt;default(false);
        $table-&gt;string('confirmation_code')-&gt;nullable();
        $table-&gt;timestamps();
        $table-&gt;rememberToken();
    });
</code></pre>

<h2><strong>clients</strong> table</h2>

<pre><code>Schema::create('clients', function(Blueprint $table)
    {
        $table-&gt;increments('id');
        $table-&gt;integer('id_marchand')-&gt;unsigned()-&gt;index();
        $table-&gt;foreign('id_marchand')-&gt;references('id')-&gt;on('users')-&gt;onDelete('cascade')-&gt;onUpdate('restrict');
        $table-&gt;integer('id_client')-&gt;unsigned()-&gt;index();
        $table-&gt;foreign('id_client')-&gt;references('id')-&gt;on('users')-&gt;onDelete('cascade')-&gt;onUpdate('restrict');
        $table-&gt;timestamps();

    });
</code></pre>

<h2><strong>employes</strong> table</h2>

<pre><code>Schema::create('employes', function(Blueprint $table)
    {
        $table-&gt;increments('id');
        $table-&gt;integer('id_marchand')-&gt;unsigned()-&gt;index();
        $table-&gt;foreign('id_marchand')-&gt;references('id')-&gt;on('users')-&gt;onDelete('cascade')-&gt;onUpdate('restrict');
        $table-&gt;integer('id_employe')-&gt;unsigned()-&gt;index();
        $table-&gt;foreign('id_employe')-&gt;references('id')-&gt;on('users')-&gt;onDelete('cascade')-&gt;onUpdate('restrict');
        $table-&gt;timestamps();

    });
</code></pre>

<h2>User Model</h2>

<pre><code>&lt;?php namespace App\Models;

/**
 * One to Many relation
 *
 * @return Illuminate\Database\Eloquent\Relations\hasMany
 */
public function employes()
{
    return $this-&gt;hasMany('App\Models\Employe', 'id_marchand');
}

/**
 * One to Many relation
 *
 * @return Illuminate\Database\Eloquent\Relations\hasMany
 */
public function clients()
{
    return $this-&gt;hasMany('App\Models\Client', 'id_marchand');
}
</code></pre>

<h2>Client Model</h2>

<pre><code>&lt;?php namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Client extends Model
{
    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'clients';

    /**
     * One to Many relation
     *
     * @return Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function user() 
    {
        return $this-&gt;belongsTo('App\Models\User');
    }
}
</code></pre>

<h2>Employe Model</h2>

<pre><code>&lt;?php namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Employe extends Model
{
    /**
     * The database table used by the model.
     *
     * @var string
     */
    protected $table = 'employes';

    /**
     * One to Many relation
     *
     * @return Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function user() 
    {
        return $this-&gt;belongsTo('App\Models\User');
    }
}
</code></pre>

<h2>The raw query I'm trying to translate :</h2>

<pre><code>SELECT users.* 
FROM clients, users 
WHERE clients.id_marchand = 8 
AND users.id = clients.id_client 
UNION 
SELECT users.* 
FROM employes, users 
WHERE employes.id_marchand = 8 
AND users.id = employes.id_employe 
UNION 
SELECT users.*
FROM users
WHERE users.id = 8
ORDER BY `seen` ASC, `created_at` DESC 
LIMIT 25 OFFSET 0
</code></pre>

<h2>My problems are:</h2>

<ol>
<li>If I try to do it with a raw query, via <code>DB::raw()</code>, it returns an
array, and I can't paginate or sort results then.</li>
<li>I can't find a way to do a 'select' from several tables in Eloquent</li>
<li>I don't know how to extract datas from the array and get a Collection</li>
<li>I am still not sure if I'm doing it right or not.</li>
</ol>

<p>So is there a way or an other to make that work ?</p>

<hr>

<h2>EDIT:</h2>

<p>To be clear, what I'm trying to get is:</p>

<p>A <strong>Collection</strong> of <strong>Users</strong>, containing:</p>

<ul>
<li>Users who are 'clients' of User 8</li>
<li>Users who are 'employes' of User 8</li>
<li>User 8.</li>
</ul>

<p>on which I can apply <code>-&gt;oldest('seen')-&gt;latest()-&gt;paginate($n)</code> or something equivalent.</p>