Re: Really complicated query (or not?)
Re: Really complicated query (or not?)
- Subject: Re: Really complicated query (or not?)
- From: Miguel Arroz <email@hidden>
- Date: Sun, 8 Jul 2007 19:38:03 +0100
Hi!
I was getting really nervous about this (specially because I'm
tired, because I went to bed ad 3am and had dreams about qualifiers)
so I drove to the beautiful Tejo river in Lisbon and walked some time
to clarify all this. The result is that I'm thinking about writing a
qualifier to solve this! :)
My idea was to write something like (ERX?)GroupByQuantityQualifier
that would have two constructors:
GroupByQuantityQualifier( NSArray qualifiers ); -> This would do
the ORs and GROUP BY COUNT (qualifiers.count()).
GroupByQuantityQualifier( NSArray qualifiers, int count ); ->
GROUP BY COUNT (count) if, for some reason, the count must be
different from the number of qualifiers to group.
So, the next question is... what should I read to help me building
my first qualifier? :)
I said ERX back there because, if I can do this, and if you think
it's good enough, it would obviously be a pleasure to donate the code
to the Wonder framework.
Yours
Miguel Arroz
On 2007/07/08, at 17:36, Alexander Spohr wrote:
I think the question was more like: is there anything in the
FrontBase adaptor that does the same as I did in the PostgreSQL
adaptor.
I used to do that outside the adaptor. I did count(*) and max() and
GROUP BY in some method that talked to the EODatabaseChannel (I
think). But that was no real EOF-integration as you needed to give
the attributes names in database-speak. You could not use EOFs
EOAttribute names. The adaptor add-on gives you that feature.
atze
Am 08.07.2007 um 16:29 schrieb Mike Schrag:
yes .. GROUP BY and HAVING are sql92.
ms
On Jul 8, 2007, at 10:19 AM, Miguel Arroz wrote:
Hi!
That appears to be a good solution, but I have another little
problem... this thing is running on PostgreSQL, but the final
version will probably run on FrontBase... does FrontBase support
that GROUP BY stuff?
You said on some other mail about this "ps. i wonder why nobody
else seems to need this? i use it at least for all statistics i
run in my apps.". That's exactly what I'm doing - obtaining
statistical results by filtering the data with one or more
filters (on my example, the "1" query was the "main" query and
the "2" query was the filter). I feel the same as you, this is
too important to be so hard to do!
Yours
Miguel Arroz
On 2007/07/08, at 14:31, Alexander Spohr wrote:
Ah... i love those :)
You can not AND them, as one AP can not have property=x AND
property=y.
Therefore you need to OR both qualifiers.
That brings the problem that you would get all Rs having either
x or y or (x and y), which you don’t want.
Now comes the tricky part: you need to add a GROUP BY HAVING
COUNT = 2 to your qualifier. And maybe you should add a view
that flattens some of the relationships just to get a better
angle at qualifying. As I have no deeper insight to your model I
am not sure what attribute you should GROUP.
If you use PostgreSQL just include the extensions i mailed to
the list a while ago. They are pretty EOF-aware and don’t break
any of your code. I did not find the time to push them back to
WOnder.
If you use another database you need to either inject the GROUP
BY somewhere, or you could fetch them all and filter in memory -
whereas the latter will be much slower and memory consuming.
atze
Am 08.07.2007 um 03:53 schrieb Miguel Arroz:
Hi!
I need to build a complex fetch spec to solve a weird
problem. Here's the part of the model that matters:
+-----+ +-----+ +------+ +------+
| R | <--->> | A | <--->> | AP | <<---> | QO |
+-----+ +-----+ +------+ +------+
^
^
|
|
V
+------+
| QP |
+------+
My goal it to count the Rs in a given situation (it's the
same as fetching them, but I'm using Wonder to count instead of
fetch). And the crieria I want is:
1) All Rs where I have an APs with aValue property = "x" and
QO = "qo" and QP = "qp".
2) All Rs where I have an APs with aValue property = "y" and
QO = "qo2" and QP = "qp2".
(each R will have only one AP for condition 1 and one AP for
condition 2)
I want to AND those two conditions. In practice, I have two
sets of APs, and I want the Rs' that are in both sets (the
intersection of Rs).
Also, I want to do this all in the same query. I'm talking
about huge numbers of objects, so I don't want to get all the
PKs of the APs, and then work with them in memory to obtain Rs.
Any ideia how to do this? I think the solution may be
ERXQualifierInSubquery, but I couldn't make it work (specially
because the "3 argument constructor" is actually a 4 argument
constructor...).
My problem is that making a qualifier for R with the format
"a.ap.qo = %@ and a.ap.qp = %@ and a.ap.aValue = %@" wouldn't
work, because I would not be selecting the same AP, right? I'm
saying that I want Rs with an AP with a certain QO, and an AP
with a certain QP but i'm not saying they MUST be the same.
Please please give me some hints, I really need to make this
work. If you want more details just ask, it's almost 3am here
and my mind is really confused with this.
Yours
Miguel Arroz
Miguel Arroz
http://www.terminalapp.net
http://www.ipragma.com
Freeport & Soliversum
Alexander Spohr
email@hidden
www.freeport.de
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
40guiamac.com
This email sent to email@hidden
Miguel Arroz
http://www.terminalapp.net
http://www.ipragma.com
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden