Re: [Not So] Simple Query Issues (Improved Grammer)
Re: [Not So] Simple Query Issues (Improved Grammer)
- Subject: Re: [Not So] Simple Query Issues (Improved Grammer)
- From: Jonathan Rochkind <email@hidden>
- Date: Mon, 31 Mar 2003 14:47:03 -0600
I'm confused about that query. It doesn't look to me like a query that
could be at all useful---it's basically an unjoined multi-table query, and
will return an awful lot of rows (on the order of magnitude of [# rows in
table 1] * [# rows in table 2]), and the result rows won't have any useful
meaning that I can see. So I suspect that query is not what the developer
really wants.
Aha, the developer tells us he wants to get all the rows in table A that
have an item1 that does not exist in table B. Okay, that query is not
going to do that. So what is a query that will actually tell you
that? Hm. What I'm coming up with is querries involving either a
sub-query or an outer join. And those are both kind of tricky to make an
EOModel-based query for.
I'll supply a couple of possible querries, and maybe starting from them
someone can come up with a way to create an EOModel-based qualifier for
it. [Of course, you can always just write raw SQL in your WO app when
neccesary too. Using rawRowsForSQL is the easiest way, but there are
others. Writing raw SQL has certain obvious design disadvantages].
SELECT A.item1, A.item2, A.item3
FROM A topLevelTable
WHERE (SELECT count(*) from B WHERE B.item1 != topLevelTable.item1) = 0
or instead, with an outer join instead of a subquery:
SELECT A.item1, A.item2, A.item3
FROM A LEFT OUTER JOIN B ON A.item1 = B.item1
WHERE B.item1 = null
[Or, there might be some way to do it with the SQL 'set' functions, like
UNION, INTERSECT, etc. But that would be least likely to be possible to
create in a model-based qualifier, so I won't bother trying to come up with
a way to do it that way.]
But like I said, creating a qualifier for a subquery or an outer join is
somewhat tricky. There are a couple possible options:
1) Create the subquery as a "derived" column in the model for A. Make sure
you make this attribute NOT class visible, otherwise any fetches on A are
going to be rather slow, as the subquery count will be calculated for each
row. But you can still include a non-class-attribute in a qualifier, I
think? If so, your qualifier says you want this non class property to equal 0.
2) I think there's a qualifier in Project Wonder that lets you construct
subqueries? That could potentially be used to construct the neccesary
subquery, without doing anything weird in the model.
3) Model the A.item1 to B.item1 relationship as a left outer join. You
might already have that relationship modeled as a normal join. Model it
again as a left outer join, with a different name. Again, I'd recommend NOT
making it class visible. But now you want to qualify accross that left
outer join-modeled relationship in your qualifier, not the other more
conventional relationship.
Haven't actually done any of these things myself, so not positive they
would work.
Hope this helps,
--Jonathan
At 09:24 AM 3/31/2003 -1000, Art Isbell wrote:
On Sunday, March 30, 2003, at 05:22 PM, I wrote:
On Sunday, March 30, 2003, at 08:54 AM, Brian Allen wrote:
I have not been able to determine how to do some basic queries within
WebObjects that can be easily done using SQL.
For example, two tables A and B with a number of columns.
SELECT A.item1, A.item2, A.item3 FROM A,B WHERE A.item1 <> B.item1
The query should return all of the records within table A that did not have
any matches between the A.item1s and the B.item1s.
The above query doesn't specify any relationship between
entities A and B.
Upon further consideration, I suppose the above query does
specify a relationship between entities A and B. However, the
relationship is based upon the inequality of attributes whereas EOF's
relationships are based on the equality of attributes. I don't know of a
way to define a relationship in EOF based on the inequality of
attributes. Suggestions?
Aloha,
Art
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives: http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.