Minilang enhancement suggestion

[Michael Imhof]

I would like to make a database query on a archiv database with millions of datasets.
But I'm only interested by the current valid entries (would be around 5000).

Ofbiz minilang provides following commands for such queries: <find-by-and/> and <filter-list-by-date/>.

But if I look at the code, then <find-by-and/> is reading all entries (Well, in blocks if I use the iterator)
and then, with <filter-list-by-date/>, a date filter is applied on the found entries!

I suggest an enhancement of the minilang commang <find-by-and/> (and <remove-by-and/>, ...):
A new Element <where/> as subelement from <find-by-and/>

        <find-by-and entity-name="Archivindex" map-name="searchMap" list-name="list">
            <where field-name="dateFrom" operator="less-equals"  env-name="parameters.date"/>
            <where field-name="dateTo" operator="greater-equals" env-name="parameters.date"/>
        </find-by-and>

With this enhancement, it would be possible to make custom database queries...

What do you think about this??

Regards
Michael

[Jacopo Cappellato]

Immi,

<find-by-and> has already an attribute that can be used for this:

filter-by-date

for example:

<find-by-and filter-by-date="true" entity-name="Archivindex"
map-name="searchMap" list-name="list"/>

Or you should use the more flexible:

<entity-condition> operator instead of <find-by-and>

For a full list of available options you should look at:

framework/minilang/dtd/simple-methods.xsd

Jacopo


Immi wrote:

> I would like to make a database query on a archiv database with millions of
> datasets.
> But I'm only interested by the current valid entries (would be around 5000).
>
> Ofbiz minilang provides following commands for such queries: <find-by-and/>
> and <filter-list-by-date/>.
>
> But if I look at the code, then <find-by-and/> is reading all entries (Well,
> in blocks if I use the iterator)
> and then, with <filter-list-by-date/>, a date filter is applied on the found
> entries!
>
> I suggest an enhancement of the minilang commang <find-by-and/> (and
> <remove-by-and/>, ...):
> A new Element <where/> as subelement from <find-by-and/>
>
>         <find-by-and entity-name="Archivindex" map-name="searchMap"
> list-name="list">
>             <where field-name="dateFrom" operator="less-equals"
> env-name="parameters.date"/>
>             <where field-name="dateTo" operator="greater-equals"
> env-name="parameters.date"/>
>         </find-by-and>
>
> With this enhancement, it would be possible to make custom database
> queries...
>
> What do you think about this??
>
> Regards
> Michael
>
>
>

[Michael Imhof]

You're right. But this are filters and are applied after reading from the database.
That means ofbiz is reading all my datasets (over a million) from the database
before filtering out the valid datasets.
If there are 3 datasets out of a million wich are valid today, I doesn't seem to
be very performant to read million datasets and apply the filter on them before
returning the 3 found datasets...

Michael

Jacopo Cappellato wrote

Immi,

<find-by-and> has already an attribute that can be used for this:

filter-by-date

for example:

<find-by-and filter-by-date="true" entity-name="Archivindex"
map-name="searchMap" list-name="list"/>

Or you should use the more flexible:

<entity-condition> operator instead of <find-by-and>

For a full list of available options you should look at:

framework/minilang/dtd/simple-methods.xsd

Jacopo


Immi wrote:
> I would like to make a database query on a archiv database with millions of
> datasets.
> But I'm only interested by the current valid entries (would be around 5000).
>
> Ofbiz minilang provides following commands for such queries: <find-by-and/>
> and <filter-list-by-date/>.
>
> But if I look at the code, then <find-by-and/> is reading all entries (Well,
> in blocks if I use the iterator)
> and then, with <filter-list-by-date/>, a date filter is applied on the found
> entries!
>
> I suggest an enhancement of the minilang commang <find-by-and/> (and
> <remove-by-and/>, ...):
> A new Element <where/> as subelement from <find-by-and/>
>
>         <find-by-and entity-name="Archivindex" map-name="searchMap"
> list-name="list">
>             <where field-name="dateFrom" operator="less-equals"
> env-name="parameters.date"/>
>             <where field-name="dateTo" operator="greater-equals"
> env-name="parameters.date"/>
>         </find-by-and>
>
> With this enhancement, it would be possible to make custom database
> queries...
>
> What do you think about this??
>
> Regards
> Michael
>
>
>

[Jacopo Cappellato]

Immi,

Immi wrote:
> You're right. But this are filters and are applied after reading from the
> database.

why are you saying this? Please read carefully what I suggested you to
do. It's different from what you did initially.

Jacopo

