首页 文章

Laravel查询多个雄辩的关系

提问于
浏览
0

嗨,我试图查询表之间的数据库中的关系 . 快速浏览我的应用程序,我有 userstasksprojectstasksstatuses 和优先级 . 目前我想返回auth用户项目及其客户端名称和状态名称,可以使用db表中的相关id来检索 .

我几乎正确地返回所有这些,例如,如果我用项目和客户端查询Auth用户我返回了正确的数据,我在repo中查询,并让我的控制器使用它:

public function getUserProjectswithClients()
    {
        return \User::with(array('projects', 'projects.clients'))
                            ->find(Auth::user()->id); 
    }

用户控制器

public function profile() {
 //returning users projects
    $data["usersprojects"] = $usersprojects = $this->userrepo->getUserProjectswithClients();
//  return $usersprojects;
  if(Request::ajax())
            {
              // --- this part of the code is odd
              $html = View::make('projects.openprojects ', $data)->render();
              return Response::json(array('html' => $html));

              // ---
            } else {


              $data = array(
                 'usersprojects' => $usersprojects
              );

              return View::make('users.profile', $data);
            } 

}

档案视图

@foreach($usersprojects->projects as $project) 

{ $project->project_name }}
    @if(!is_null($project->clients)) 
        {{ $project->clients->client_name }}
    @endif
@endif

@endforeach

这工作正常,但如果我扩展查询以包含另一个关系,这将失败,例如

public function getUserProjectswithClients()
    {

        return \User::with(array('projects', 'projects.clients', 'projects.statuses'))->find(Auth::user()->id); 
    }

我的laravel错误日志如下:

Next exception 'Illuminate\Database\QueryException' with message 'SQLSTATE[42S22]:
 Column not found: 1054 Unknown column 'statuses.project_id' in 'where clause' 
(SQL: select * from `statuses` where `statuses`.`project_id` in (4, 5, 6, 7, 8, 9, 
10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 
31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 
52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 
73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 
94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 
112, 113, 114))' in /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Connection.php:600
    Stack trace:

当查询返回未知列时,我不确定我是否在模型中设置了正确的关系 . 我的关系如下:

Project.php

public function statuses() {
        return $this->hasOne('Status');
    }

Status.php

public function projects()
{
    return $this->hasOne('Project');
}

如果我将project.php更改为:

public function statuses() {
        return $this->belongsTo('Status');
    }

我得到的状态返回为null,所以我认为我的关系是错误的,但我不确定Projects与状态的关系如何,一次只能分配一个.399625_ . 对于状态也是如此,可以将状态分配给项目,但状态表中不保留此状态的记录 .

我的状态表的架构如下:

id | user_id | name | date_created | date_updated

我的Projects表如下:

id | user_id | client_id | status_id | project_name | project_brief

任何人都可以帮助我开始这个吗?谢谢

Upadte

根据Jarek改变关系的指示,我知道得到以下错误:

