function tripal_stock_add_organism_count_mview
2.x tripal_stock.install | tripal_stock_add_organism_count_mview() |
Creates a materialized view that stores the type & number of stocks per organism
Related topics
2 calls to tripal_stock_add_organism_count_mview()
- tripal_stock_install in tripal_stock/
tripal_stock.install - Implementation of hook_install().
- tripal_stock_update_7201 in tripal_stock/
tripal_stock.install - Add materialized views
File
- tripal_stock/
tripal_stock.install, line 115 - Install the tripal stock module including it's content type
Code
function tripal_stock_add_organism_count_mview() {
$view_name = 'organism_stock_count';
$comment = 'Stores the type and number of stocks per organism';
$schema = array(
'description' => $comment,
'table' => $view_name,
'fields' => array(
'organism_id' => array(
'size' => 'big',
'type' => 'int',
'not null' => TRUE,
),
'genus' => array(
'type' => 'varchar',
'length' => '255',
'not null' => TRUE,
),
'species' => array(
'type' => 'varchar',
'length' => '255',
'not null' => TRUE,
),
'common_name' => array(
'type' => 'varchar',
'length' => '255',
'not null' => FALSE,
),
'num_stocks' => array(
'type' => 'int',
'not null' => TRUE,
),
'cvterm_id' => array(
'size' => 'big',
'type' => 'int',
'not null' => TRUE,
),
'stock_type' => array(
'type' => 'varchar',
'length' => '255',
'not null' => TRUE,
),
),
'indexes' => array(
'organism_stock_count_idx1' => array('organism_id'),
'organism_stock_count_idx2' => array('cvterm_id'),
'organism_stock_count_idx3' => array('stock_type'),
),
);
$sql = "
SELECT
O.organism_id, O.genus, O.species, O.common_name,
count(S.stock_id) as num_stocks,
CVT.cvterm_id, CVT.name as stock_type
FROM organism O
INNER JOIN stock S ON O.Organism_id = S.organism_id
INNER JOIN cvterm CVT ON S.type_id = CVT.cvterm_id
GROUP BY
O.Organism_id, O.genus, O.species, O.common_name, CVT.cvterm_id, CVT.name
";
tripal_add_mview($view_name, 'tripal_stock', $schema, $sql, $comment);
}