little trouble remote_side when local col points to same col in parent

[David Gardner]

So I have a self referential relationship between tasks, the tricky part
is the tasks table has a composite key (asset,name), the children of a
task will always have the same name as their parent, but will point to
different assets.
I'm not sure how to specify remote_side properly in my mapper so that SA
(0.5.6) will know how to join them properly.
I could probably just add in a "parent_name" column, but I would prefer
not since I want to enforce child tasks to have the same name as their
parents.

Looks like:

CREATE TABLE task (
"name" text NOT NULL,
asset text NOT NULL,
parent_asset text,
<more columns>
CONSTRAINT "task_PK" PRIMARY KEY (asset, name),
CONSTRAINT "task_parent_FK" FOREIGN KEY (parent_asset, "name")
REFERENCES task (asset, "name") MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE);

So I define my mapper like:
mapper(Task,task_table, properties={
'Children' : relation(Task, backref=backref('Parent',

remote_side=[task_table.c.asset, task_table.c.name]),

order_by=task_table.c.asset,cascade='all', lazy=True)
})

the problem is when I do:

task=session.query(Task).options(eagerload(Task.Children)).get(('test_project','test_task'))

produces this in SQL:
SELECT <columns>
FROM task LEFT OUTER JOIN task AS task_1 ON task.asset =
task_1.parent_asset AND task.name = task.name
WHERE task.asset = %(param_1)s AND task.name = %(param_2)s ORDER BY
task_1.asset

--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgar...@creatureshop.com

[Michael Bayer]

Theres a test case like this which had a behavioral change as of
0.5.5, but looking at that, eager loading doesn't come into the picture.

for that particular test, we add foreign_keys=[task.c.parent_asset] to
the many to one side, and foreign_keys=[None] to the one-to-many
side. You might need that here just in general.

But for eagerloading I actually don't think we have a solution for
that right now. the "task_1" comes into the ON clause by way of
clause adaption, which has a list of columns that it wants to
"adapt". So "task.name" is either in or not in the list.

I'm thinking of a completely bizarre hack which would be to add a
Column to the table with the same name as "name", but a different key,
then setting up primaryjoin using that. But I don't know if that
would do it.

Otherwise you might just take the easy route and say:

t2 = aliased(Task)
s.query(Task).join(t2, and_(Task.parent_asset==t2.asset,
Task.name==t2.name)).options(contains_eager("parent" , alias=t2))

[David Gardner]

Actually I hadn't realized that the problem only occurred on eagerloading.

Would it make sense to be able to do an alias at the table level? In
other words:

task_parent=aliased(task_table)

mapper(Task,task_table, properties={
'Children' : relation(Task, backref=backref('Parent',

primaryjoin=(and_(task_parent.c.asset==task_table.c.parent_asset,

task_parent.c.name==task_table.c.name)),
remote_side=[task_parent.c.asset,
task_parent.c.name]),

order_by=task_table.c.asset,cascade='all',
lazy=True)
})

Michael Bayer wrote:
> Theres a test case like this which had a behavioral change as of
> 0.5.5, but looking at that, eager loading doesn't come into the picture.
>
> for that particular test, we add foreign_keys=[task.c.parent_asset] to
> the many to one side, and foreign_keys=[None] to the one-to-many
> side. You might need that here just in general.
>
> But for eagerloading I actually don't think we have a solution for
> that right now. the "task_1" comes into the ON clause by way of
> clause adaption, which has a list of columns that it wants to
> "adapt". So "task.name" is either in or not in the list.
>
> I'm thinking of a completely bizarre hack which would be to add a
> Column to the table with the same name as "name", but a different key,
> then setting up primaryjoin using that. But I don't know if that
> would do it.
>
> Otherwise you might just take the easy route and say:
>
> t2 = aliased(Task)
> s.query(Task).join(t2, and_(Task.parent_asset==t2.asset,
> Task.name==t2.name)).options(contains_eager("parent" , alias=t2))
>
>
>

[Michael Bayer]

On Nov 9, 2009, at 1:09 PM, David Gardner wrote:

>
> Actually I hadn't realized that the problem only occurred on
> eagerloading.
>
> Would it make sense to be able to do an alias at the table level? In
> other words:
>
> task_parent=aliased(task_table)
> mapper(Task,task_table, properties={
> 'Children' : relation(Task, backref=backref('Parent',
>
> primaryjoin=(and_(task_parent.c.asset==task_table.c.parent_asset,
>
> task_parent.c.name==task_table.c.name)),
> remote_side=[task_parent.c.asset,
> task_parent.c.name]),
> order_by=task_table.c.asset,cascade='all',
> lazy=True)
> })

that wouldn't work for a variety of reasons. aliased() doesn't work
at that level. you'd at least want to use a table alias, i.e.
task_table.alias() - but then you'd need to map the class twice, and
then you're dealing with something totally different.

there's a way to make this work which I'll eventually get to in ticket
1612.

[David Gardner]

Thanks for looking into this, just as an fyi this isn't effecting any production code for me,
I still have the option of implementing parent/children using an integer id's column.

Michael Bayer wrote:

at that level.  you'd at least want to use a table alias, i.e.  
task_table.alias() - but then you'd need to map the class twice, and  
then you're dealing with something totally different.

there's a way to make this work which I'll eventually get to in ticket  
1612.