--- [EMAIL PROTECTED] a écrit :
> tom wang wrote:
> > Hi,
> >
> >
> > First, sorry, I kind of messed of with copy and
> > pasting (it's been a long day) and forgot to strip
> all
> > the useless part (for the sake of explaining my
> > problem) between select and from...
> >
> > SELECT * FROM projects LEFT OUTER JOIN forums ON
> > forums.work_id = projects.id AND forums.work_type
> =
> > 'Project' LEFT OUTER JOIN posts ON posts.forum_id
> =
> > forums.id LEFT OUTER JOIN topics ON topics.id =
> > posts.topic_id LEFT OUTER JOIN readerships ON
> > readerships.topic_id = topics.id LEFT OUTER JOIN
> > titles ON titles.project_id = projects.id LEFT
> OUTER
> > JOIN forums forums_titles ON forums_titles.work_id
> =
> > titles.id AND forums_titles.work_type = 'Title'
> LEFT
> > OUTER JOIN posts posts_forums ON
> posts_forums.forum_id
> > = forums_titles.id LEFT OUTER JOIN topics
> topics_posts
> > ON topics_posts.id = posts_forums.topic_id LEFT
> OUTER
> > JOIN readerships readerships_topics ON
> > readerships_topics.topic_id = topics_posts.id LEFT
> > OUTER JOIN forums forums_projects ON
> > forums_projects.work_id = projects.id AND
> > forums_projects.work_type = 'Project' LEFT OUTER
> JOIN
> > titles titles_projects ON
> titles_projects.project_id =
> > projects.id WHERE ((readerships.read != '1' OR
> > readerships_topics.read != '1'))
>
> That's still, um ... a bit difficult to follow.
> First rule of SQL: line
> breaks are allowed. Especially when posting queries
> in an email.
>
Sorry, I should have thought of that....
>
> > I'm using ActiveRecord with ruby on rails, which
> > generated this query with from:
> >
> >
> Project.find(:all,{:conditions=>["(readerships.read
> !=
> > '1' OR readerships_topics.read != '1')"],
> :order=>nil,
> >
>
:include=>{:forum=>{:posts=>{:topic=>:readerships}}},
> >
>
{:titles=>{:forum=>{:posts=>{:topic=>:readerships}}}}}
>
> I've been waiting for a sign to show me that giving
> RoR a pass was the
> correct thing to do. Now, i think i know ;-)
>
I guess someone better than me with mysql and rails
would have done something much cleaner though.... But
rails make simple things simpler and things out of the
ordinary harder.....
> > The problem I have is that I have a projects that
> is
> > linked to a forum table (itself linked with posts
> and
> > topics) and a titles table that is also linked to
> a
> > forum table (etc...)
> >
> > I need to know which titles (don't ask me for the
> > name, that's the what happen when a manager
> designs
> > the database schema based on what the customer
> wants
> > :-( ) and which projects have posts that have
> been
> > read by the user.
>
> The first thing that (i think) i see is that you're
> not selecting
> anything at all from titles:
>
> SELECT * FROM projects
>
> I'm not sure i understand your schema enough to
> help. Are you sure you
> need a separate titles table, for instance?
Well the name is rather misleading... the database
schema was created by my japanese coworker and is not
at all used for titles but used to store different
parts of the project.... (when I asked why it was
named title, the answer was that it's client
requirement ;-) )
it seems that the titles table is pulled out through
this in the LEFT OUTER JOIN
titles titles_projects ON titles_projects.project_id
= projects.id
but I guess it would be more correct to put it inside
the from clause...
> And you
> shouldn't be able to
> use the alias readerships_topics in the WHERE
> clause. Come to think of
> it, your WHERE clause makes no sense at all because
> you're selecting
> from projects. If you want to test readerships.read
> it should go in the
> ON clause of that particular join:
>
> LEFT OUTER JOIN readerships
> ON readerships.topic_id = topics.id
> AND readerships.read != '1'
>
> Though i'm unsure whether or not the test on
> topics.id would work here,
> either.
>
I guess I need to find a way to ask rails to change my
left outer join....
I also have another question more or less related....
How could I check that I either have
readerships.read = '0' AND readerships.user_id = '5'
or
no record with readerships.user_id = '5'
?
Thanks for your help
Thomas
_____________________________________________________________________________
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
