Regarding the implementation of the "in" operator...

[Carl Michael Skog]

>> Interesting is also to filter on which objects is NOT in a collection.
>> Applying the 'not' operator on the 'in' operator leads to following
>> condition (same conditions as above except the not operator applied).
>> 'NOT (theArticles.id=t.tabCarModel_Articles AND
>> t.tabArt_CarModels=theCarModel.id)'
>> That is a simple 'NOT' added around the equijoin.
>> This is, of course, very wrong(the Cartesian product except the number
>> of related objects).
>
>Right, negating the join clauses does not seem to be what you want. I
>think it would be better to have a <notin /> operator that would
>generate the correct SQL.
>
>If I am not mistaken not in would correctly implemented with a NOT IN
>SQL clause followd by a sub-select right? I am not sure if this would be
>the best method.

I think an EXIST sub-select is semantically the right thing to use for
the in-operator. After all, we are interested in filtering out the
objects or rows of some class that are related with some objects of
another class, no matter how many.
We are NOT interested enumerating the relations also, which we are
doing when adding an equi-join.

One other advantage, you can use the "not"(for filtering out objects
that are NOT related) operator as it is implemented today. This does
not work with the current "equi-join" implementation.

Also, adding a "notin" operator seems a bit like pollution...
Using a <not><in/></not> would be illegal in that case ?
Then you would need extra code on the "not" operator to pick out that
case...

I.e. (using my many-to-many example)instead of using an equi-join(i.e.
add the many-relation table, and add a where-clause
'theArticles.id=t.tabCarModel_Articles AND
t.tabArt_CarModels=theCarModel.id'), instead use EXIST(i.e. add an
extra where condition 'exists(select * from
tabart_carmodels_tabcarmodel_articles where theArticles.id =
tabart_carmodels_tabcarmodel_articles.tabCarModel_Articles)').
This works analogously for 1-many relations also...

Regards,
Carl Michael Skog








 
Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/metal-dev/

<*> To unsubscribe from this group, send an email to:
    metal-dev-unsubscribe@...

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/
 

[mlemos]

Hello,

on 02/13/2006 09:33 AM Carl Michael Skog said the following:

>>> Interesting is also to filter on which objects is NOT in a collection.
>>> Applying the 'not' operator on the 'in' operator leads to following
>>> condition (same conditions as above except the not operator applied).
>>> 'NOT (theArticles.id=t.tabCarModel_Articles AND
>>> t.tabArt_CarModels=theCarModel.id)'
>>> That is a simple 'NOT' added around the equijoin.
>>> This is, of course, very wrong(the Cartesian product except the number
>>> of related objects).
>> Right, negating the join clauses does not seem to be what you want. I
>> think it would be better to have a <notin /> operator that would
>> generate the correct SQL.
>>
>> If I am not mistaken not in would correctly implemented with a NOT IN
>> SQL clause followd by a sub-select right? I am not sure if this would be
>> the best method.
>
> I think an EXIST sub-select is semantically the right thing to use for
> the in-operator. After all, we are interested in filtering out the
> objects or rows of some class that are related with some objects of
> another class, no matter how many.
> We are NOT interested enumerating the relations also, which we are
> doing when adding an equi-join.

Maybe I am missing something here but in practice using NOT EXISTS or
NOT IN, it is the same thing.



> One other advantage, you can use the "not"(for filtering out objects
> that are NOT related) operator as it is implemented today. This does
> not work with the current "equi-join" implementation.

Right, but I am trying to avoid sub-selects in situations that I can,
because some databases do not support it. I guess for not in, it is
hopeless, but in can be done without sub-selects.


> Also, adding a "notin" operator seems a bit like pollution...
> Using a <not><in/></not> would be illegal in that case ?
> Then you would need extra code on the "not" operator to pick out that
> case...

Right, I do not like notin either, it becomes less intuitive . I am
considering hacking the OQL engine to support expression inference. I
mean <not> <object /> <in /> <collection /> </not> would use sub-select
but <object /> <in /> <collection /> would use joins.


> I.e. (using my many-to-many example)instead of using an equi-join(i.e.
> add the many-relation table, and add a where-clause
> 'theArticles.id=t.tabCarModel_Articles AND
> t.tabArt_CarModels=theCarModel.id'), instead use EXIST(i.e. add an
> extra where condition 'exists(select * from
> tabart_carmodels_tabcarmodel_articles where theArticles.id =
> tabart_carmodels_tabcarmodel_articles.tabCarModel_Articles)').
> This works analogously for 1-many relations also...

I made a few tests with PostgreSQL 8 and I could achieve the desired
effect with NOT IN:

