php - Laravel Eloquent ManyToMany with Pivot，带有数据透视表中变量的辅助查询？

Question

我一直在玩 laravel，遇到了一个奇怪的边缘案例，我不太明白

我有以下表结构：

CREATE TABLE `community_address` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `address_id` int(10) unsigned NOT NULL,
  `community_id` int(10) unsigned NOT NULL,
  `is_billing` tinyint(1) NOT NULL DEFAULT '1',
  `is_service` tinyint(1) NOT NULL DEFAULT '1',
  `is_mailing` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
)

CREATE TABLE `communities` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
)

CREATE TABLE `addresses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `address_1` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Street address',
  `address_2` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Street adddress 2 (Company name, Suite, etc)',
  `city` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'City',
  `state` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'State / Province',
  `zip` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Zip / Postal Code',
  `country_id` int(10) unsigned NOT NULL COMMENT 'Country ID',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
)

我用以下 Laravel 模型代表了一个社区

class Community extends Model
{
    public function addresses(){
        return $this->belongsToMany(Address::class, 'community_address', 'community_id',  'address_id');
    }
}

$community->addresses() 实际上只返回社区的地址，但是说我想在我的数据透视表中按地址类型进行过滤（账单、邮寄等）

我可以试试这个：

   public function getBillingAddress(){
        return $this->addresses()->wherePivot('is_billing','=', true)->firstOrFail()->get();
    }

确实会返回结果，但是它是我的数据透视表中与我的查询匹配的每一行，而不是从现有地址运行我的查询

所以我的第二个想法是像这样使用“和”布尔参数

 public function getBillingAddress(){
        return $this->addresses()->wherePivot('community_id', '=', $this->id, true)->wherePivot('is_billing','=', true)->firstOrFail()->get();
    }

这会导致以下 SQL 出错（出于明显的原因），但它看起来也不像是在搜索我想要的东西，即使它确实有效？

select `addresses`.*, `community_address`.`community_id` as `pivot_community_id`, `community_address`.`address_id` as `pivot_address_id` from `addresses` inner join `community_address` on `addresses`.`id` = `community_address`.`address_id` where `community_address`.`community_id` = 2 1 `community_address`.`community_id` = 2 and `community_address`.`is_billing` = 1 limit 1 

在我看来，“and”值实际上不是布尔值，而是将值作为字符串直接打印到查询中。

我尝试了显而易见的方法，并尝试将第四个参数与“and”交换，并生成了以下 sql，它不会失败，但会返回所有地址，而不仅仅是链接到我的社区的地址

select `addresses`.*, `community_address`.`community_id` as `pivot_community_id`, `community_address`.`address_id` as `pivot_address_id` from `addresses` inner join `community_address` on `addresses`.`id` = `community_address`.`address_id` where `community_address`.`community_id` = 2 and `community_address`.`community_id` = 2 and `community_address`.`is_billing` = 1 limit 1)

我在这里遗漏了一些明显的东西吗？

通过对结果 SQL 进行一些修改，我可以得到我想要的，即以下原始 sql 查询：

select `addresses`.*,
       `community_address`.`community_id` as `pivot_community_id`,
       `community_address`.`address_id`   as `pivot_address_id`
from `addresses`
       inner join `community_address` on `addresses`.`id` = `community_address`.`address_id` and `community_address`.`community_id` = 2 and `community_address`.`is_billing` = 1
limit 1

如何通过 eloquent 为我生成相同的 SQL？

标签： phplaraveleloquent

Answer 1

我认为这对你来说是有用的如果我想出一个例子，我们有用户 角色和角色用户表，我们已经将用户连接到角色，属于很多，我们想要使用选择：

用户型号：

function Roles()
{
 return $this->belongsToMany('App\Role', 'role_user', 'user_id', 'role_id');
}

在我们的控制器中，我们可以编写任何选择，如下所示：

class exampleController extends Controller
{
public function index()
{
User::with(['Roles'=>function($query){$query->where(....)->get();}])->get(); 
}

}

您可以在查询中使用任何选择并返回您想要的任何内容。

如果您需要在选择中使用任何变量，请务必小心，您必须使用以下格式

class exampleController extends Controller
{
public function index()
{
$var =...;
User::with(['Roles'=>function($query) use ($var){$query->where(....,$var)->get();}])->get(); 
}

}

我希望这能解决你的问题......