MySQL nested resources (pivot tables) permission to view/update/manage

Question

users                   transactions                    tasks
+----+--------+         +----+---------------+          +----+--------+
| id |  name  |         | id |     name      |          | id |  name  |
+----+--------+         +----+---------------+          +----+--------+
|  1 | User 1 |         |  1 | Transaction 1 |          |  1 | Task 1 |
|  2 | User 2 |         |  2 | Transaction 2 |          |  2 | Task 2 |
+----+--------+         +----+---------------+          +----+--------+


templates                   transaction_user                    task_transaction                  
+----+---------------+      +---------+----------------+        +---------+----------------+      
| id |     name      |      | user_id | transaction_id |        | task_id | transaction_id |
+----+---------------+      +---------+----------------+        +---------+----------------+
|  1 | Template 1    |      |       1 |              1 |        |       1 |              1 |
|  2 | Template 2    |      |       2 |              2 |        +---------+----------------+
+----+---------------+      +---------+----------------+            


task_template
+---------+-------------+
| task_id | template_id |
+---------+-------------+
|       2 |           2 |
+---------+-------------+

Motive: If there is a logged in user, say user with the ID 1, and he/she wants to see a task (say task with the ID 1) then i want to make sure that the task with ID 1 Belongs to the user before i let him view it. Also i need someway to show user all tasks that belong to him. Task is just one model.. i need to handle this for all models. I have shared my code below, am i trying too hard?

I may have omitted some details here so please feel free to ask questions. Thanks.

Code

<?php namespace SomeProject\Repositories;

use User;
use Account;
use Task;
use Document;
use Transaction;
use Property;
use DB;
use Respond;

abstract class DbRepository
{

/**
 * The many to many relationships are handeled using pivot tables
 * We will use this array to figure out relationships and then get
 * a particular resource's owner / account
 */
public $pivot_models = array(

    'Task'          => array(
                        'Transaction'   => 'task_transaction'
                    ),

    'Transaction'   => array(
                        'User'  => 'transaction_user'
                    ),

    'Document'      => array(
                        'Property'      => 'document_property',
                        'Task'          => 'document_task',
                        'Message'       => 'document_message'
                    )
);

public $entity_ids;


public function getOwnersByEntity(array $ids, $entity)
    {
        $this->entity_ids = [];

        $user_ids = [];


        $entity = ucfirst(strtolower($entity)); // arrays keys are case sensitive

        if( $this->getPivotIds($ids, $entity) )
        {
            foreach ($this->entity_ids as $entity_name => $entity_ids_arr)
            {
                $entity_name_lowercase = strtolower($entity_name);

                if($entity_name_lowercase != 'user')
                {
                    $user_ids_from_entity = $entity_name::whereIn('id', $entity_ids_arr)
                                                ->lists('user_id');
                }
                else
                {
                    // We already have the IDs if the entity is User
                    $user_ids_from_entity = $entity_ids_arr;
                }

                array_push($user_ids, $user_ids_from_entity);

            }

            $merged_user_ids = call_user_func_array('array_merge', $user_ids);

            return array_unique($merged_user_ids);
        }
        else
        {
            return $entity::whereIn('id', $ids)->lists('user_id');
        }
    }


    public function getPivotIds(array $ids, $entity)
    {
        $entity_lowercase = strtolower($entity);

        if( array_key_exists($entity, $this->pivot_models) )
        {
            // Its a pivot model

            foreach ($this->pivot_models[$entity] as $related_model => $table) // Transaction, Template
            {
                $related_model_lowercase = strtolower($related_model);

                $this->entity_ids[$related_model] = DB::table($table)
                                                        ->whereIn($entity_lowercase . '_id', $ids)
                                                        ->lists($related_model_lowercase . '_id');

                if( $this->getPivotIds($this->entity_ids[$related_model], $related_model) )
                {
                    unset($this->entity_ids[$related_model]);
                }
            }

            return true;
        }

        return false;
    }
}

Answer 1

To check if given model is related to another one, which is what you want if I get you right, all you need is this tiny method making the most of Eloquent:

(Implement it in BaseModel, Entity or a scope, whatever suits you)

// usage
$task->isRelatedTo('transactions.users', $id);
// or
$template->isRelatedTo('tasks.transactions.users', Auth::user());

// or any kind of relation:
// imagine this: User m-m Transaction 1-m Item m-1 Group
$group->isRelatedTo('items.transaction.users', $id);

The magic happens here:

/**
 * Check if it is related to any given model through dot nested relations
 * 
 * @param  string  $relations
 * @param  int|\Illuminate\Database\Eloquent\Model  $id
 * @return boolean
 */