SELECT theArticles.Name, theCarModel.Name FROM tabArt theArticles,
tabCarModel theCarModel WHERE theArticles.id NOT IN (SELECT
t.tabArt_CarModels FROM tabCarModel theCarModel,
tabArt_CarModels_tabCarModel_Articles t WHERE
t.tabCarModel_Articles=theCarModel.id)

and with NOT EXISTS .

SELECT theArticles.Name, theCarModel.Name FROM tabArt theArticles,
tabCarModel theCarModel WHERE NOT EXISTS (SELECT * FROM tabCarModel
theCarModel, tabArt_CarModels_tabCarModel_Articles t WHERE
theArticles.id=t.tabArt_CarModels AND t.tabCarModel_Articles=theCarModel.id)

I have not yet hacked the OQL engine yet. I am not sure about the pros
and cons of each approach as both seem equal in practice. What do you think?

--

Regards,
Manuel Lemos

Metastorage - Data object relational mapping layer generator
http://www.metastorage.net/

PHP Classes - Free ready to use OOP components written in PHP
http://www.phpclasses.org/


 
Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/metal-dev/

<*> To unsubscribe from this group, send an email to:
    metal-dev-unsubscribe@...

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/
 

[Carl Michael Skog]

--- In metal-dev@..., Manuel Lemos <mlemos@...> wrote:
>
> Hello,

Hi !

>
> Maybe I am missing something here but in practice using NOT EXISTS or
> NOT IN, it is the same thing.
>
More or less.
IN subquery enumerates values to be used the query, whereas enumerates
rows.

These are equivalent:
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);
SELECT * FROM t1 WHERE EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

The argument that sub-queries is not supported in earlier MySQL is, of
course, valid.
Remember, though, that a DISTINCT is needed in the select.
We want to filter objects, not multiply them (in case the object are
related twice or more times to objects on the "other" side of the
collection).
I.e., if you want to avoid subqueries, normal "in" should result in a
query like(I am using my old example):
SELECT DISTINCT theArticles.Name FROM tabArt theArticles,
tabart_carmodels_tabcarmodel_articles t WHERE
theArticles.id=t.tabArt_CarModels AND
t.tabCarModel_Articles=theCarModel.id;

> Right, but I am trying to avoid sub-selects in situations that I can,
> because some databases do not support it. I guess for not in, it is
> hopeless, but in can be done without sub-selects.
>

For <not><in/></not> you can generate:
SELECT theArticles.Name FROM tabArt theArticles
  LEFT JOIN tabart_carmodels_tabcarmodel_articles t ON
theArticles.id=t.tabArt_CarModels WHERE t.tabArt_CarModels IS NULL;

Regards,
Carl Michael Skog

> > Also, adding a "notin" operator seems a bit like pollution...
> > Using a <not><in/></not> would be illegal in that case ?
> > Then you would need extra code on the "not" operator to pick out that
> > case...
>
> Right, I do not like notin either, it becomes less intuitive . I am
> considering hacking the OQL engine to support expression inference. I
> mean <not> <object /> <in /> <collection /> </not> would use sub-select
> but <object /> <in /> <collection /> would use joins.
>
>
> > I.e. (using my many-to-many example)instead of using an equi-join(i.e.
> > add the many-relation table, and add a where-clause
> > 'theArticles.id=t.tabCarModel_Articles AND
> > t.tabArt_CarModels=theCarModel.id'), instead use EXIST(i.e. add an
> > extra where condition 'exists(select * from
> > tabart_carmodels_tabcarmodel_articles where theArticles.id =
> > tabart_carmodels_tabcarmodel_articles.tabCarModel_Articles)').
> > This works analogously for 1-many relations also...
>
> I made a few tests with PostgreSQL 8 and I could achieve the desired
> effect with NOT IN:
>
> SELECT theArticles.Name, theCarModel.Name FROM tabArt theArticles,
> tabCarModel theCarModel WHERE theArticles.id NOT IN (SELECT
> t.tabArt_CarModels FROM tabCarModel theCarModel,
> tabArt_CarModels_tabCarModel_Articles t WHERE
> t.tabCarModel_Articles=theCarModel.id)
>
> and with NOT EXISTS .
>
> SELECT theArticles.Name, theCarModel.Name FROM tabArt theArticles,
> tabCarModel theCarModel WHERE NOT EXISTS (SELECT * FROM tabCarModel
> theCarModel, tabArt_CarModels_tabCarModel_Articles t WHERE
> theArticles.id=t.tabArt_CarModels AND

t.tabCarModel_Articles=theCarModel.id)
>
> I have not yet hacked the OQL engine yet. I am not sure about the pros
> and cons of each approach as both seem equal in practice. What do
you think?

