Smalltalk › Frameworks & Tools › GLORP

SQL in() in GLORP?

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

8 messages Options

jtuchel

SQL in() in GLORP?

Hi,

Sometimes it would be great to have teh ability to write a SELECT like this:

myCollection := self someObjectsWithSpecialProperties collect: [:ea| ea id].
dbSession readManyOf: MyClass where: [:ea| (ea amount > 300) & (myCollection includes: ea id)].

Which then translates to:

SELECT ... FROM MYTABLE WHERE AMNT > 300 and ID in (100,103,765,999, etc.)

It seems like GLORP doesn't support this.

Am I wrong?

Looking at implementors of #initializeFunctions it seems like there is no IN-Function and to be honest, I can't see how I could add my own.

Joachim

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

Maarten Mostert

RE: SQL in() in GLORP?

Hi Joachim,

1) If you want to build a query based on multiple criteria you can do something like.

myQuery := Glorp.Query readOneOf: ExceptionDay
where: [:each | each associated_resource = resourceTableTree selections first].
myQuery := myQuery AND: [:each | each day_concerned = aDate].
thisHolliday := self getGlorpSession execute: myQuery.

you can also use OR:

2) the class GlorpExpressio contains the operator in: which does exactly what you want. I used to have code for that but that was many moons ago. The problem with using in is that the collection you give it is limited in size on certain platforms (notably ODBC) to a couple of thousand occurrences which caused me problems at that time.

Maybe if you run all the test you will be able to find an example of how to use it in VW.

Regards,

@+Maarten

28 Av Alphonse Denis

83400 Hyères, France

+33 676411296

http://stakepoint.com/

> "jtuchel" <[hidden email]> |

Esteban A. Maringolo

Re: SQL in() in GLORP?

2015-10-05 10:36 GMT-03:00 <[hidden email]>:

Hi Joachim,

1) If you want to build a query based on multiple criteria you can do something like.

myQuery := Glorp.Query readOneOf: ExceptionDay
where: [:each | each associated_resource = resourceTableTree selections first].
myQuery := myQuery AND: [:each | each day_concerned = aDate].
thisHolliday := self getGlorpSession execute: myQuery.

you can also use OR:

2) the class GlorpExpressio contains the operator in: which does exactly what you want. I used to have code for that but that was many moons ago. The problem with using in is that the collection you give it is limited in size on certain platforms (notably ODBC) to a couple of thousand occurrences which caused me problems at that time.

Maybe if you run all the test you will be able to find an example of how to use it in VW.

The current Pharo version doesn't include the #in: operator and it's also buggy in its MessageArchiver, however I agree with Maarten about the use of IN, it's limited in many platforms.

I ended up using OR expressions, or even better EXISTS whenever possible.

It wouldn't be difficult to implement an IN operator using a collection of literals.

Regards

Esteban A. Maringolo

pd: Maarten, which VW and GLORP version are you using? I downloaded the latest one (8.0.1) and all the sources of Glorp packages are decompiled.

Alan Knight

Re: SQL in() in GLORP?

If the first criterion is also going against the database, you can also do it as a subselect, which doesn't have size limits. But if the size of that collection is relatively small, or it's purely in-memory, then using in: would probably work best.

On Mon, Oct 5, 2015 at 6:49 AM Esteban A. Maringolo <[hidden email]> wrote:

2015-10-05 10:36 GMT-03:00 <[hidden email]>:
Hi Joachim,

1) If you want to build a query based on multiple criteria you can do something like.

myQuery := Glorp.Query readOneOf: ExceptionDay
where: [:each | each associated_resource = resourceTableTree selections first].
myQuery := myQuery AND: [:each | each day_concerned = aDate].
thisHolliday := self getGlorpSession execute: myQuery.

you can also use OR:

2) the class GlorpExpressio contains the operator in: which does exactly what you want. I used to have code for that but that was many moons ago. The problem with using in is that the collection you give it is limited in size on certain platforms (notably ODBC) to a couple of thousand occurrences which caused me problems at that time.

Maybe if you run all the test you will be able to find an example of how to use it in VW.

The current Pharo version doesn't include the #in: operator and it's also buggy in its MessageArchiver, however I agree with Maarten about the use of IN, it's limited in many platforms.

I ended up using OR expressions, or even better EXISTS whenever possible.

It wouldn't be difficult to implement an IN operator using a collection of literals.

