You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Carlos Villegas <ca...@uniscope.jp> on 2004/12/29 10:15:51 UTC
deleteByQuery and relationships
Hi,
Let's say I have reference on table A to a table B and I want to delete
the A rows for which the related B instances match some criteria. Let's
say my reference field is 'b'. The nice way to do it (if it worked)
would be:
criteria.addEqualTo("b.type", "foo");
query = new QueryByCriteria(A.class, criteria);
broker.deleteByQuery(query);
However, this doesn't work. I get the following SQL:
DELETE FROM A WHERE type='foo'
where I was expecting something like
DELETE FROM A WHERE A.bid = B.id AND B.type='foo'
in case of PostgreSQL but the syntax will depend on the database.
Should this work? Or is there any reason why this is not supported or
not working now?
Carlos
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: deleteByQuery and relationships
Posted by Carlos Villegas <ca...@uniscope.jp>.
Jakob Braeuchi wrote:
> hi carlos,
>
>> should work on PostgreSQL, which DELETE syntax doesn't support
>> multiple tables, but allows using several tables in the WHERE clause:
>>
>> DELETE FROM A WHERE A.bid = B.id AND B.type='foo'
>
>
> is this the normal join syntax for postgresql ? how is table b referenced ?
> if this is the ordinary syntax i could change the delete by query
> mechanism to support joined tables as well.
>
Regular SELECT queries in PostgreSQL allow all kind of JOINs in the FROM
clause which OJB currently uses, but in the DELETE clause only this
syntax is supported. See the comment at the end of
http://www.postgresql.org/docs/7.4/interactive/sql-delete.html
>>
>> or in case of MySQL:
>>
>> DELETE A FROM A, B WHERE A.bid = B.id AND B.type='foo'
>>
>> OJB doesn't process the relationship and produces:
>>
>> DELETE FROM A WHERE type='foo'
>
>
> ojb currently does not even try to resolve references to joined tables
> in case of delete.
>
That's what I thought ;-(
>>
>> but the 'type' field doesn't exist in table A and hence the error.
>>
>> I haven't tested with MySQL which supports the extended syntax
>> including JOINS in the FROM clause. PostgreSQL is limited regarding
>> DELETEs. OJB seems to implement the relations using JOINs in the FROM
>> clause. INNER JOINS can be replaced by an AND condition in the WHERE
>> clause, but it
>
>
> the type of the join is defined in the platform class for each database.
> there's only one kind of join that can be defined.
>
The current join type for PostgreSQL on select queries is fine, but on
delete queries is not supported as explained. Note that since in the
delete query aliases are not supported either, the full table names have
to be used to qualify fields from different tables.
To support this properly, a "delete" join type may have to be added to
the platform class then. I don't think it will work with only one type
for everything.
> jakob
>
>> seems OJB doesn't try to do that. Maybe that's the reason it doesn't
>> work for PostgreSQL?
>>
>> Carlos
>>
>> Jakob Braeuchi wrote:
>>
>>> hi carlos,
>>>
>>> sorry for the wrong answer :( ojb cannot delete from multiple tables !
>>> actually i'm not sure if standard sql supports this kind of delete
>>> (mysql does afaik).
>>>
>>> jakob
>>>
>>> Jakob Braeuchi schrieb:
>>>
>>>> hi carlos,
>>>>
>>>> afaik ojb should be able to delete objects by query, no matter
>>>> whether it contains joins or not.
>>>> one thing to keep in mind: always clear the cache after execution of
>>>> delete by query.
>>>>
>>>> jakob
>>>>
>>>> Carlos Villegas schrieb:
>>>>
>>>>> Hi,
>>>>>
>>>>> Let's say I have reference on table A to a table B and I want to
>>>>> delete the A rows for which the related B instances match some
>>>>> criteria. Let's say my reference field is 'b'. The nice way to do
>>>>> it (if it worked) would be:
>>>>>
>>>>> criteria.addEqualTo("b.type", "foo");
>>>>> query = new QueryByCriteria(A.class, criteria);
>>>>> broker.deleteByQuery(query);
>>>>>
>>>>> However, this doesn't work. I get the following SQL:
>>>>>
>>>>> DELETE FROM A WHERE type='foo'
>>>>>
>>>>> where I was expecting something like
>>>>>
>>>>> DELETE FROM A WHERE A.bid = B.id AND B.type='foo'
>>>>>
>>>>> in case of PostgreSQL but the syntax will depend on the database.
>>>>>
>>>>>
>>>>> Should this work? Or is there any reason why this is not supported
>>>>> or not working now?
>>>>>
>>>>> Carlos
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>>
>>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: deleteByQuery and relationships
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi carlos,
> should work on PostgreSQL, which DELETE syntax doesn't support multiple
> tables, but allows using several tables in the WHERE clause:
>
> DELETE FROM A WHERE A.bid = B.id AND B.type='foo'
is this the normal join syntax for postgresql ? how is table b referenced ?
if this is the ordinary syntax i could change the delete by query
mechanism to support joined tables as well.
>
> or in case of MySQL:
>
> DELETE A FROM A, B WHERE A.bid = B.id AND B.type='foo'
>
> OJB doesn't process the relationship and produces:
>
> DELETE FROM A WHERE type='foo'
ojb currently does not even try to resolve references to joined tables
in case of delete.
>
> but the 'type' field doesn't exist in table A and hence the error.
>
> I haven't tested with MySQL which supports the extended syntax including
> JOINS in the FROM clause. PostgreSQL is limited regarding DELETEs. OJB
> seems to implement the relations using JOINs in the FROM clause. INNER
> JOINS can be replaced by an AND condition in the WHERE clause, but it
the type of the join is defined in the platform class for each database.
there's only one kind of join that can be defined.
jakob
> seems OJB doesn't try to do that. Maybe that's the reason it doesn't
> work for PostgreSQL?
>
> Carlos
>
> Jakob Braeuchi wrote:
>
>> hi carlos,
>>
>> sorry for the wrong answer :( ojb cannot delete from multiple tables !
>> actually i'm not sure if standard sql supports this kind of delete
>> (mysql does afaik).
>>
>> jakob
>>
>> Jakob Braeuchi schrieb:
>>
>>> hi carlos,
>>>
>>> afaik ojb should be able to delete objects by query, no matter
>>> whether it contains joins or not.
>>> one thing to keep in mind: always clear the cache after execution of
>>> delete by query.
>>>
>>> jakob
>>>
>>> Carlos Villegas schrieb:
>>>
>>>> Hi,
>>>>
>>>> Let's say I have reference on table A to a table B and I want to
>>>> delete the A rows for which the related B instances match some
>>>> criteria. Let's say my reference field is 'b'. The nice way to do it
>>>> (if it worked) would be:
>>>>
>>>> criteria.addEqualTo("b.type", "foo");
>>>> query = new QueryByCriteria(A.class, criteria);
>>>> broker.deleteByQuery(query);
>>>>
>>>> However, this doesn't work. I get the following SQL:
>>>>
>>>> DELETE FROM A WHERE type='foo'
>>>>
>>>> where I was expecting something like
>>>>
>>>> DELETE FROM A WHERE A.bid = B.id AND B.type='foo'
>>>>
>>>> in case of PostgreSQL but the syntax will depend on the database.
>>>>
>>>>
>>>> Should this work? Or is there any reason why this is not supported
>>>> or not working now?
>>>>
>>>> Carlos
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>
>>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: deleteByQuery and relationships
Posted by Carlos Villegas <ca...@uniscope.jp>.
Hi,
I'm not trying to delete from multiple tables, just from one table but
using a query that uses several related tables. The example I gave
should work on PostgreSQL, which DELETE syntax doesn't support multiple
tables, but allows using several tables in the WHERE clause:
DELETE FROM A WHERE A.bid = B.id AND B.type='foo'
or in case of MySQL:
DELETE A FROM A, B WHERE A.bid = B.id AND B.type='foo'
OJB doesn't process the relationship and produces:
DELETE FROM A WHERE type='foo'
but the 'type' field doesn't exist in table A and hence the error.
I haven't tested with MySQL which supports the extended syntax including
JOINS in the FROM clause. PostgreSQL is limited regarding DELETEs. OJB
seems to implement the relations using JOINs in the FROM clause. INNER
JOINS can be replaced by an AND condition in the WHERE clause, but it
seems OJB doesn't try to do that. Maybe that's the reason it doesn't
work for PostgreSQL?
Carlos
Jakob Braeuchi wrote:
> hi carlos,
>
> sorry for the wrong answer :( ojb cannot delete from multiple tables !
> actually i'm not sure if standard sql supports this kind of delete
> (mysql does afaik).
>
> jakob
>
> Jakob Braeuchi schrieb:
>
>> hi carlos,
>>
>> afaik ojb should be able to delete objects by query, no matter whether
>> it contains joins or not.
>> one thing to keep in mind: always clear the cache after execution of
>> delete by query.
>>
>> jakob
>>
>> Carlos Villegas schrieb:
>>
>>> Hi,
>>>
>>> Let's say I have reference on table A to a table B and I want to
>>> delete the A rows for which the related B instances match some
>>> criteria. Let's say my reference field is 'b'. The nice way to do it
>>> (if it worked) would be:
>>>
>>> criteria.addEqualTo("b.type", "foo");
>>> query = new QueryByCriteria(A.class, criteria);
>>> broker.deleteByQuery(query);
>>>
>>> However, this doesn't work. I get the following SQL:
>>>
>>> DELETE FROM A WHERE type='foo'
>>>
>>> where I was expecting something like
>>>
>>> DELETE FROM A WHERE A.bid = B.id AND B.type='foo'
>>>
>>> in case of PostgreSQL but the syntax will depend on the database.
>>>
>>>
>>> Should this work? Or is there any reason why this is not supported or
>>> not working now?
>>>
>>> Carlos
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: deleteByQuery and relationships
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi carlos,
sorry for the wrong answer :( ojb cannot delete from multiple tables !
actually i'm not sure if standard sql supports this kind of delete
(mysql does afaik).
jakob
Jakob Braeuchi schrieb:
> hi carlos,
>
> afaik ojb should be able to delete objects by query, no matter whether
> it contains joins or not.
> one thing to keep in mind: always clear the cache after execution of
> delete by query.
>
> jakob
>
> Carlos Villegas schrieb:
>
>> Hi,
>>
>> Let's say I have reference on table A to a table B and I want to
>> delete the A rows for which the related B instances match some
>> criteria. Let's say my reference field is 'b'. The nice way to do it
>> (if it worked) would be:
>>
>> criteria.addEqualTo("b.type", "foo");
>> query = new QueryByCriteria(A.class, criteria);
>> broker.deleteByQuery(query);
>>
>> However, this doesn't work. I get the following SQL:
>>
>> DELETE FROM A WHERE type='foo'
>>
>> where I was expecting something like
>>
>> DELETE FROM A WHERE A.bid = B.id AND B.type='foo'
>>
>> in case of PostgreSQL but the syntax will depend on the database.
>>
>>
>> Should this work? Or is there any reason why this is not supported or
>> not working now?
>>
>> Carlos
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: deleteByQuery and relationships
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi carlos,
afaik ojb should be able to delete objects by query, no matter whether
it contains joins or not.
one thing to keep in mind: always clear the cache after execution of
delete by query.
jakob
Carlos Villegas schrieb:
> Hi,
>
> Let's say I have reference on table A to a table B and I want to
> delete the A rows for which the related B instances match some
> criteria. Let's say my reference field is 'b'. The nice way to do it
> (if it worked) would be:
>
> criteria.addEqualTo("b.type", "foo");
> query = new QueryByCriteria(A.class, criteria);
> broker.deleteByQuery(query);
>
> However, this doesn't work. I get the following SQL:
>
> DELETE FROM A WHERE type='foo'
>
> where I was expecting something like
>
> DELETE FROM A WHERE A.bid = B.id AND B.type='foo'
>
> in case of PostgreSQL but the syntax will depend on the database.
>
>
> Should this work? Or is there any reason why this is not supported or
> not working now?
>
> Carlos
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org