>
> --
>
> Regards,
> Manuel Lemos
>
> Metastorage - Data object relational mapping layer generator
> http://www.metastorage.net/
>
> PHP Classes - Free ready to use OOP components written in PHP
> http://www.phpclasses.org/
>

 
Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/metal-dev/

<*> To unsubscribe from this group, send an email to:
    metal-dev-unsubscribe@...

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/
 

[mlemos]

Hello,

on 02/14/2006 11:39 PM Carl Michael Skog said the following:

>> Maybe I am missing something here but in practice using NOT EXISTS or
>> NOT IN, it is the same thing.
>>
> More or less.
> IN subquery enumerates values to be used the query, whereas enumerates
> rows.
>
> These are equivalent:
> SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);
> SELECT * FROM t1 WHERE EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

Right, this difference detail will be more important when multiple
explicit OID fields will be supported. I think that is a good reason to
opt for NOT EXISTS right now.

> Remember, though, that a DISTINCT is needed in the select.
> We want to filter objects, not multiply them (in case the object are
> related twice or more times to objects on the "other" side of the
> collection).
> I.e., if you want to avoid subqueries, normal "in" should result in a
> query like(I am using my old example):
> SELECT DISTINCT theArticles.Name FROM tabArt theArticles,
> tabart_carmodels_tabcarmodel_articles t WHERE
> theArticles.id=t.tabArt_CarModels AND
> t.tabCarModel_Articles=theCarModel.id;

Sorry I did not follow your reasoning for the need of DISTINCT.

In a many to many relationship, an object may be related with more than
one object of another class.

If want to avoid duplicate rows in reports, there is the need for
DISTINCT yes, but at the report definition level. DISTINT support in
reports is not yet implemented. It may be a good thing to add, but that
would be a distinct feature.

Now, for other uses of filter expressions I am not sure we really need
to use DISTINCT . Can you give me an example?


>> Right, but I am trying to avoid sub-selects in situations that I can,
>> because some databases do not support it. I guess for not in, it is
>> hopeless, but in can be done without sub-selects.
>>
>
> For <not><in/></not> you can generate:
> SELECT theArticles.Name FROM tabArt theArticles
>   LEFT JOIN tabart_carmodels_tabcarmodel_articles t ON
> theArticles.id=t.tabArt_CarModels WHERE t.tabArt_CarModels IS NULL;

That can be another idea. I have never used LEFT JOIN so I am not yet
very familiar with the syntax . What if you want to add conditions to
the objects of other side? I mean for instance, you want to all articles
that are not related to car models with the name 'BMW' ?

--

Regards,
Manuel Lemos

Metastorage - Data object relational mapping layer generator
http://www.metastorage.net/

PHP Classes - Free ready to use OOP components written in PHP
http://www.phpclasses.org/


 
Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/metal-dev/

<*> To unsubscribe from this group, send an email to:
    metal-dev-unsubscribe@...

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/
 

[Carl Michael Skog]

Hello !

--- In metal-dev@..., Manuel Lemos <mlemos@...> wrote:
> Sorry I did not follow your reasoning for the need of DISTINCT.
>
> In a many to many relationship, an object may be related with more than
> one object of another class.

The "in" operator is about filtering a set of objects on the property
of the objects in that set being part of a relation to another class
of object.
Either the object is related to an object of the other kind, OR not.
Whether it is related to 1 or 1 million objects of the other kind is
immaterial.
Either the "in" operator includes the object in the result, or it doesn't.
In particular, IF it is included in the result, it should be included
ONCE, not 2, 3 or 1 million copies.

If a straight select is used like now, data for an object will be
included each time an object is in the relation.

Yes, there is equally often a need to ENUMERATE the collection, but
that should be another operator("enum", perhaps ?).

So, a DISTINCT is needed in the SELECT for the "in" operator


> If want to avoid duplicate rows in reports, there is the need for
> DISTINCT yes, but at the report definition level. DISTINT support in
> reports is not yet implemented. It may be a good thing to add, but that
> would be a distinct feature.
>
> Now, for other uses of filter expressions I am not sure we really need
> to use DISTINCT . Can you give me an example?
DISTINCT is too low level to have any use in OQL.
Se my reasoning above.


> That can be another idea. I have never used LEFT JOIN so I am not yet
> very familiar with the syntax . What if you want to add conditions to
> the objects of other side? I mean for instance, you want to all articles
> that are not related to car models with the name 'BMW' ?

