select A join B where B is null

[David Gardner]

I am trying to get all of the rows in table A that do not have a match
in table B. I believe the problem is that I am using a text foreign key,
and for the rows I am looking for the field will still have a value, it
just won't match anything in table B.

To make things confusing the table 'qstatus' is mapped to a class called
'JobInfo', also the qstatus table is a temporary table populated with
values of what is currently running, so there are no foreign keys or
indexes on the table, the qstat_table.c.job_number field is unique but
isn't a primary key as far as PostgreSQL is concerned.

This is what I am trying to do:

SELECT farm.qstatus.* FROM farm.qstatus
LEFT OUTER JOIN farm.job ON qstatus.job_name=job.name
WHERE job IS NULL
ORDER BY qstatus.job_name;

This is what my mapper looks like:

mapper(JobInfo, qstat_table, primary_key=[qstat_table.c.job_number],
properties={
'Chunk':relation(Chunk, lazy=True, uselist=False),
'Job' : relation(Job,
primaryjoin=(qstat_table.c.job_name==job_table.c.name),
foreign_keys=[qstat_table.c.job_name], lazy=True, viewonly=True,
uselist=False)
}, save_on_init=False)

This is my python code:
>>> from farmdb import * #where my mappers live
>>> session=create_session()
>>> db.echo=True
>>>
session.query(JobInfo).outerjoin(JobInfo.Job).filter(JobInfo.Job==None).order_by(JobInfo.job_name).all()
2009-04-06 16:49:26,922 INFO sqlalchemy.engine.base.Engine.0x...0210
SELECT farm.qstatus.state AS farm_qstatus_state, farm.qstatus.job_name
AS farm_qstatus_job_name, farm.qstatus.priority AS
farm_qstatus_priority, farm.qstatus.job_number AS
farm_qstatus_job_number, farm.qstatus.owner AS farm_qstatus_owner,
farm.qstatus.start_time AS farm_qstatus_start_time, farm.qstatus.queue
AS farm_qstatus_queue, farm.qstatus.slots AS farm_qstatus_slots,
farm.qstatus.req_que AS farm_qstatus_req_que, farm.qstatus.s_rt AS
farm_qstatus_s_rt, farm.qstatus.h_rt AS farm_qstatus_h_rt
FROM farm.qstatus LEFT OUTER JOIN farm.job ON farm.qstatus.job_name =
farm.job.name
WHERE farm.qstatus.job_name IS NULL ORDER BY farm.qstatus.job_name
2009-04-06 16:49:26,922 INFO sqlalchemy.engine.base.Engine.0x...0210 {}
[]

--
David Gardner
Pipeline Tools Programmer, "Sid the Science Kid"
Jim Henson Creature Shop
dgar...@creatureshop.com

[Michael Bayer]

On Apr 6, 2009, at 8:11 PM, David Gardner wrote:

>
> I am trying to get all of the rows in table A that do not have a match
> in table B. I believe the problem is that I am using a text foreign
> key,
> and for the rows I am looking for the field will still have a value,
> it
> just won't match anything in table B.
>
> To make things confusing the table 'qstatus' is mapped to a class
> called
> 'JobInfo', also the qstatus table is a temporary table populated with
> values of what is currently running, so there are no foreign keys or
> indexes on the table, the qstat_table.c.job_number field is unique but
> isn't a primary key as far as PostgreSQL is concerned.
>
> This is what I am trying to do:
>
> SELECT farm.qstatus.* FROM farm.qstatus
> LEFT OUTER JOIN farm.job ON qstatus.job_name=job.name
> WHERE job IS NULL
> ORDER BY qstatus.job_name;

this is usually easiest via NOT EXISTS

select * from table where not exists (select 1 from othertable where
othertable.foo=table.bar)

> mapper(JobInfo, qstat_table, primary_key=[qstat_table.c.job_number],
> properties={
> 'Chunk':relation(Chunk, lazy=True, uselist=False),
> 'Job' : relation(Job,
> primaryjoin=(qstat_table.c.job_name==job_table.c.name),
> foreign_keys=[qstat_table.c.job_name], lazy=True, viewonly=True,
> uselist=False)
> }, save_on_init=False)

so a query like this would do it:

session.query(JobInfo).filter(~JobInfo.Job.has())

[David Gardner]

Thanks that ended up working.

> so a query like this would do it:
>
> session.query(JobInfo).filter(~JobInfo.Job.has()