sqlobject-discuss

[SQLObject] Regular joins?

[Peter G. <pe...@fs...>]

Hello. I'm looking to make a regular join, but with testing.

I have two tables (simplified for the example):

class Project(SQLObject):
  permissions = MultipleJoin("Permission")

class Permission(SQLObject):
  person = ForeignKey("person")
  project = ForeignKey("Project")
  type = EnumCol(enumValues = ['A', 'B', 'C'])

The SQL for the select would be something like:

SELECT project.* FROM project, permission WHERE permission.project_id = 
project.project_id AND permission.person_id = 666;

I don't know in the beginning of the program which projects actualy contain 
permissions, otherwise I'd just do a myproject.permissions, what I want to 
do is actualy select all projects that have permissions for a certain user.

How can this be done?

/Peter Gebauer

Re: [SQLObject] Regular joins?

[Ian B. <ia...@co...>]

Peter Gebauer wrote:
> Hello. I'm looking to make a regular join, but with testing.
> 
> I have two tables (simplified for the example):
> 
> class Project(SQLObject):
>   permissions = MultipleJoin("Permission")
> 
> class Permission(SQLObject):
>   person = ForeignKey("person")
>   project = ForeignKey("Project")
>   type = EnumCol(enumValues = ['A', 'B', 'C'])
> 
> The SQL for the select would be something like:
> 
> SELECT project.* FROM project, permission WHERE permission.project_id = 
> project.project_id AND permission.person_id = 666;
> 
> I don't know in the beginning of the program which projects actualy contain 
> permissions, otherwise I'd just do a myproject.permissions, what I want to 
> do is actualy select all projects that have permissions for a certain user.


Project.select(AND(Project.q.permissionsID == Permission.q.id,
                    Permission.q.personID == aPerson.id))

or

Project.select("project.project_id = permission.project_id AND 
permission.person_id = %i" % aPerson.id, clauseTables=['permission'])

Cheers.
   Ian