> That means ofbiz is reading all my datasets (over a million) from the
> database
> before filtering out the valid datasets.
> If there are 3 datasets out of a million wich are valid today, I doesn't
> seem to
> be very performant to read million datasets and apply the filter on them
> before
> returning the 3 found datasets...
>
> Michael
>
>
> Jacopo Cappellato wrote:
>> Immi,
>>
>> <find-by-and> has already an attribute that can be used for this:
>>
>> filter-by-date
>>
>> for example:
>>
>> <find-by-and filter-by-date="true" entity-name="Archivindex"
>> map-name="searchMap" list-name="list"/>
>>
>> Or you should use the more flexible:
>>
>> <entity-condition> operator instead of <find-by-and>
>>
>> For a full list of available options you should look at:
>>
>> framework/minilang/dtd/simple-methods.xsd
>>
>> Jacopo
>>
>>
>> Immi wrote:
>>> I would like to make a database query on a archiv database with millions
>>> of
>>> datasets.
>>> But I'm only interested by the current valid entries (would be around
>>> 5000).
>>>
>>> Ofbiz minilang provides following commands for such queries:
>>> <find-by-and/>
>>> and <filter-list-by-date/>.
>>>
>>> But if I look at the code, then <find-by-and/> is reading all entries
>>> (Well,
>>> in blocks if I use the iterator)
>>> and then, with <filter-list-by-date/>, a date filter is applied on the
>>> found
>>> entries!
>>>
>>> I suggest an enhancement of the minilang commang <find-by-and/> (and
>>> <remove-by-and/>, ...):
>>> A new Element <where/> as subelement from <find-by-and/>
>>>
>>>         <find-by-and entity-name="Archivindex" map-name="searchMap"
>>> list-name="list">
>>>             <where field-name="dateFrom" operator="less-equals"
>>> env-name="parameters.date"/>
>>>             <where field-name="dateTo" operator="greater-equals"
>>> env-name="parameters.date"/>
>>>         </find-by-and>
>>>
>>> With this enhancement, it would be possible to make custom database
>>> queries...
>>>
>>> What do you think about this??
>>>
>>> Regards
>>> Michael
>>>
>>>
>>>
>>
>>
>>
>

[Jacopo Cappellato]

Jacopo Cappellato wrote:
> Immi,
>

Well, sorry, I just noticed that your name is Michael, not "Immi".

Jacopo

[Michael Imhof]

That doesn't matter, Jacopo. You can call me Immi or Michael.
Immi is just a nickname:-)

Back to business:

I would like to test the attribute "filter-by-date" but I couldn't find it in
my "framework/minilang/dtd/simple-methods.xsd".
I just checked out the last version of ofbiz from
"http://svn.apache.org/repos/asf/ofbiz"!!

My next step would be to test reverse date validation:
- Ofbiz suggests two dates on the database: "FROM_DATE"/"THRU_DATE" and clients are searching
  for a specific date.
- Our client wants to search datasets between date1 & date2. That means we have on date field on the
  database and two dates to search with.

Is this possible with the current minilang?

Michael

Jacopo Cappellato wrote

Immi,

Immi wrote:
> You're right. But this are filters and are applied after reading from the
> database.

why are you saying this? Please read carefully what I suggested you to
do. It's different from what you did initially.

Jacopo

> That means ofbiz is reading all my datasets (over a million) from the
> database
> before filtering out the valid datasets.
> If there are 3 datasets out of a million wich are valid today, I doesn't
> seem to
> be very performant to read million datasets and apply the filter on them
> before
> returning the 3 found datasets...
>
> Michael
>
>
> Jacopo Cappellato wrote:
>> Immi,
>>
>> <find-by-and> has already an attribute that can be used for this:
>>
>> filter-by-date
>>
>> for example:
>>
>> <find-by-and filter-by-date="true" entity-name="Archivindex"
>> map-name="searchMap" list-name="list"/>
>>
>> Or you should use the more flexible:
>>
>> <entity-condition> operator instead of <find-by-and>
>>
>> For a full list of available options you should look at:
>>
>> framework/minilang/dtd/simple-methods.xsd
>>
>> Jacopo
>>
>>
>> Immi wrote:
>>> I would like to make a database query on a archiv database with millions
>>> of
>>> datasets.
>>> But I'm only interested by the current valid entries (would be around
>>> 5000).
>>>
>>> Ofbiz minilang provides following commands for such queries:
>>> <find-by-and/>
>>> and <filter-list-by-date/>.
>>>
>>> But if I look at the code, then <find-by-and/> is reading all entries
>>> (Well,
>>> in blocks if I use the iterator)
>>> and then, with <filter-list-by-date/>, a date filter is applied on the
>>> found
>>> entries!
>>>
>>> I suggest an enhancement of the minilang commang <find-by-and/> (and
>>> <remove-by-and/>, ...):
>>> A new Element <where/> as subelement from <find-by-and/>
>>>
>>>         <find-by-and entity-name="Archivindex" map-name="searchMap"
>>> list-name="list">
>>>             <where field-name="dateFrom" operator="less-equals"
>>> env-name="parameters.date"/>
>>>             <where field-name="dateTo" operator="greater-equals"
>>> env-name="parameters.date"/>
>>>         </find-by-and>
>>>
>>> With this enhancement, it would be possible to make custom database
>>> queries...
>>>
>>> What do you think about this??
>>>
>>> Regards
>>> Michael
>>>
>>>
>>>
>>
>>
>>
>

