[Bioperl-l] Re: [BioSQL-l] How to find by foreign key?

[Hilmar Lapp]

Uhhm yes, this is the very dark corner of bioperl-db documentation.

Generally speaking it goes like this:

	# setup a BioQuery object that represents the foreign key query
     my $query = Bio::DB::Query::BioQuery->new(
                                    -datacollections => [
	                                   # you may use aliases
	                                   "Bio::SeqI seq",
	                                   # format is parent=>child; alias is 
always
	                                   # for parent here
                                        "Bio::Species=>Bio::SeqI sp"],
	                               # primary_key is generally stands for 
the primary
	                               # key of an entity
                                    -where => ["sp.primary_key = ?"]
	                               # alternatively, query by species name:
	                               # -where => ["sp.name = ?"]
	                               );
	# along with the values, pass it to the adaptor matching the intended
	# result object
	my $adp = $db->get_object_adaptor("Bio::SeqI");
	# the return value is an iterator over the result set
     my $qresult = $adp->find_by_query($query,
	                               # -name is optional. If you provide it 
the statement
                                    # will be cached (and not rebuilt if 
you query the
	                               # second time
                                    -name => "FIND SEQ BY SPECIES",
	                               # you may have more parameters than 
just this one
                                    -values => [$obj->primary_key()]
	                               # or if you queried by species common 
name:
	                               # -values => [$species->common_name()]
                                    );
     # iterate over the result set
     while(my $seq = $qresult->next_object()) {
		# do stuff
	}

Here is where to glean this from the code until there is better 
documentation:

- t/query.t contains a whole series of tests for the 
$adp->find_by_query() functionality, from simple queries to rather 
complex ones. What you can glean from there is how to setup the query 
object to be passed into find_by_query(). Since it only tests the 
generated SQL for correctness, you won't see how to program with 
find_by_query().

- many adaptors in Bio/DB/BioSQL override the method attach_children(), 
where the purpose essentially is find all objects referencing the given 
one as foreign key, e.g., for a bioentry (Bio::Seq object) find all 
seqfeatures (Bio::SeqFeatureI objects) in SeqAdaptor. Some adaptors 
will also find objects by association if the relationship is n-n, an 
example is TermAdaptor finding the dbxrefs for a term.

Note that the above example with bioentry by species got somewhat messy 
since the taxon tables don't resemble the bioperl object model anymore. 
For instance, to constrain the above query by species common name 
('mouse') correctly, you'd specify that name_class equals 'common 
name'. You can in fact add this as another condition, because 
name_class is a pseudo attribute mapped to the table column, but you 
really have to know this since there is no such attribute on the object.

Let me know if this helps.

	-hilmar


On Sunday, May 25, 2003, at 11:31  PM, Juguang Xiao wrote:

> Hi guys,
>
> I cannot find any doc or demo on how to find object by foreign key 
> through API, say, I want to find all bioentries whose species is > mouse.
>
> Can some harkers enlighten me? Thanks
>
> Juguang
> ------------ATGCCGAGCTTNNNNCT--------------
> Juguang Xiao
> Temasek Life Sciences Laboratory, National University of Singapore
> 1 Research Link,  Singapore 117604
> juguang at tll.org.sg
>
> _______________________________________________
> 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
-------------------------------------------------------------