[2014-09-07 15:39:30] production.ERROR: exception 'BadMethodCallException' 
with message 'Call to undefined method Illuminate\Database\Query\Builder::statuses()' in /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:2117
Stack trace:
#0 [internal function]: Illuminate\Database\Query\Builder->__call('statuses', Array)
#1 [internal function]: Illuminate\Database\Query\Builder->statuses()
#2 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(913): call_user_func_array(Array, Array)
#3 [internal function]: Illuminate\Database\Eloquent\Builder->__call('statuses', Array)
#4 [internal function]: Illuminate\Database\Eloquent\Builder->statuses()
#5 /media/sf_Sites/tempus/bootstrap/compiled.php(7423): call_user_func_array(Array, Array)
#6 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(498): Illuminate\Database\Eloquent\Model->__call('statuses', Array)
#7 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(498): Project->statuses()
#8 [internal function]: Illuminate\Database\Eloquent\Builder->Illuminate\Database\Eloquent\{closure}()
#9 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Relations/Relation.php(158): call_user_func(Object(Closure))
#10 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(499): Illuminate\Database\Eloquent\Relations\Relation::noConstraints(Object(Closure))
#11 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(469): Illuminate\Database\Eloquent\Builder->getRelation('statuses')
#12 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(449): Illuminate\Database\Eloquent\Builder->loadRelation(Array, 'statuses', Object(Closure))
#13 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(158): Illuminate\Database\Eloquent\Builder->eagerLoadRelations(Array)
#14 [internal function]: Illuminate\Database\Eloquent\Builder->get()
#15 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Relations/Relation.php(281): call_user_func_array(Array, Array)
#16 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Relations/Relation.php(103): Illuminate\Database\Eloquent\Relations\Relation->__call('get', Array)
#17 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Relations/Relation.php(103): Illuminate\Database\Eloquent\Relations\HasMany->get()
#18 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(480): Illuminate\Database\Eloquent\Relations\Relation->getEager()
#19 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(449): Illuminate\Database\Eloquent\Builder->loadRelation(Array, 'projects', Object(Closure))
#20 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(158): Illuminate\Database\Eloquent\Builder->eagerLoadRelations(Array)
#21 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(125): Illuminate\Database\Eloquent\Builder->get(Array)
#22 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(82): Illuminate\Database\Eloquent\Builder->first(Array)
#23 /media/sf_Sites/tempus/app/Acme/Repositories/DbProjectRepository.php(25): Illuminate\Database\Eloquent\Builder->find('1')
#24 /media/sf_Sites/tempus/app/controllers/UsersController.php(47): Acme\Repositories\DbProjectRepository->getUserProjectswithClients()
#25 [internal function]: UsersController->profile()
#26 /media/sf_Sites/tempus/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(231): call_user_func_array(Array, Array)
#27 /media/sf_Sites/tempus/bootstrap/compiled.php(5784): Illuminate\Routing\Controller->callAction('profile', Array)
#28 /media/sf_Sites/tempus/bootstrap/compiled.php(5772): Illuminate\Routing\ControllerDispatcher->call(Object(UsersController), Object(Illuminate\Routing\Route), 'profile')
#29 /media/sf_Sites/tempus/bootstrap/compiled.php(4971): Illuminate\Routing\ControllerDispatcher->dispatch(Object(Illuminate\Routing\Route), Object(Illuminate\Http\Request), 'UsersController', 'profile')
#30 [internal function]: Illuminate\Routing\Router->Illuminate\Routing\{closure}()
#31 /media/sf_Sites/tempus/bootstrap/compiled.php(5330): call_user_func_array(Object(Closure), Array)
#32 /media/sf_Sites/tempus/bootstrap/compiled.php(4996): Illuminate\Routing\Route->run(Object(Illuminate\Http\Request))
#33 /media/sf_Sites/tempus/bootstrap/compiled.php(4984): Illuminate\Routing\Router->dispatchToRoute(Object(Illuminate\Http\Request))
#34 /media/sf_Sites/tempus/bootstrap/compiled.php(717): Illuminate\Routing\Router->dispatch(Object(Illuminate\Http\Request))
#35 /media/sf_Sites/tempus/bootstrap/compiled.php(698): Illuminate\Foundation\Application->dispatch(Object(Illuminate\Http\Request))
#36 /media/sf_Sites/tempus/bootstrap/compiled.php(7706): Illuminate\Foundation\Application->handle(Object(Illuminate\Http\Request), 1, true)
#37 /media/sf_Sites/tempus/bootstrap/compiled.php(8309): Illuminate\Session\Middleware->handle(Object(Illuminate\Http\Request), 1, true)
#38 /media/sf_Sites/tempus/bootstrap/compiled.php(8256): Illuminate\Cookie\Queue->handle(Object(Illuminate\Http\Request), 1, true)
#39 /media/sf_Sites/tempus/bootstrap/compiled.php(10895): Illuminate\Cookie\Guard->handle(Object(Illuminate\Http\Request), 1, true)
#40 /media/sf_Sites/tempus/bootstrap/compiled.php(659): Stack\StackedHttpKernel->handle(Object(Illuminate\Http\Request))
#41 /media/sf_Sites/tempus/public/index.php(49): Illuminate\Foundation\Application->run()
#42 {main} [] []

