On 6 Mar 2007, at 18:16, seth redmond wrote:
I have two schemata: vb and vb_map, which have the following tables
(and primary keys)
vb__reporter__main (reporter_id_key)
vb__reporter_array__dm (reporter_id_key)
vb__experiment__main (reporter_id_key,
reporter_experiment_key)
vb__experiment_anova__dm (reporter_id_key,
reporter_experiment_key)
vb__hybridisation__main (reporter_id_key, reporter_experiment_key,
hybridisation_id_key)
vb__hybridisation_annotation__dm (reporter_id_key,
reporter_experiment_key, hybridisation_id_key)
vb_map__reporter__main (reporter_id_key)
vb_map__reporter_mapping__main (reporter_id_key, reporter_location_key)
vb_map__mapping_exon__dm (reporter_location_key)
vb_map__mapping_transcript__dm (reporter_location_key)
As the most basic test I'm trying to return all attributes without any
filters (there is only one or two lines of test data in each table).
For both datasets I've changed all tableConstraints to their full
names and both now work like this. To give you an example I've tried
it both ways with vb_map.
The SQL generated (and the error where tableConstraint: main is used)
are below:
I'd guess the mart should give only the first table
(vb_map__reporter__main) the 'main' alias since the query crosses all
tables, but instead it gives it to the child table:
vb_map__reporter_mapping__main. Reporter_id_list is in reporter__main
only.
-s
# WITHOUT 'MAINS'
SELECT vb__reporter__main.reporter_id_list,
vbmart_01_3.vb__reporter__main.reporter_id_list,
vbmart_01_3.vb__reporter_array__dm.reporter_name,
vbmart_01_3.vb__reporter__main.reporter_type_list,
vbmart_01_3.vb__reporter_array__dm.array_name,
vbmart_01_3.vb__experiment__main.experiment_id,
vbmart_01_3.vb__experiment__main.experiment_name_list,
vbmart_01_3.vb__experiment_anova__dm.annotation_type,
vbmart_01_3.vb__hybridisation_annotation__dm.annotation_type,
vbmart_01_3.vb__hybridisation_annotation__dm.annotation_list,
vbmart_01_3.vb__experiment__main.log2range,
vbmart_01_3.vb__experiment_anova__dm.anova
FROM
vbmart_01_3.vb__reporter__main,
vbmart_01_3.vb__hybridisation_annotation__dm,
vbmart_01_3.vb__reporter_array__dm,
vbmart_01_3.vb__experiment_anova__dm,
vbmart_01_3.vb__experiment__main,
vbmart_01_3.vb__hybridisation__main main
WHERE
main.reporter_id_key = vb__reporter__main.reporter_id_key
AND
main.hybridisation_id_key =
vbmart_01_3.vb__hybridisation_annotation__dm.hybridisation_id_key
AND
main.reporter_experiment_key =
vbmart_01_3.vb__experiment__main.reporter_experiment_key
AND
main.reporter_id_key =
vbmart_01_3.vb__reporter_array__dm.reporter_id_key
AND
main.reporter_experiment_key =
vbmart_01_3.vb__experiment_anova__dm.reporter_experiment_key
AND
main.reporter_id_key = vbmart_01_3.vb__reporter__main.reporter_id_key
LIMIT 200\G
# WITH 'MAINS'
SELECT
main.reporter_id_list,
main.spp,
main.seq_region_name,
main.seq_region_start,
main.seq_region_strand,
main.seq_region_end,
vbmart_01_3.vb_map__mapping_transcript__dm.transcript_id_list,
vbmart_01_3.vb_map__mapping_transcript__dm.gene_id_list,
vbmart_01_3.vb_map__mapping_exon__dm.exon_short_location,
main.short_location
FROM
vbmart_01_3.vb_map__mapping_transcript__dm,
vbmart_01_3.vb_map__mapping_exon__dm,
vbmart_01_3.vb_map__reporter_mapping__main main
WHERE
main.reporter_location_key =
vbmart_01_3.vb_map__mapping_exon__dm.reporter_location_key
AND
main.reporter_location_key =
vbmart_01_3.vb_map__mapping_transcript__dm.reporter_location_key
LIMIT 200\G
DBD::mysql::st execute failed: Unknown column 'main.reporter_id_list'
in 'field list' at
/home/snr02/work/biomart/0.5/biomart-perl/lib/BioMart/Dataset/
TableSet.pm line 234.
Hi Seth,
it seems to me that you simply have a missing column in the
vbmart_01_3.vb_map__reporter_mapping__main main
A child table need to 'inherit' all columns from the parent table.
That's why dynamic switching between main tables is possible
and 'main' aliases should work.
As an example you can look at ensembl gene and ensembl transcript table
from ensembl gene dataset.
All columns from gene (parent) are repeated in transcript (child).
However, transcript is allowed to have additional columns
which are not in the parent table but not the other way round
a.
On 6 Mar 2007, at 14:35, Arek Kasprzyk wrote:
On 6 Mar 2007, at 14:22, seth redmond wrote:
I've been having a number of problems with a schema which includes
multiple / nested main tables. It seems a naive build will build
filters which all refer to 'main' instead of the full table name and
when generating SQL will request columns from 'main' but only create
the alias from the last table added to TableSet.pm.
Sorting this out in one dataset was just a matter of replacing all
the tableConstraint: main values with their fullname equivalents,
but in another this hasn't worked and I'm a bit stumped as to why.
Could you give me more info about how this 'main' table is defined
and anywhere else I could look to resolve it.
thanks
Hi Seth,
the 'main' alias is there on purpose to allow SQL compiler
to dynamically choose a 'path' through the schema dependent
on your attribute and filter choices ei choosing the right main
table for each individual query. Changing it to fully qualified names
for the 'single main table' dataset should work but for a 'multiple
main table' one
probably wont.
Could you give us more details on your schema and tell us exactly why
this is causing you problems?
cheers,
a.
-s
-- Seth Redmond
Scientific Programmer, VectorBase
Kafatos / Christophides Groups
Div. Cell and Molecular Biology
Imperial College, London
[EMAIL PROTECTED]
--
----------------------------------------------------------------------
---------
Arek Kasprzyk
EMBL-European Bioinformatics Institute.
Wellcome Trust Genome Campus, Hinxton,
Cambridge CB10 1SD, UK.
Tel: +44-(0)1223-494606
Fax: +44-(0)1223-494468
----------------------------------------------------------------------
---------
------------------------------------------------------------------------
-------
Arek Kasprzyk
EMBL-European Bioinformatics Institute.
Wellcome Trust Genome Campus, Hinxton,
Cambridge CB10 1SD, UK.
Tel: +44-(0)1223-494606
Fax: +44-(0)1223-494468
------------------------------------------------------------------------
-------