public function isRelatedTo($relations, $id)
{
    $relations = explode('.', $relations);

    if ($id instanceof Model)
    {
        $related = $id;
        $id = $related->getKey();
    }
    else
    {
        $related = $this->getNestedRelated($relations);
    }

    // recursive closure
    $callback = function ($q) use (&$callback, &$relations, $related, $id) 
    {
        if (count($relations))
        {
            $q->whereHas(array_shift($relations), $callback);
        }
        else
        {
            $q->where($related->getQualifiedKeyName(), $id);
        }
    };

    return (bool) $this->whereHas(array_shift($relations), $callback)->find($this->getKey());
}

protected function getNestedRelated(array $relations)
{
    $models = [];

    foreach ($relations as $key => $relation)
    {
        $parent = ($key) ? $models[$key-1] : $this;
        $models[] = $parent->{$relation}()->getRelated();
    }

    return end($models);
}

---

Hey, but what's going on there?

isRelatedTo() works like this:

1. check if passed $id is a model or just an id, and prepares $related model and its $id for use in the callback. If you don't pass an object then Eloquent needs to instantiate all the related models on the $relations (relation1.relation2.relation3...) chain to get the one we are interested in - that's what happens in getNestedRelated(), pretty straightforward.

2. then we need to do something like this:

   // assuming relations 'relation1.relation2.relation3'
   $this->whereHas('relation1', function ($q) use ($id) {
      $q->whereHas('relation2', function ($q) use ($id) {
         $q->whereHas('relation3', function ($q) use ($id) {
            $q->where('id', $id);
         });
      });
   })->find($this->getKey()); 
   // returns new instance of current model or null, thus cast to (bool)
   

3. since we don't know how deeply the relation is nested, we need to use recurrency. However we pass a Closure to the whereHas, so we need to use little trick in order to call itself inside its body (in fact we don't call it, but rather pass it as $callback to the whereHas method, since the latter expects a Closure as 2nd param) - this might be useful for those unfamiliar Anonymous recursive PHP functions:

   // save it to the variable and pass it by reference
   $callback = function () use (&$callback) {
     if (...) // call the $callback again
     else // finish;
   }
   

4. we also pass to the closure $relations (as an array now) by reference in order to unshift its elements, and when we got them all (meaning we nested whereHas), we finally put the where clause instead of another whereHas, to search for our $related model.

5. finally let's return bool

Answer 2

There's really no easy nor canonical way, but here's a raw example of what I'd try to do.

class Entity extends Eloquent {

    public function isRelatedTo($instance, $through)
    {
        $column = $instance->joiningTable($through) . '.' . $instance->getForeignKey();
        $query = DB::table('');
        this->buildJoin($query, $instance, $through);
        return $query->where($column, '=', $instance->getKey())->exists();
    }

    public function relatesToMany($related, $through)
    {
        $that = $this;
        $related = new $related;
        return $related->whereIn($related->getKeyName(), function($query) use ($that, $related, $through) {
            $that->buildJoin($query, $related, $through);
        })->get();
    }

    protected function buildJoin($query, $related, $through)
    {
        $through = new $through;
        $this_id = $this->getForeignKey();
        $related_id = $related->getForeignKey();
        $through_id = $through->getForeignKey();
        $this_pivot = $this->joiningTable($through);
        $related_pivot = $related->joiningTable($through);
        $query->select($related_pivot . '.' . $related_id)->from($related_pivot)
            ->join($this_pivot, $related_pivot . '.' . $through_id, '=', $this_pivot . '.' . $through_id)
            ->where($this_pivot . '.' . $this_id, '=', $this->getKey());
    }

}

Then, for your use case:

class User extends Entity {

    public function isOwnerOf($task)
    {
        return $this->isRelatedTo($task, 'Transaction');
    }

    public function tasks()
    {
        return $this->relatesToMany('Task', 'Transaction');
    }

}

Disclaimer: the code has not been tested.

Note that, in this very simplified example, relatesToMany directly returns a Collection. To have more advantages, it could instead return an instance of your own extension of Eloquent's Relation class - that takes longer to implement, clearly.
It shouldn't be difficult to add support for multiple intermediate entities; you could likely expect the $through argument to possibly be an array and then build the multi-join query accordingly.

Answer 3

If this is a Laravel project, then yes, you're trying far too hard.

If you're going to use Laravel, it's recommended that you use the features provided to you with Laravel, which are namely it's ORM, Eloquent, and it's bundled Schema tool. I'd recommend that you view Laravel's Getting Started page in their documentation, so that you can set your project up correctly to use Eloquent.

It would also be beneficial if you read up on the basics of how Eloquent handles relations in their models, as they do all of the work that you're trying to do.