编辑:

DbProjectRepository.php

<?php
 namespace Acme\Repositories;

use Project, Auth, Status, Client, Teams, Input, Task;


class DbProjectRepository implements ProjectRepositoryInterface  {


//get user projects with all associated clients and statuses
public function getUserProjectswithClients()
    {

        return \User::with(array('projects', 'projects.clients', 'projects.statuses'))->find(Auth::user()->id); 

    }

} //end class

Project.php

<?php
use Illuminate\Database\Eloquent\ModelNotFoundException;

class Project extends Eloquent
{

    public function clients()
    {
        return $this->belongsTo('Client', 'client_id');
    }

    public function users()
    {
        return $this->belongsTo('User');
    }

    public function tasks() 
    {
        return $this->hasMany('Task');
    }


    public function status() // singular name, since it will be     single model
    {
        return $this->hasOne('Status');
        // equal to:
        // return $this->hasOne('Status', 'status_id');
    }



}

Status.php

<?php
use Illuminate\Database\Eloquent\ModelNotFoundException;

class Status extends Eloquent
{

    public function users()
    {
        return $this->belongsTo('User');
    }

// Status
public function project() // singular name, since it will be single model
{
    return $this->hasOne('Project');
    // equal to
    // return $this->hasOne('Project', 'status_id');
}

    public function tasks() 
    {
        return $this->hasMany('Task');
    }   

}

user.php的

<?php

use Zizaco\Confide\ConfideUser;
use Zizaco\Confide\ConfideUserInterface;

class User extends Eloquent implements ConfideUserInterface
{
    use ConfideUser;

    public function clients()
    {
        return $this->hasMany('Client');
    }

    public function projects()
    {
        return $this->hasMany('Project');
    }
    public function statuses()
    {
        return $this->hasMany('Status');
    }
    public function tasks()
    {
        return $this->hasMany('Task');
    }
        public function teams()
    {
        return $this->hasMany('Team');
    }


}

<?php

use Acme\Repositories\ProjectRepositoryInterface;

/**
 * UsersController Class
 *
 * Implements actions regarding user management
 */
class UsersController extends Controller
{

 public function profile() {


        //returning users projects
    $data["usersprojects"] = $usersprojects = $this->userrepo->getUserProjectswithClients();


  return $usersprojects;
}
}

1 回答

  • 3

    tldr; Eloquent以这种方式猜测你的关系的外键:

    1. for belongsTo by method's name (statuses() -> statuses_id)

    2. for hasOne using calling model getForeignKey() method ($status->getForeignKey())

    这是错误的关系:

    // Project
    public function statuses() {
        return $this->hasOne('Status');
    }
    

    这是正确的,但仍然不好(复数名称):

    // Status
    public function projects()
    {
        return $this->hasOne('Project');
    }
    

    这是正确的关系,但缺少外键:

    public function statuses() {
        return $this->belongsTo('Status');
        // it is the same as:
        // return $this->belongsTo('Status', 'statuses_id');
    }
    

    So, correct and adequate version of your relations would be:

    // Project
    public function status() // singular name, since it will be single model
    {
        return $this->belongsTo('Status');
        // equal to:
        // return $this->belongsTo('Status', 'status_id');
    }
    
    // Status
    public function project() // singular name, since it will be single model
    {
        return $this->hasOne('Project');
        // equal to
        // return $this->hasOne('Project', 'status_id');
    }
    

相关问题