你如何在laravel雄辩中查询孩子和父母

Question

I want to figure out how to query to get a result that was either created by the current user or the current users parent.

I have two tables users and workouts. Both tables have a created_by column, which stores the user id of whoever created said user or workout record.

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_by` int(10) unsigned DEFAULT NULL,
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `users_created_by_foreign` (`created_by`),
  CONSTRAINT `users_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
);

CREATE TABLE `workouts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_by` int(10) unsigned NOT NULL,
  `description` text COLLATE utf8_unicode_ci,
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `workouts_created_by_foreign` (`created_by`),
  CONSTRAINT `workouts_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
);

In my user model I have a function to return all the workouts that the user has created.

public function createdWorkouts(): HasMany
{
    return $this->hasMany(Workout::class, 'created_by');
}

What I want to figure out is, how can I query to find a workout using a ID but only for workouts the current user has created or the creator of the current user has created. Below is my current route logic, which only returns a workout created by the current user.

$user = User::find(1); // This would come from the JWT
if (!$workout = $user->createdWorkouts()->find($args[‘workout_id’])) {
    return $response->withJson([], 404);
}

Answer 1

For this instance, I probably wouldn't use an eloquent relationship as it could be either the users id or their created_by value.

Instead, I would change the method on your user model to read:

public function createdWorkouts()
{
    return Workout::where('created_by', $this->id)->orWhere('created_by', $this->created_by)->get();
}

Then you can do $user->createdWorkouts() which will return you a collection of all workouts where it was created by the user, or created by the user that created the user.

As it is a collection, you then have access to all of the other methods on a collection such as find etc