doushi5117 2014-06-27 08:14
浏览 23
已采纳

MySQL嵌套资源(数据透视表)查看/更新/管理权限

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;
    }
}
  • 写回答

3条回答 默认 最新

  • dry69034 2014-07-03 21:37
    关注

    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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 想问一下stata17中这段代码哪里有问题呀
  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决