This is all articles that are *not* related to car models(it might be
related to other models or none at all) with the name 'BMW':
SELECT theArticles.Name FROM tabArt theArticles, tabCarModel LEFT JOIN
tabart_carmodels_tabcarmodel_articles t ON
theArticles.id=t.tabArt_CarModels AND
t.tabCarModel_Articles=tabCarModel.id WHERE t.tabArt_CarModels IS NULL
AND tabCarModel.Name = "BMW";

I guess, this would be something like this "virtual" OQL(it is a good
idea to be able to add conditions on the other side, as you say):
  <query>

    <name>querynonbmw</name>

    <object>
      <name>theArticles</name>
      <class>tabArt</class>
    </object>

    <column>
      <name>Name</name>
      <value>
                <variable>
                  <name>Name</name>
                  <object>theArticles</object>
                </variable>
          </value>
    </column>

        <filter>
          <object>
                <name>theArticles</name>
          </object>
          <in/>
          <collection>
                <name>Articles</name>
                <object>theCarModel</object>
                <filter>
                        <variable>
                                <object>theCarModel</object>
                                <name>Name</name>
                        </variable>
                        <equalto/>
                        <text>BMW</text>
                </filter>
          </collection>
        </filter>

  </query>



Regards,
Carl Michael Skog






 
Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/metal-dev/

<*> To unsubscribe from this group, send an email to:
    metal-dev-unsubscribe@...

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/
 

[mlemos]

Hello,

on 02/15/2006 11:05 PM Carl Michael Skog said the following:

>> Sorry I did not follow your reasoning for the need of DISTINCT.
>>
>> In a many to many relationship, an object may be related with more than
>> one object of another class.
>
> The "in" operator is about filtering a set of objects on the property
> of the objects in that set being part of a relation to another class
> of object.
> Either the object is related to an object of the other kind, OR not.
> Whether it is related to 1 or 1 million objects of the other kind is
> immaterial.

I am not sure if that is what you are thinking but currently an object
may not be related to another object more than once.

Even if by design you would like to allow an object be added to another
related object more than once, in the current implementation that is
impossible because the many-to-many relationship table uses the fields
of the OIDs as primary keys, so all objects must not be NULL and the
combination must be unique.

> Either the "in" operator includes the object in the result, or it doesn't.
> In particular, IF it is included in the result, it should be included
> ONCE, not 2, 3 or 1 million copies.
>
> If a straight select is used like now, data for an object will be
> included each time an object is in the relation.
>
> Yes, there is equally often a need to ENUMERATE the collection, but
> that should be another operator("enum", perhaps ?).
>
> So, a DISTINCT is needed in the SELECT for the "in" operator

That would be true if you would not care about the object on the other
side. When an object is in the same relationship more than once, that is
because it is related with more than one distinct object.

If you use an OQL expression with the in operator for a report query
filter, the DISTINCT clause does not eliminate repeated appearances of
the same object in the results if you make define report columns  with
the OID of the objects of both sides of the in operator.


>> If want to avoid duplicate rows in reports, there is the need for
>> DISTINCT yes, but at the report definition level. DISTINT support in
>> reports is not yet implemented. It may be a good thing to add, but that
>> would be a distinct feature.
>>
>> Now, for other uses of filter expressions I am not sure we really need
>> to use DISTINCT . Can you give me an example?
> DISTINCT is too low level to have any use in OQL.
> Se my reasoning above.

I was not thinking of DISTINCT for the OQL but rather for eliminating
duplicated rows in report query results.

>> That can be another idea. I have never used LEFT JOIN so I am not yet
>> very familiar with the syntax . What if you want to add conditions to
>> the objects of other side? I mean for instance, you want to all articles
>> that are not related to car models with the name 'BMW' ?
>
> This is all articles that are *not* related to car models(it might be
> related to other models or none at all) with the name 'BMW':
> SELECT theArticles.Name FROM tabArt theArticles, tabCarModel LEFT JOIN
> tabart_carmodels_tabcarmodel_articles t ON
> theArticles.id=t.tabArt_CarModels AND
> t.tabCarModel_Articles=tabCarModel.id WHERE t.tabArt_CarModels IS NULL
> AND tabCarModel.Name = "BMW";

Something is not right, but I do not quite understand the LEFT JOIN
syntax to figure what it is the problem.

I run the query in PostgreSQL and it failed with the error:

ERROR:  relation "thearticles" does not exist

