Applyin IN() operator to a subselect

[Esteban Maringolo]

Hi,

I need to perform a "basic" query, where I need to bring the "latest"
(at a specified date) record for each of the rows in a table.

This query provides me with that.

select *
from gwplayerhandicap t
where (player_id, date) in
(
select t2.player_id, max(t2.date)
from gwplayerhandicap t2
where t2.date <= '2022-01-31'
group by t2.player_id
)


My "expected" subquery is straightforward to do:
d := '2022-02-15' asDate.
sq := Query read: GwPlayerHandicap where: [ :each | each date <= d ].
sq retrieveMax: [ :each | each date ].
sq retrieve: [ :each | each player id].
sq groupBy: [ :each | each player id ].
sq executeIn: db.

But if I want to do a Query like this it fails.

q := Query read: GwPlayerHandicap where: [:each |
(OrderedCollection with: each date with: each player) in: (
(Query read: GwPlayerHandicap where: [:eachHcp | eachHcp date <= d])
retrieveMax: [:eachHcp | eachHcp date];
retrieve: [ :eachHcp | eachHcp player id ];
groupBy: [ :eachHcp | eachHcp player ]
)
].

Obviously, there is no way to get an RelationalOperator (A IN(B)) on
an OrderedCollection. Is there other way to get the same thing? Maybe
with an EXISTS() kind of rewrite, but my SQL skills are rusty these
days.

Best regards,


Esteban A. Maringolo

[jtuchel]

Esteban,

just a thought: is it even possible to use "normal" Smalltalk in a Query Block? What if you used one Query to create the OrderedCollection and a second one to check for inclusion of objects in it?

I had a somewhat related problem where I had to split a query in two and user include. In my case the problem was that I could not use a subquery because of a cross-prouct and I wanted to use limit: on the reslt in order to only get the first x records.

Are you getting a wrong query or are you seeing an Error thrown during creation of the query? I would expect the former...?

Joachim

[Esteban Maringolo]

Coming three months later, I took the last hours of my Friday to work
on this again, because I was doing too many single queries that I
wanted to replace by a single query for all the elements in
aCollection.

This query made it and I'm leaving it here for posterity.
The answer was in the #testReadTransactionWithMaxSubselect test case.

query := SimpleQuery read: GwPlayerHandicap where: [ :each |
(each player in: aCollection) AND: [
each date = ((Query
read: GwPlayerHandicap
where: [ :subHcp |
subHcp player = each player AND: [
subHcp date <= aDate ] ]) retrieve: [ :subHcp |
subHcp date max ]) ] ].


Best regards!

Esteban A. Maringolo

> --
> 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 glorp-group...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/glorp-group/c51b1229-6005-4b97-b217-2b610dd2cfabn%40googlegroups.com.