Re: Implement or avoid LEFT JOIN in EOF?
Re: Implement or avoid LEFT JOIN in EOF?
- Subject: Re: Implement or avoid LEFT JOIN in EOF?
- From: email@hidden
- Date: Wed, 21 Sep 2005 13:35:53 +0200
Hi!
I don't really see what prevents you
from using a left join. Does the MySQL plug-in lack support for this?
As a workaround you could use the InSubqueryQualifier
off my my web site: http://homepage.mac.com/i_love_my/code.html
This will allow you to generate SQL
along the lines of:
... WHERE t0.person_id is NULL OR t0.person_id
IN (SELECT a0.person_id from persons WHERE a0.is_test_user = 'false)
Pierre
http://homepage.mac.com/i_love_my
webobjects-dev-bounces+pierre.bernard=email@hidden
wrote on 09/20/2005 04:08:29 PM:
> 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
>
**********************************************************************
This email and any files transmitted with it are intended solely for
the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the sender
of this message. (email@hidden)
This email message has been checked for the presence of computer
viruses; however this protection does not ensure this message is
virus free.
Banque centrale du Luxembourg; Tel ++352-4774-1; http://www.bcl.lu
**********************************************************************
_______________________________________________
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