[David E Jones]

If you want these sorts of conditions the best thing is to build your  
condition tree with the entity-condition operation, then you can have  
the database filter by these conditions, etc, and all very explicitly.

-David


On Feb 22, 2007, at 6:52 AM, Immi wrote:

>
> That doesn't matter, Jacopo. You can call me Immi or Michael.
> Immi is just a nickname:-)
>
> Back to business:
>
> I would like to test the attribute "filter-by-date" but I couldn't  
> find it
> in
> my "framework/minilang/dtd/simple-methods.xsd".
> I just checked out the last version of ofbiz from
> "http://svn.apache.org/repos/asf/ofbiz"!!
>
> My next step would be to test reverse date validation:
> - Ofbiz suggests two dates on the database: "FROM_DATE"/"THRU_DATE"  
> and
> clients are searching
>   for a specific date.
> - Our client wants to search datasets between date1 & date2. That  
> means we
> have on date field on the
>   database and two dates to search with.
>
> Is this possible with the current minilang?
>
> Michael
>
>
> Jacopo Cappellato wrote:
>>
>> Immi,
>>
>> Immi wrote:
>>> You're right. But this are filters and are applied after reading  
>>> from the
>>> database.
>>
>> why are you saying this? Please read carefully what I suggested  
>> you to
>> do. It's different from what you did initially.
>>
>> Jacopo
>>
>>> That means ofbiz is reading all my datasets (over a million) from  
>>> the
>>> database
>>> before filtering out the valid datasets.
>>> If there are 3 datasets out of a million wich are valid today, I  
>>> doesn't
>>> seem to
>>> be very performant to read million datasets and apply the filter  
>>> on them
>>> before
>>> returning the 3 found datasets...
>>>
>>> Michael
>>>
>>>
>>> Jacopo Cappellato wrote:
>>>> Immi,
>>>>
>>>> <find-by-and> has already an attribute that can be used for this:
>>>>
>>>> filter-by-date
>>>>
>>>> for example:
>>>>
>>>> <find-by-and filter-by-date="true" entity-name="Archivindex"
>>>> map-name="searchMap" list-name="list"/>
>>>>
>>>> Or you should use the more flexible:
>>>>
>>>> <entity-condition> operator instead of <find-by-and>
>>>>
>>>> For a full list of available options you should look at:
>>>>
>>>> framework/minilang/dtd/simple-methods.xsd
>>>>
>>>> Jacopo
>>>>
>>>>
>>>> Immi wrote:
>>>>> I would like to make a database query on a archiv database with
>>>>> millions
>>>>> of
>>>>> datasets.
>>>>> But I'm only interested by the current valid entries (would be  
>>>>> around
>>>>> 5000).
>>>>>
>>>>> Ofbiz minilang provides following commands for such queries:
>>>>> <find-by-and/>
>>>>> and <filter-list-by-date/>.
>>>>>
>>>>> But if I look at the code, then <find-by-and/> is reading all  
>>>>> entries
>>>>> (Well,
>>>>> in blocks if I use the iterator)
>>>>> and then, with <filter-list-by-date/>, a date filter is applied  
>>>>> on the
>>>>> found
>>>>> entries!
>>>>>
>>>>> I suggest an enhancement of the minilang commang <find-by-and/>  
>>>>> (and
>>>>> <remove-by-and/>, ...):
>>>>> A new Element <where/> as subelement from <find-by-and/>
>>>>>
>>>>>         <find-by-and entity-name="Archivindex" map-
>>>>> name="searchMap"
>>>>> list-name="list">
>>>>>             <where field-name="dateFrom" operator="less-equals"
>>>>> env-name="parameters.date"/>
>>>>>             <where field-name="dateTo" operator="greater-equals"
>>>>> env-name="parameters.date"/>
>>>>>         </find-by-and>
>>>>>
>>>>> With this enhancement, it would be possible to make custom  
>>>>> database
>>>>> queries...
>>>>>
>>>>> What do you think about this??
>>>>>
>>>>> Regards
>>>>> Michael
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>>
>>
>
> --
> View this message in context: http://www.nabble.com/Minilang- 
> enhancement-suggestion-tf3271907.html#a9100199
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>

