Querying most recent values
Locked
Querying most recent values
 
|
I want to retrieve the most recent values using GLORP. Ej: myTable date entity_id 1/6/13 1 1/10/13 1 1/7/13 2 1/1/14 2 FROM myTable WHERE (entity_id, date) IN (SELECT entity_id, max(date) from myTable WHERE DATE <= '2014-01-01' GROUP BY entity_id) This would bring: date entity_id 1/10/13 1 1/1/14 2 Or in the case I want it for a specific entity: SELECT * FROM myTable WHERE (entity_id, date) IN (SELECT entity_id, max(date) from myTable WHERE DATE <= '2014-01-01' AND entity_id = 1 GROUP BY entity_id) Result: date entity_id 1/10/13 1 Is there a way I can do this using a Glorp Query? Regards, Esteban. 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/groups/opt_out. |
Re: Querying most recent values
|
|
Bump.
No one ever needed to perform this? |
Re: Querying most recent values
|
|
Yes, it does it slightly differently in the SQL, but for examples see examples in Store like getNamesAndMaxTimestampsIn: latestOf:version:in: or in
Glorp tests testReadTransactionsWithMaxSubselect testReadTransactionsWithMaxSubselectAndRetrieveMax testReadCustomerWithFilteredSubSelectToAccountsAndAggregate On 27 February 2014 14:52, Esteban A. Maringolo <[hidden email]> wrote: Bump. 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/groups/opt_out. |
Re: Querying most recent values
|
|
Hi Alan,
I adapted the query of the test to fetch my own class. It is the most recent status change of each repositor (from the table REPOSITORSTATUSCHANGE). Like this: query := Query returningManyOf: GptRepositorStatusChange where: [:each | each date = ( (Query readManyOf: GptRepositorStatusChange where: [:change | change entity = each entity]) retrieveMax: [:change | change date]) ]. result := glorpSession execute: query. I get the following DB error: GlorpDatabaseReadError: ERROR: missing FROM-clause entry for table "s1t2" at character 194 This is the generated SQL: SELECT t1.id, t1.entity_id, t1.date, t1.comments, t1.status_id FROM REPOSITORSTATUSCHANGE t1 WHERE (t1.date = ( SELECT MAX(s1t1.date) FROM REPOSITORSTATUSCHANGE s1t1 WHERE (s1t1.entity_id = s1t2.id))) However I don't know if this will fetch exactly what I want. I want the most recent record (max date) of REPOSITORSTATUSCHANGE for each entity_id. Regards, Esteban A. Maringolo 2014-02-28 13:45 GMT-03:00 Alan Knight <[hidden email]>: > Yes, it does it slightly differently in the SQL, but for examples see > examples in Store like > > getNamesAndMaxTimestampsIn: > latestOf:version:in: > > or in > Glorp tests > testReadTransactionsWithMaxSubselect > testReadTransactionsWithMaxSubselectAndRetrieveMax > testReadCustomerWithFilteredSubSelectToAccountsAndAggregate > > > > On 27 February 2014 14:52, Esteban A. Maringolo <[hidden email]> > wrote: >> >> Bump. >> >> No one ever needed to perform this? >> >> >> >> -- >> View this message in context: >> http://forum.world.st/Querying-most-recent-values-tp4733708p4746871.html >> Sent from the GLORP mailing list archive at Nabble.com. >> >> -- >> 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/groups/opt_out. > > > -- > You received this message because you are subscribed to a topic in the > Google Groups "glorp-group" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/glorp-group/OC0ly25IZPM/unsubscribe. > To unsubscribe from this group and all its topics, 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/groups/opt_out. -- 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/groups/opt_out. |
|
|
Hi Esteban,
I was able to execute the following query, equivalent to what you're trying to do on the current dev build of VisualWorks connected to a Store repository running in PostgreSQL. | session query | session := StoreLoginFactory currentStoreSession. query read: StorePackage where: [:each | | subQuery | subQuery := Query read: StorePackage where: [:eachPkg| eachPkg name = each name]. subQuery retrieveMax: [:eachPkg | eachPkg timestamp ]. each timestamp = subQuery]. session execute: query. This appears to be what you're trying to do. On 2/28/2014 11:37 AM, Esteban A. Maringolo wrote: > Hi Alan, > > I adapted the query of the test to fetch my own class. It is the most > recent status change of each repositor (from the table > REPOSITORSTATUSCHANGE). > > Like this: > query := Query > returningManyOf: GptRepositorStatusChange > where: [:each | > each date = ( > (Query readManyOf: GptRepositorStatusChange > where: [:change | change entity = each entity]) > retrieveMax: [:change | change date]) > ]. > result := glorpSession execute: query. > > I get the following DB error: > GlorpDatabaseReadError: ERROR: missing FROM-clause entry for table > "s1t2" at character 194 > > This is the generated SQL: > SELECT t1.id, t1.entity_id, t1.date, t1.comments, t1.status_id > FROM REPOSITORSTATUSCHANGE t1 > WHERE (t1.date = ( > SELECT MAX(s1t1.date) > FROM REPOSITORSTATUSCHANGE s1t1 > WHERE (s1t1.entity_id = s1t2.id))) > > > However I don't know if this will fetch exactly what I want. > I want the most recent record (max date) of REPOSITORSTATUSCHANGE for > each entity_id. > > Regards, > > > > > > > > Esteban A. Maringolo > > > 2014-02-28 13:45 GMT-03:00 Alan Knight <[hidden email]>: >> Yes, it does it slightly differently in the SQL, but for examples see >> examples in Store like >> >> getNamesAndMaxTimestampsIn: >> latestOf:version:in: >> >> or in >> Glorp tests >> testReadTransactionsWithMaxSubselect >> testReadTransactionsWithMaxSubselectAndRetrieveMax >> testReadCustomerWithFilteredSubSelectToAccountsAndAggregate >> >> >> >> On 27 February 2014 14:52, Esteban A. Maringolo <[hidden email]> >> wrote: >>> Bump. >>> >>> No one ever needed to perform this? >>> >>> >>> >>> -- >>> View this message in context: >>> http://forum.world.st/Querying-most-recent-values-tp4733708p4746871.html >>> Sent from the GLORP mailing list archive at Nabble.com. >>> >>> -- >>> 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/groups/opt_out. >> >> -- >> You received this message because you are subscribed to a topic in the >> Google Groups "glorp-group" group. >> To unsubscribe from this topic, visit >> https://groups.google.com/d/topic/glorp-group/OC0ly25IZPM/unsubscribe. >> To unsubscribe from this group and all its topics, 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/groups/opt_out. -- 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/groups/opt_out. |
Re: Querying most recent values
|
|
Hi Tom,
It might have to do with the Pharo version of Glorp. I get the exact same error as with Alan's suggestion. This is the code I tried: query := Query returningManyOf: GptRepositorStatusChange where: [:each | | subQuery | subQuery := Query read: GptRepositorStatusChange where: [:change | change entity = each entity]. subQuery retrieveMax: [:sq | sq date ]. each date = subQuery]. result := glorpSession execute: query. Bad luck :-/ I there any way I can know which version of Glorp I'm running in Pharo? Thank you! Esteban A. Maringolo 2014-03-03 18:21 GMT-03:00 Tom Robinson <[hidden email]>: > Hi Esteban, > > I was able to execute the following query, equivalent to what you're trying > to do on the current dev build of VisualWorks connected to a Store > repository running in PostgreSQL. > > | session query | > session := StoreLoginFactory currentStoreSession. > query read: StorePackage > where: [:each | | subQuery | > subQuery := Query read: StorePackage > where: [:eachPkg| eachPkg name = each name]. > subQuery retrieveMax: [:eachPkg | eachPkg timestamp ]. > each timestamp = subQuery]. > session execute: query. > > This appears to be what you're trying to do. > > > > On 2/28/2014 11:37 AM, Esteban A. Maringolo wrote: >> >> Hi Alan, >> >> I adapted the query of the test to fetch my own class. It is the most >> recent status change of each repositor (from the table >> REPOSITORSTATUSCHANGE). >> >> Like this: >> query := Query >> returningManyOf: GptRepositorStatusChange >> where: [:each | >> each date = ( >> (Query readManyOf: GptRepositorStatusChange >> where: [:change | change entity = each entity]) >> retrieveMax: [:change | change date]) >> ]. >> result := glorpSession execute: query. >> >> I get the following DB error: >> GlorpDatabaseReadError: ERROR: missing FROM-clause entry for table >> "s1t2" at character 194 >> >> This is the generated SQL: >> SELECT t1.id, t1.entity_id, t1.date, t1.comments, t1.status_id >> FROM REPOSITORSTATUSCHANGE t1 >> WHERE (t1.date = ( >> SELECT MAX(s1t1.date) >> FROM REPOSITORSTATUSCHANGE s1t1 >> WHERE (s1t1.entity_id = s1t2.id))) >> >> >> However I don't know if this will fetch exactly what I want. >> I want the most recent record (max date) of REPOSITORSTATUSCHANGE for >> each entity_id. >> >> Regards, >> >> >> >> >> >> >> >> Esteban A. Maringolo >> >> >> 2014-02-28 13:45 GMT-03:00 Alan Knight <[hidden email]>: >>> >>> Yes, it does it slightly differently in the SQL, but for examples see >>> examples in Store like >>> >>> getNamesAndMaxTimestampsIn: >>> latestOf:version:in: >>> >>> or in >>> Glorp tests >>> testReadTransactionsWithMaxSubselect >>> testReadTransactionsWithMaxSubselectAndRetrieveMax >>> testReadCustomerWithFilteredSubSelectToAccountsAndAggregate >>> >>> >>> >>> On 27 February 2014 14:52, Esteban A. Maringolo <[hidden email]> >>> wrote: >>>> >>>> Bump. >>>> >>>> No one ever needed to perform this? >>>> >>>> >>>> >>>> -- >>>> View this message in context: >>>> http://forum.world.st/Querying-most-recent-values-tp4733708p4746871.html >>>> Sent from the GLORP mailing list archive at Nabble.com. >>>> >>>> -- >>>> 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/groups/opt_out. >>> >>> >>> -- >>> You received this message because you are subscribed to a topic in the >>> Google Groups "glorp-group" group. >>> To unsubscribe from this topic, visit >>> https://groups.google.com/d/topic/glorp-group/OC0ly25IZPM/unsubscribe. >>> To unsubscribe from this group and all its topics, 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/groups/opt_out. > > > -- > You received this message because you are subscribed to a topic in the > Google Groups "glorp-group" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/glorp-group/OC0ly25IZPM/unsubscribe. > To unsubscribe from this group and all its topics, 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/groups/opt_out. -- 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/groups/opt_out. |
Re: Querying most recent values
|
|
I think that whatever version it is it's an old one, because that looks like a bug that was fixed some while ago. It's not putting in the subselect table in the from clause. I would hope that the Pharo port would list somewhere which Glorp version it was built from. There's code to generate a Squeak/Pharo port from the VisualWorks version, and the code tries to be entirely portable in the core, but I never got fixes back from the last porting effort to incorporate, so there are probably some divergences.
On 5 March 2014 10:31, Esteban A. Maringolo <[hidden email]> wrote: Hi Tom, 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/groups/opt_out. |
«
Return to GLORP
|
1 view|%1 views
| Free forum by Nabble | Edit this page |