Validation while defining a join

Question

I'm trying to define a validation, to include only users that meet specific criteria - mainly, to take into account users whose metadata (that is stored in another table) has field supplier_id with specific value. As such, I've done this in UpdateXRequest class:

public function rules()
{
     $journey = $this->route()->parameter('journey');

     return ['driver_id' => [
          Rule::exists('users', 'id')
               ->where(function($query) use ($journey){
                     $query->join('users_meta', 'users.id', '=', 'users_meta.user_id')
                         ->where('users_meta.key', 'supplier_id')
                         ->where('users_meta.value', $journey->supplier_id);
               })
          ]
     ];
}

However, I'm getting Column not found: 1054 Unknown column 'users_meta.key' in 'where clause' (SQL: select count(*) as aggregate from users where id = x and (users_meta.key = supplier_id and users_meta.value = y)) error.

How can I accomplish this?

---

I'm looking for answers that use the default Laravel logic, rather than writing my own validator

---

Per request, here're the schema queries:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `role_id` int(10) unsigned NOT NULL
);

CREATE TABLE IF NOT EXISTS `users_meta` (
  `id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'null',
  `key` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `value` text COLLATE utf8_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
);

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `users_email_unique` (`email`);

ALTER TABLE `users`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT;

ALTER TABLE `users_meta`
  ADD PRIMARY KEY (`id`),
  ADD KEY `users_meta_user_id_index` (`user_id`),
  ADD KEY `users_meta_key_index` (`key`);

ALTER TABLE `users_meta`
  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT;

INSERT INTO `users` (`id`, `email`, `password`, `created_at`, `updated_at`, `role_id`) VALUES (6898, 'a@b.com', '', '2017-08-09 12:15:05', '2017-08-09 13:19:56', 4);

INSERT INTO `users_meta` (`id`, `user_id`, `type`, `key`, `value`, `created_at`, `updated_at`) VALUES (18, 6898, 'string', 'supplier_id', '6897', '2017-08-09 12:15:05', '2017-08-09 12:15:05');

---

The query existed in OP, but per request added SQL query that is produced taken from the query log:

select count(*) as aggregate from `users` where `id` = 7011 and (`users_meta`.`key` = supplier_id and `users_meta`.`value` = 6897)

---

Thinking more about it, it all makes perfect sense why it's not working - the internal join doesn't have any conditions on it (it's still weird that it doesn't appear in query log - maybe it's an optimisation done by Laravel to not unnecessarily join tables for where as opposed to select). Internal wheres at this moment are run on users, and not on the table from the join - and per https://laravel.com/docs/5.4/queries#joins (Advanced Join Clauses), they need to be inside join for it to work (per @DarkMukke's answer)

Answer 1

Not a 100% sure, but logically it sounds like the where conditions on your join should be in a Closure

public function rules()
{
    $journey = $this->route()->parameter('journey');

    return ['driver_id' => [
        Rule::exists('users', 'id')
            ->where(function($query) use ($journey){
                $query->join('users_meta', function ($join) use ($journey) {
                    $join->on('users.id', '=', 'users_meta.user_id')
                        ->where('users_meta.key', 'supplier_id')
                        ->where('users_meta.value', $journey->supplier_id);
                });
            })
        ]
    ];
}

And even then, you might not need the first closure, eg I was wrong, the where relates to the condition of the Rule, not the where condition of a query builder.

EDIT : I've not tried this code, but from my experience, and some double checking in the docs, this should be correct.

Additionally, since the conditions are all on the join, you could make it faster by joining less data, by adding conditions instead

public function rules()
{
    $journey = $this->route()->parameter('journey');

    return [
        'driver_id' => [
            Rule::exists('users', 'id')
                ->where(function ($query) use ($journey) {
                    $query->join('users_meta', function ($join) use ($journey) {
                        $join->on('users.id', '=', 'users_meta.user_id')
                            ->on('users_meta.key', '=', 'supplier_id')
                            ->on('users_meta.value', '=', $journey->supplier_id);
                    });
                })
        ]
    ];
}