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 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥20 Python安装cvxpy库出问题
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥15 python天天向上类似问题,但没有清零
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 C#调用python代码(python带有库)
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题