Re: [SQLObject] Join with results from both tables

Hi!

On Mon, Jun 16, 2014 at 08:50:44PM +0000, "Goldberg, Arthur P" <art...@ms...> wrote:
> Hi
> 
> I'm just learning sqlobject and find it pretty neat!

   Welcome!

> Suppose I have this:
> from sqlobject import *
> 
> class Variant2(SQLObject):
>     class sqlmeta:
>         style = Style(longID=True)
>     ref = StringCol()
>     alt = StringCol()
>     SubjectID = StringCol()
> 
> class Subject2(SQLObject):
>     FamilyID = StringCol()
>     IndivID = StringCol( alternateID=True, length=50 )  # do not know if this is long enough
>     Sex = StringCol()
> 
> connection = connectionForURI(Minerva_URI)
> connection.debug = True
> sqlhub.processConnection = connection
> Variant2.dropTable( True, )
> Variant2.createTable()
> Subject2.dropTable( True, )
> Subject2.createTable()
> 
> Variant2(
>     ref = 'A',
>     alt = 'C',
>     SubjectID = 'Sub1' )
> 
> Variant2(
>     ref = 'A',
>     alt = 'G',
>     SubjectID = 'Sub2' )
> 
> Subject2( FamilyID = '', IndivID = 'Sub1', Sex = '1' )
> Subject2( FamilyID = '', IndivID = 'Sub2', Sex = '2' )
> 
> for var in Variant2.select(
>     """ subject2.indiv_id = Variant2.SubjectID""",
>     clauseTables=['subject2']):
>     print var
> 
> How would I generate a query that joins Variant2 and subject2? Like this:
> 
> SELECT Variant2.Variant2_id, Variant2.ref, Variant2.alt, Variant2.AAC, Variant2.SubjectID, subject2.Sex FROM subject2, Variant2 WHERE  subject2.indiv_id = Variant2.SubjectID;


for var in Variant2.select(Subject2.q.IndivID==Variant2.q.SubjectID):
    print var

   When you use magic .q. attributes SQLObject derives clauseTables
automatically; it lists all tables used in WHERE clause.

> Also, how could I define SubjectID as a foreign key that references indiv_id?

   Alas, that much harder. Short answer is this (I show only relevant
changes):

class Variant2(SQLObject):
    class sqlmeta:
        idType = str
    Subject = ForeignKey('Subject2', refColumn='IndivID')

   'idType = str' is required because you want SubjectID to be a string
foreign key. But at the same time it changes the type of the 'id' column --
it also becomes TEXT and loses autoincrement property so you have to
assign it manually. The entire programs is now:

class Variant2(SQLObject):
    class sqlmeta:
        idType = str
        style = Style(longID=True)
    ref = StringCol()
    alt = StringCol()
    Subject = ForeignKey('Subject2', refColumn='IndivID')

class Subject2(SQLObject):
    FamilyID = StringCol()
    IndivID = StringCol( alternateID=True, length=50 )  # do not know if this is long enough
    Sex = StringCol()

Variant2.createTable()
Subject2.createTable()

Variant2(
    id = '1',
    ref = 'A',
    alt = 'C',
    SubjectID = 'Sub1' )

Variant2(
    id = '2',
    ref = 'A',
    alt = 'G',
    SubjectID = 'Sub2' )

Subject2( FamilyID = '', IndivID = 'Sub1', Sex = '1' )
Subject2( FamilyID = '', IndivID = 'Sub2', Sex = '2' )

for var in Variant2.select(Subject2.q.IndivID==Variant2.q.SubjectID):
    print var

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.