3

I have 3 tables:

Table 1      Table 2            Table 3
id_1 [...]   id_1, type, id_2   id_2 [...]

I want to join table 1 to table 3 by way of table 2. This is easy enough,eg.

$data['table3']['table']['join']['table1'] = array(
  'left_table' => 'table2',
  'left_field' => 'id_2',
  'field' => 'id_2',
);
$data['table2']['table']['join']['table1'] = array(
  'left_field' => 'id_1',
  'field' => 'id_1',
);

However, the intermediary table also has a 'type' attribute in it. What if I want to specify, as part of the join, what the type is? eg. I want something like this:

SELECT id_a.table1 FROM table3
LEFT JOIN table2 ON table3.id_2 = table2.id_2 AND table2.type = 'blah'
LEFT JOIN table1 ON table2.id_1 = table2.id_1;

To be clear, I want the condition to the part of the join, not after the fact (as a filter will remove null rows).

3

I havent done this in Views 3, but try the following, adopted from Views2 blog post applied to your code: http://www.agileapproach.com/blog-entry/views-2-table-aliases-and-extra-criteria

# I think you had typo in your join for table2 where
# you had as the final key 'table1'.
$data['table3']['table']['join']['table2'] = array(
  'left_table' => 'table2',
  'left_field' => 'id_2',
  'field' => 'id_2',
  'extra' => array(
      array('field' => 'type', 'value' => 'blah'),
    ),
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.