Regards

Esteban A. Maringolo

pd: Maarten, which VW and GLORP version are you using? I downloaded the latest one (8.0.1) and all the sources of Glorp packages are decompiled.

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

Tom Robinson

Re: SQL in() in GLORP?

In reply to this post by jtuchel

Joachim,

You *can* do this... In Cincom Smalltalk, the Store GarbageCollector makes extensive use of this feature. Here is an example:

Query read: StoreBundle where: [:each | each primaryKey in: bundleKeys]

You may need to substitute #readManyOf: for #read.

On 10/5/2015 1:40 AM, jtuchel wrote:

Hi,

Sometimes it would be great to have teh ability to write a SELECT like this:

myCollection := self someObjectsWithSpecialProperties collect: [:ea| ea id].
dbSession readManyOf: MyClass where: [:ea| (ea amount > 300) & (myCollection includes: ea id)].

Which then translates to:

SELECT ... FROM MYTABLE WHERE AMNT > 300 and ID in (100,103,765,999, etc.)

It seems like GLORP doesn't support this.

Am I wrong?

Looking at implementors of #initializeFunctions it seems like there is no IN-Function and to be honest, I can't see how I could add my own.

Joachim

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

jtuchel

Re: SQL in() in GLORP?

Thanks all for your responses.

Seems I need to go back to a Smalltalk course. I always teach my scholars to search for implementers/senders. I should have listened to myself.

I used the subselect thingie a lot because of this anticipated absence of in:, but this time I had a list of object IDs that could only be collected in the Smalltalk image (or at least a respective select statement would have been enormous and far beyond my SQL skills...), so I thought I'd ask.

I was also wondering what would happen if I have thousands of IDs to pass into an SQL statement. And I guess not only ODBC has limits here. I am quite sure there is a limit to the sizre of an SQL String that can be passed to DB2 and Oracle as well. Luckily, in my case, the list would be no longer than a few dozen...

Joachim

Tom Robinson

Re: SQL in() in GLORP?

If you have thousands of IDs to pass to a SQL statement, it may be faster to run the query multiple times with a subset of the IDs. The number of elements in the subset depends on a couple of factors. The ones that come immediately to mind are:

Databases have varying limits on the length of SQL query strings. If you have long character values, you'll probably run into this limitation first.
Some databases may have limits on the number of elements contained in an IN clause. I see a number of web postings suggesting that Oracle may have a limit of about 1000. Here's a discussion on ways of dealing with this:

http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause

In clauses tend to become disproportionately slower when the list of values gets large. The size where this happens depends on the database.

Another way to deal with thousands of values is to insert them into a temporary table and join to the temporary table. This assumes that the user's database id has permission to create temp tables. Here are a couple of articles on this:

On 10/12/2015 12:11 AM, jtuchel wrote:

Thanks all for your responses.

Seems I need to go back to a Smalltalk course. I always teach my scholars to search for implementers/senders. I should have listened to myself.

I used the subselect thingie a lot because of this anticipated absence of in:, but this time I had a list of object IDs that could only be collected in the Smalltalk image (or at least a respective select statement would have been enormous and far beyond my SQL skills...), so I thought I'd ask.

I was also wondering what would happen if I have thousands of IDs to pass into an SQL statement. And I guess not only ODBC has limits here. I am quite sure there is a limit to the sizre of an SQL String that can be passed to DB2 and Oracle as well. Luckily, in my case, the list would be no longer than a few dozen...

Joachim

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

jtuchel

Re: SQL in() in GLORP?

Thanks, Tom!

cool links and good ideas. I won't need them for now, but chances are the next time I do a larger data migration, this issue bubbles up again.

For now I am happy I can use #in: for my purposes.

Joachim

Am Montag, 12. Oktober 2015 19:01:28 UTC+2 schrieb Tom Robinson:

If you have thousands of IDs to pass to a SQL statement, it may be faster to run the query multiple times with a subset of the IDs. The number of elements in the subset depends on a couple of factors. The ones that come immediately to mind are:

Databases have varying limits on the length of SQL query strings. If you have long character values, you'll probably run into this limitation first.

Some databases may have limits on the number of elements contained in an IN clause. I see a number of web postings suggesting that Oracle may have a limit of about 1000. Here's a discussion on ways of dealing with this:

