Implement or avoid LEFT JOIN in EOF?
Implement or avoid LEFT JOIN in EOF?
- Subject: Implement or avoid LEFT JOIN in EOF?
- From: Eugene Khablov <email@hidden>
- Date: Tue, 20 Sep 2005 18:08:29 +0400
Hi!
Trying to fetch data we are facing the problems that generate a need to use
LEFT JOIN of tables.
PROBLEM CONTEXT:
Person (entity)
isTestUser (attribute)
...
LoggedAction (entity)
personId (allows null attribute)
person (optional to-one Person relationship using personId)
...
THE GOAL is to get loggedActions without users along with loggedActions of
notTest users.
DETAILS:
The simplified qualifier for FetchSpecification:
((personId = nil) or (person.isTestUser = 'false'))
We are using MySQL and WONDER with some improvements.
Generated SQL:
SELECT t0.song_id, COUNT(t0._rowid)
FROM logged_action t0, person T1
WHERE (t0.person_id is NULL OR T1.is_test_user = 'false') AND
t0.person_id = T1._rowid
GROUP BY t0.song_id
THE PROBLEM: results contains only objects with non null personId.
So there are 2 big questions:
- Are there any ways to avoid this problem? May be any design patterns, or
aproaches suitable for WO/EOF apps?
- Is this problem worth implementing a general solution on EOAccess level
(LEFT JOIN support) or it's better to provide special solution for every
particular case?
It seems there are several possible workarounds:
1. Filter objects in memory. The reasons not to use this aproach are:
- it could be a lot of objects to hold all of them in memory;
- not fast comparison on large data sets;
- we already have GROUP BY for DB implemented in our EOAccess logic.
2. Another suggestion from list by Jeff LaMarche:
"First, you could create a denormalized search table with all the data
you need.
Second, you could create a view that loads the data you want but
doesn't use outer joins. Since the only fields you are actually looking
at are in PROJECT, you can probably do "IF EXISTS" subselects on the
other tables, or create a view with PROJECT and only the fields you
need to look at from the other tables so that your SQL just has to look
at different conditions.."
3. Create pure SQL. It not a WO style...
4. Using stored procedures. Not great too. Especially with MySQL :(
5. Update FS qualifier to get something like this:
((personId = nil) or (personId = xx) or (personId = yy) ... )
Where personId are ids for objects from another table. In our case person
objects with (isTestUser = 'false')
This will work for relatively small number of records from another table. Is
it a good solution?
6. General solution from topic "EOF Generates Incorrect SQL for Outer Joins
on MySql" from Lee Harvey Osmond was:
"My preferred fix would be to subclass
com.webobjects.jdbcadaptor.MySQLPlugIn.MySQLExpression and override
com.webobjects.eoaccess.EOSQLExpression.assembleJoinClause(String,String
,int) so that if the int was the right constant, after calling super()
we'd replace " *= " with the expression in your previous post you said
was needed."
I think in my case SQL should look like this:
SELECT t0.song_id, COUNT(t0._rowid)
FROM logged_action t0 LEFT JOIN person T1 ON t0.person_id = T1._rowid
WHERE t0.person_id IS NULL OR T1.is_test_user = 'false'
GROUP BY t0.song_id
Using this aproach we have override not only "assembleJoinClause" from
com.webobjects.eoaccess.EOSQLExpression. Looks like we have to rewrite
"tableListWithRootEntity" to provide the correct JOIN syntax and move a part
of generated by assembleJoinClause condition to tableListWithRootEntity (if
we will have several tables to join). May be someone can share the code if
exists or point to hidden dangers of such implementation?
Any help on this topic would be greatly appreciated!
Thanks and regards,
Eugene.
---
Eugene Khablov
Media-agency DesignMaximum
Tel: +7 863 264-82-11
Fax: +7 863 264-52-29
Web: http://www.demax.ru/en/
_______________________________________________
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