[BioSQL-l] query problem

[Hilmar Lapp]

On Saturday, November 8, 2003, at 03:13  PM, Marc Logghe wrote:

> Hi,
> I'm having some problems in creating a correct query. What I am trying 
> to do is fetching a persistent feature object with primary_tag 
> 'source' of a seq object with accesssion 'ABC' belonging to namespace 
> 'test'. I have tried this using the following code snippet:
> my $adp    = $db->get_object_adaptor('Bio::SeqFeatureI');
> $adp->verbose(1);
> my $query  = Bio::DB::Query::BioQuery->new();
> my $mapper = 
> Bio::DB::BioSQL::Oracle::BasePersistenceAdaptorDriver->new();
>
> $query->datacollections(
>                          [
>                            "Bio::SeqFeatureI f",
>                            "Bio::SeqI=>Bio::SeqFeatureI s",
>                        #    "Bio::Ontology::TermI=>Bio::SeqFeatureI t",
>                            "BioNamespace=>Bio::PrimarySeqI db",
>                          ]
> );
>
> $query->where( [ "db.namespace = 'test'", "f.primary_tag = 'source'", 
> "s.accession_number = 'ABC'"] );
>
> This did not work as I expected however. Seems like f.primary_tag is 
> mapped to type_trm_id, so I had to pass it the value 11 instead.

Right. Source tag and primary tag are ontology terms.

> So, I guessed "Bio::Ontology::TermI=>Bio::SeqFeatureI t" had to be 
> added to the datacollections in order to get to the value 'source' 
> with the where clause:
> $query->where( [ "db.namespace = 'test'", "t.name = 'source'", 
> "s.accession_number = 'ABC'"] );
> This did not work neither

seqfeature has two foreign keys to term, one for type 
($feature->primary_tag) and one for source ($feature->source_tag). 
Without context, the OR mapper cannot know which one you're referring 
to in a join. The mechanism for supplying context is appending the 
contextual keyword to the alias of the ambiguous parent, delimited by 
'::'. For seqfeature's foreign keys to term, the context keywords are 
primary_tag and source_tag. I.e., you need to write

	...,
	"Bio::Ontology::TermI=>Bio::SeqFeatureI t",
	...

as

	...,
	"Bio::Ontology::TermI=>Bio::SeqFeatureI t::primary_tag",
	...

Let me know if it still produces the wrong SQL after making this change.

	-hilmar

BTW the other instances when you need to supply context is for all 
relationships (seqfeature, bioentry, term). The keywords are subject 
and object for the subject and object of the relationship, 
respectively, and predicate for the predicate in a term relationship. 
No, this is not documented (yet; anyone willing to help?) ...


> , because the generated sql was not OK:
> SELECT f.oid, f.display_name, f.rank, f.ent_oid, f.type_trm_oid, 
> f.source_trm_oid FROM seqfeature f, bioentry s, term t, biodatabase db 
> WHERE f.ent_oid = s.oid AND f.trm_oid = t.oid AND s.db_oid = db.oid 
> AND (db.name = 'test' AND t.name = 'source' AND s.accession = 
> 'ABC')''])
> As far as I can see 'f.trm_oid = t.oid' should read 'f.type_trm_oid = 
> t.oid' ?
> I am pretty new to the API and schema, so I guess it is more probable 
> that I am missing something here.
>
> Regards,
> Marc
>
>
> _______________________________________________
> BioSQL-l mailing list
> BioSQL-l at open-bio.org
> http://open-bio.org/mailman/listinfo/biosql-l
>
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------