<a href="http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause" target="_blank" rel="nofollow" onmousedown="this.href='http://www.google.com/url?q\75http%3A%2F%2Fstackoverflow.com%2Fquestions%2F400255%2Fhow-to-put-more-than-1000-values-into-an-oracle-in-clause\46sa\75D\46sntz\0751\46usg\75AFQjCNHdFrKIB0Aby1wlqgdPRPzXyXZpvg';return true;" onclick="this.href='http://www.google.com/url?q\75http%3A%2F%2Fstackoverflow.com%2Fquestions%2F400255%2Fhow-to-put-more-than-1000-values-into-an-oracle-in-clause\46sa\75D\46sntz\0751\46usg\75AFQjCNHdFrKIB0Aby1wlqgdPRPzXyXZpvg';return true;">http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause

In clauses tend to become disproportionately slower when the list of values gets large. The size where this happens depends on the database.

Another way to deal with thousands of values is to insert them into a temporary table and join to the temporary table. This assumes that the user's database id has permission to create temp tables. Here are a couple of articles on this:

Oracle - <a href="http://stackoverflow.com/questions/22797896/what-are-the-performance-implications-of-oracle-in-clause-with-no-joins" target="_blank" rel="nofollow" onmousedown="this.href='http://www.google.com/url?q\75http%3A%2F%2Fstackoverflow.com%2Fquestions%2F22797896%2Fwhat-are-the-performance-implications-of-oracle-in-clause-with-no-joins\46sa\75D\46sntz\0751\46usg\75AFQjCNGRrWqQFCFTRaYKJ5rceSI_7qAuhw';return true;" onclick="this.href='http://www.google.com/url?q\75http%3A%2F%2Fstackoverflow.com%2Fquestions%2F22797896%2Fwhat-are-the-performance-implications-of-oracle-in-clause-with-no-joins\46sa\75D\46sntz\0751\46usg\75AFQjCNGRrWqQFCFTRaYKJ5rceSI_7qAuhw';return true;">http://stackoverflow.com/questions/22797896/what-are-the-performance-implications-of-oracle-in-clause-with-no-joins

MySQL - <a href="http://explainextended.com/2009/08/18/passing-parameters-in-mysql-in-list-vs-temporary-table/" target="_blank" rel="nofollow" onmousedown="this.href='http://www.google.com/url?q\75http%3A%2F%2Fexplainextended.com%2F2009%2F08%2F18%2Fpassing-parameters-in-mysql-in-list-vs-temporary-table%2F\46sa\75D\46sntz\0751\46usg\75AFQjCNG1QkZmvj9F-3qpGuUTpKq2knjZMg';return true;" onclick="this.href='http://www.google.com/url?q\75http%3A%2F%2Fexplainextended.com%2F2009%2F08%2F18%2Fpassing-parameters-in-mysql-in-list-vs-temporary-table%2F\46sa\75D\46sntz\0751\46usg\75AFQjCNG1QkZmvj9F-3qpGuUTpKq2knjZMg';return true;">http://explainextended.com/2009/08/18/passing-parameters-in-mysql-in-list-vs-temporary-table/

On 10/12/2015 12:11 AM, jtuchel wrote:

Thanks all for your responses.

Seems I need to go back to a Smalltalk course. I always teach my scholars to search for implementers/senders. I should have listened to myself.

I used the subselect thingie a lot because of this anticipated absence of in:, but this time I had a list of object IDs that could only be collected in the Smalltalk image (or at least a respective select statement would have been enormous and far beyond my SQL skills...), so I thought I'd ask.

I was also wondering what would happen if I have thousands of IDs to pass into an SQL statement. And I guess not only ODBC has limits here. I am quite sure there is a limit to the sizre of an SQL String that can be passed to DB2 and Oracle as well. Luckily, in my case, the list would be no longer than a few dozen...

Joachim

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="sKzLGSdWAwAJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp-group...@googlegroups.com.
To post to this group, send email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="sKzLGSdWAwAJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">glorp...@....
Visit this group at <a href="http://groups.google.com/group/glorp-group" target="_blank" rel="nofollow" onmousedown="this.href='http://groups.google.com/group/glorp-group';return true;" onclick="this.href='http://groups.google.com/group/glorp-group';return true;">http://groups.google.com/group/glorp-group.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href='https://groups.google.com/d/optout';return true;" onclick="this.href='https://groups.google.com/d/optout';return true;">https://groups.google.com/d/optout.