我有一个原始查询，工作得很好，但我不能把它翻译成laravel雄辩…
这是我的表格:
表
Schema::create('users', function(Blueprint $table)
{
$table->increments('id');
$table->string('username', 30)->unique();
$table->string('email')->unique();
$table->string('password', 60);
$table->integer('role_id')->unsigned();
$table->boolean('seen')->default(false);
$table->boolean('valid')->default(false);
$table->boolean('confirmed')->default(false);
$table->string('confirmation_code')->nullable();
$table->timestamps();
$table->rememberToken();
});
表
Schema::create('clients', function(Blueprint $table)
{
$table->increments('id');
$table->integer('id_marchand')->unsigned()->index();
$table->foreign('id_marchand')->references('id')->on('users')->onDelete('cascade')->onUpdate('restrict');
$table->integer('id_client')->unsigned()->index();
$table->foreign('id_client')->references('id')->on('users')->onDelete('cascade')->onUpdate('restrict');
$table->timestamps();
});
表
Schema::create('employes', function(Blueprint $table)
{
$table->increments('id');
$table->integer('id_marchand')->unsigned()->index();
$table->foreign('id_marchand')->references('id')->on('users')->onDelete('cascade')->onUpdate('restrict');
$table->integer('id_employe')->unsigned()->index();
$table->foreign('id_employe')->references('id')->on('users')->onDelete('cascade')->onUpdate('restrict');
$table->timestamps();
});
<?php namespace App'Models;
/**
* One to Many relation
*
* @return Illuminate'Database'Eloquent'Relations'hasMany
*/
public function employes()
{
return $this->hasMany('App'Models'Employe', 'id_marchand');
}
/**
* One to Many relation
*
* @return Illuminate'Database'Eloquent'Relations'hasMany
*/
public function clients()
{
return $this->hasMany('App'Models'Client', 'id_marchand');
}
<?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->belongsTo('App'Models'User');
}
}
<?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->belongsTo('App'Models'User');
}
}
我试图翻译的原始查询:
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
我的问题是:
- 如果我尝试用原始查询，通过
DB::raw()，它返回一个 - 我找不到一种方法来做一个'选择'从几个表雄辩
- 我不知道如何从数组中提取数据并获得一个集合
- 我仍然不确定我做的是否正确。
那么有什么方法可以让它工作吗?
编辑:
要清楚，我想要得到的是:
的用户,包含:
- 用户8的"客户"用户
- 用户8的雇员
- 用户8。
我可以在上面应用->oldest('seen')->latest()->paginate($n)或者类似的东西
看起来你的员工模型设置正确，这应该使这相当简单…
我认为考虑一下你想做什么以及Eloquent如何帮助你做到这一点比简单地尝试转换一个查询来使用查询生成器更容易。
$id = 8;
$users = App'User::whereHas('clients', function($q) use ($id) {
$q->where('id_marchand', $id);
})->orWhereHas('employes', function($q) use ($id) {
$q->where('id_marchand', $id);
})->orWhere('id', $id)
->orderBy('seen')
->oldest()
->get();
这将返回User模型的集合。如果您想分页，只需将get()替换为paginate($numRecords)，其中$numRecords是您希望每页的记录数。
然后使用该模型集合，可以使用foreach循环输出每个用户的数据....
foreach ($users as $user) {
echo 'email: ' . $user->email;
}
id_client和id_employe连接客户端和雇员表。因此，如果您修改User模型，并将id_marchand更改为id_employe用于employes函数，将id_client更改为clients函数，则此代码应该可以工作(或至少对我来说是这样)。
只是为了澄清，上面的代码生成了以下查询，因此您可以在进行任何更改之前自己查看结果…
SELECT
*
FROM
`users`
WHERE EXISTS
(SELECT
*
FROM
`clients`
WHERE `clients`.`id_client` = `users`.`id`
AND `id_marchand` = '8')
OR EXISTS
(SELECT
*
FROM
`employes`
WHERE `employes`.`id_employe` = `users`.`id`
AND `id_marchand` = '8')
OR `id` = '8'
ORDER BY `seen` ASC,
`created_at` ASC