mysql - 加入一个表的搜索结果 "belongs_to"另一个

Question

visits 有很多 visitsTasks 属于 tasks。

我正在查看 visits 表中的特定行 (visitID)，并希望找到所有已完成的任务。在这些任务中，我想访问 path 和 mode 列(在 tasks 表中)。

      my $visitTasks = $visit->visits_tasks()->search(
            {'me.subsection' => 'completed'},
            {join => 'task_name' } # PROBLEM HERE
            ); 
      while(my $vt=$visitTasks->next()){
         say Dumper($vt->get_columns());

         # if completed is true then record the task
         push @{$visit{Tasks}},  {taskName => $vt->task_name, path=>$vt->path, mode=>$vt->mode } if $vt->value == 1;
      }

taskName 和 value(visits_tasks 列)可用。但是我找不到语法或缺少的关系来获取 visitsTasks 结果与 tasks

---

我用 dia 创建了一个 UML 图，用 parsediasql 将图转换成 mySQL，并使用 dbicdump 填充 DBIX::class。

访问任务

package lncddb3::Schema::Result::VisitsTask;
#...
=head2 task_name

Type: belongs_to

Related object: L<lncddb3::Schema::Result::Task>

=cut

__PACKAGE__->belongs_to(
  "task_name",
  "lncddb3::Schema::Result::Task",
  { taskName => "taskName" },
  {
    is_deferrable => 1,
    join_type     => "LEFT",
    on_delete     => "RESTRICT",
    on_update     => "RESTRICT",
  },  
);

任务

package lncddb3::Schema::Result::Task;
# ...
=head2 visits_tasks

Type: has_many

Related object: L<lncddb3::Schema::Result::VisitsTask>

=cut

__PACKAGE__->has_many(
  "visits_tasks",
  "lncddb3::Schema::Result::VisitsTask",
  { "foreign.taskName" => "self.taskName" },
  { cascade_copy => 0, cascade_delete => 0 },
);

架构

create table visits (
   visitID      int unsigned    not null auto_increment          ,
   peopleID     int unsigned                      ,
   visitdate    date                              ,
   visitTime    time                              ,
   scanID       varchar(50)                       ,
   noShow       bool                              ,
   schedualedBy varchar(50)                       ,
   checkedInBy  varchar(50)                       ,
   googleid     varchar(50)                       ,
   age          double unsigned                   ,
   onMeds       varchar(20)                       ,
   location     varchar(50)                       ,
   cohort       varchar(50)      default "control",
   quality      int                               ,
   constraint pk_visits primary key (visitID)
) 
create table visitsTasks (
   peopleID   int unsigned ,
   visitID    int unsigned ,
   taskName   varchar(50)  ,
   subsection varchar(50)  ,
   value      varchar(50)
) 
create table tasks (
   taskName varchar(50)  not null,
   taskDesc char(200)            ,
   path     varchar(300)         ,
   mode     varchar(50)          ,--  fMRI, PET, MEG,fMRI+eyetracking,eyetracking, etc
   constraint pk_tasks primary key (taskName)
)

Answer 1

需要花更多时间在 Cookbook 上.连接的列与原始列不在同一级别。

$vt->task_name->path #not $vt->path