> I guess, this would be something like this "virtual" OQL(it is a good
> idea to be able to add conditions on the other side, as you say):
>   <query>
>
>     <name>querynonbmw</name>
>
>     <object>
>       <name>theArticles</name>
>       <class>tabArt</class>
>     </object>
>
>     <column>
>       <name>Name</name>
>       <value>
> <variable>
>  <name>Name</name>
>  <object>theArticles</object>
> </variable>
>  </value>
>     </column>
>
> <filter>
>  <object>
> <name>theArticles</name>
>  </object>
>  <in/>
>  <collection>
> <name>Articles</name>
> <object>theCarModel</object>
> <filter>
> <variable>
> <object>theCarModel</object>
> <name>Name</name>
> </variable>
> <equalto/>
> <text>BMW</text>
> </filter>
>  </collection>
> </filter>
>
>   </query>

Yes, I was thinking of something more natural like this:

<filter>

  <object>
    <name>theArticles</name>
  </object>
 <in/>
  <collection>
   <name>Articles</name>
   <object>theCarModel</object>
  </collection>

 <and />

  <variable>
   <object>theCarModel</object>
   <name>Name</name>
  </variable>
 <equalto/>
  <text>BMW</text>

</filter>



--

Regards,
Manuel Lemos

Metastorage - Data object relational mapping layer generator
http://www.metastorage.net/

PHP Classes - Free ready to use OOP components written in PHP
http://www.phpclasses.org/


 
Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/metal-dev/

<*> To unsubscribe from this group, send an email to:
    metal-dev-unsubscribe@...

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/
 

[Carl Michael Skog]

--- In metal-dev@..., Manuel Lemos <mlemos@...> wrote:

> > SELECT theArticles.Name FROM tabArt theArticles, tabCarModel LEFT JOIN
> > tabart_carmodels_tabcarmodel_articles t ON
> > theArticles.id=t.tabArt_CarModels AND
> > t.tabCarModel_Articles=tabCarModel.id WHERE t.tabArt_CarModels IS NULL
> > AND tabCarModel.Name = "BMW";
>
> Something is not right, but I do not quite understand the LEFT JOIN
> syntax to figure what it is the problem.
>
> I run the query in PostgreSQL and it failed with the error:
>
> ERROR:  relation "thearticles" does not exist

Test:
SELECT theArticles.Name FROM tabArt theArticles, tabCarModel LEFT
OUTER JOIN
tabart_carmodels_tabcarmodel_articles t ON
theArticles.id=t.tabArt_CarModels AND
t.tabCarModel_Articles=tabCarModel.id WHERE t.tabArt_CarModels IS NULL
AND tabCarModel.Name = "BMW";

Seems "LEFT JOIN" is unknown to PostgreSQL.
The join name should be "LEFT OUTER JOIN".
I don't know if there are other subtle differences in the syntax.
I tested the query in MySQL.

/CM






 
Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/metal-dev/

<*> To unsubscribe from this group, send an email to:
    metal-dev-unsubscribe@...

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/
 

[Carl Michael Skog]

This works both in MySQL 4.1 and PostgreSQL 8.1:

SELECT theArticles.Name FROM (tabArt theArticles LEFT JOIN
(tabCarModel LEFT JOIN tabart_carmodels_tabcarmodel_articles t ON
t.tabCarModel_Articles=tabCarModel.id) ON theArt
icles.id=t.tabArt_CarModels) WHERE t.tabArt_CarModels IS NULL AND
tabCarModel.Name = 'BMW';

/CM






 
Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/metal-dev/

<*> To unsubscribe from this group, send an email to:
    metal-dev-unsubscribe@...

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/
 

[mlemos]

Hello,

on 02/17/2006 10:54 PM Carl Michael Skog said the following:
> This works both in MySQL 4.1 and PostgreSQL 8.1:
>
> SELECT theArticles.Name FROM (tabArt theArticles LEFT JOIN
> (tabCarModel LEFT JOIN tabart_carmodels_tabcarmodel_articles t ON
> t.tabCarModel_Articles=tabCarModel.id) ON theArt
> icles.id=t.tabArt_CarModels) WHERE t.tabArt_CarModels IS NULL AND
> tabCarModel.Name = 'BMW';

Thanks. I need to study this better to decide whether it is worth to try
 LEFT JOIN or NOT EXISTS for implementing not in. I want to release a
new version soon with not in support.


--

Regards,
Manuel Lemos

Metastorage - Data object relational mapping layer generator
http://www.metastorage.net/

PHP Classes - Free ready to use OOP components written in PHP
http://www.phpclasses.org/


 
Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/metal-dev/

<*> To unsubscribe from this group, send an email to:
    metal-dev-unsubscribe@...

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/