[Michael Imhof]

Ok, I got it. The <entity-condition/> is solving all my problems...

Thanks

What's about iterator

David E. Jones-2 wrote

If you want these sorts of conditions the best thing is to build your  
condition tree with the entity-condition operation, then you can have  
the database filter by these conditions, etc, and all very explicitly.

-David


On Feb 22, 2007, at 6:52 AM, Immi wrote:

>
> That doesn't matter, Jacopo. You can call me Immi or Michael.
> Immi is just a nickname:-)
>
> Back to business:
>
> I would like to test the attribute "filter-by-date" but I couldn't  
> find it
> in
> my "framework/minilang/dtd/simple-methods.xsd".
> I just checked out the last version of ofbiz from
> "http://svn.apache.org/repos/asf/ofbiz"!!
>
> My next step would be to test reverse date validation:
> - Ofbiz suggests two dates on the database: "FROM_DATE"/"THRU_DATE"  
> and
> clients are searching
>   for a specific date.
> - Our client wants to search datasets between date1 & date2. That  
> means we
> have on date field on the
>   database and two dates to search with.
>
> Is this possible with the current minilang?
>
> Michael
>
>
> Jacopo Cappellato wrote:
>>
>> Immi,
>>
>> Immi wrote:
>>> You're right. But this are filters and are applied after reading  
>>> from the
>>> database.
>>
>> why are you saying this? Please read carefully what I suggested  
>> you to
>> do. It's different from what you did initially.
>>
>> Jacopo
>>
>>> That means ofbiz is reading all my datasets (over a million) from  
>>> the
>>> database
>>> before filtering out the valid datasets.
>>> If there are 3 datasets out of a million wich are valid today, I  
>>> doesn't
>>> seem to
>>> be very performant to read million datasets and apply the filter  
>>> on them
>>> before
>>> returning the 3 found datasets...
>>>
>>> Michael
>>>
>>>
>>> Jacopo Cappellato wrote:
>>>> Immi,
>>>>
>>>> <find-by-and> has already an attribute that can be used for this:
>>>>
>>>> filter-by-date
>>>>
>>>> for example:
>>>>
>>>> <find-by-and filter-by-date="true" entity-name="Archivindex"
>>>> map-name="searchMap" list-name="list"/>
>>>>
>>>> Or you should use the more flexible:
>>>>
>>>> <entity-condition> operator instead of <find-by-and>
>>>>
>>>> For a full list of available options you should look at:
>>>>
>>>> framework/minilang/dtd/simple-methods.xsd
>>>>
>>>> Jacopo
>>>>
>>>>
>>>> Immi wrote:
>>>>> I would like to make a database query on a archiv database with
>>>>> millions
>>>>> of
>>>>> datasets.
>>>>> But I'm only interested by the current valid entries (would be  
>>>>> around
>>>>> 5000).
>>>>>
>>>>> Ofbiz minilang provides following commands for such queries:
>>>>> <find-by-and/>
>>>>> and <filter-list-by-date/>.
>>>>>
>>>>> But if I look at the code, then <find-by-and/> is reading all  
>>>>> entries
>>>>> (Well,
>>>>> in blocks if I use the iterator)
>>>>> and then, with <filter-list-by-date/>, a date filter is applied  
>>>>> on the
>>>>> found
>>>>> entries!
>>>>>
>>>>> I suggest an enhancement of the minilang commang <find-by-and/> 
>>>>> (and
>>>>> <remove-by-and/>, ...):
>>>>> A new Element <where/> as subelement from <find-by-and/>
>>>>>
>>>>>         <find-by-and entity-name="Archivindex" map-
>>>>> name="searchMap"
>>>>> list-name="list">
>>>>>             <where field-name="dateFrom" operator="less-equals"
>>>>> env-name="parameters.date"/>
>>>>>             <where field-name="dateTo" operator="greater-equals"
>>>>> env-name="parameters.date"/>
>>>>>         </find-by-and>
>>>>>
>>>>> With this enhancement, it would be possible to make custom  
>>>>> database
>>>>> queries...
>>>>>
>>>>> What do you think about this??
>>>>>
>>>>> Regards
>>>>> Michael
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>>
>>
>
> --
> View this message in context: http://www.nabble.com/Minilang- 
> enhancement-suggestion-tf3271907.html#a9100199
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>