Re: [SQLObject] sqlobject one-to-many join question

[Oleg B. <ph...@ph...>]

Hi!

On Tue, Dec 15, 2015 at 03:49:29PM -0800, jo...@ma... wrote:
> So I've been absolutely loving sqlobject, until I get to actually get to
> making relations between tables.  At this point, I feel that the
> 'documentation' covers some points, but really doesn't explain other
> bits very well at all.

   Patches for documentation will be gladly accepted, as well as patches
for code and for tests.

> So I have 2 problems:
> 
> The first is that when running my program the first time, it fails to
> create all the tables (using .createTable(ifNotExists = True))  and only
> several tables are generated, before it errors out with: 
> 
> > sqlobject.dberrors.OperationalError: Can't create table `rainmeter`.`#sql-65c_209` (errno: 150 "Foreign key constraint is incorrectly formed")
> 
> Then running it again causes the rest of the tables to be created. 
> 
> Here's the relevant parts of my class definitions: 
> 
> > class Table_Sensors(sqlobject.SQLObject):
> > name = sqlobject.StringCol(length=32)
> > group = sqlobject.ForeignKey("Table_Groups")
> 
> > class Table_Groups(sqlobject.SQLObject):
> > name = sqlobject.StringCol(length=32)
> > sensors = sqlobject.MultipleJoin("Table_Sensors") 
> > Table_Sensors.createTable(ifNotExists = True)
> > Table_Groups.createTable(ifNotExists = True)

   You create table in the wrong order. Table_Sensors has a ForeignKey
pointing to to Table_Groups so you must create Table_Groups first and
then Table_Sensors.

> Anyways, this is inconvenient, but it does work after running it again,
> so I can put up with it.  The real problem I've been having is that I'm
> having trouble figuring out how to select all entries in Table_Sensors
> that point to a specific Table_Groups. 
> 
> Here's one example I've tried: 
> 
> > Table_Groups.select(Table_Groups.q.name=="Feels_Like", Table_Groups.q.sensors==Table_Sensors.q.group, orderBy=Table_Sensors.q.sort_order)

   There is a bug in the query: it lists two conditions separated by a
comma. That doesn't work because the second condition becomes the second
parameter for .select() instead of being a part of the condition. You
must join conditions using either AND() from sqlbuilder or '&' operator:

Table_Groups.select(Table_Groups.q.name=="Feels_Like" & Table_Groups.q.sensors==Table_Sensors.q.group, orderBy=Table_Sensors.q.sort_order)

> But no matter what I try, even a much simpler query, It just spits back:
> 
> > AttributeError: Table_Groups instance has no attribute 'sensors'

   In SQL sense Table_Groups really doesn't have an attribute 'sensors'.
MultipleJoin doesn't add an attribute to SQL table. Instead it instructs
SQLObject: "when the user asks for Table_Groups.sensors select rows from
Table_Sensors that point to that Table_Groups' row".
   So this query works:

Table_Groups.select(Table_Groups.q.name=="Feels_Like" &
                    Table_Groups.q.id==Table_Sensors.q.group,
                    orderBy=Table_Sensors.q.id)

   BTW, there is no need to construct an SQL query in your case -- use
the power of SQLObject. If you want to select Table_Sensors for a group
just fetch the group and ask for its sensors.
   First, let's fix the join, it should explicitly declare that it
points to 'id' column in the other table:

class Table_Groups(SQLObject):
    name = StringCol(length=32)
    sensors = MultipleJoin("Table_Sensors", joinColumn="id")

group = Table_Groups.select(Table_Groups.q.name=='Feels_Like')[0]
for sensor in group.sensors:
    print sensor

> As a side-note, I think some of the things I'm trying to do, I could use
> j-magic for, but the section on that is incredibly short, and I haven't
> been able to get that to work either :/ 

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