RE : Re: RE : Re: Left outer joins, where clause and table_names
--- [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]
Re: RE : Re: Left outer joins, where clause and table_names
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.
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 ;-)
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? 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.
brian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE : Re: Left outer joins, where clause and table_names
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'))
I guess I should have given more background
> > I can't just the left outer join part as it's
> > autogenerated but I can change the WHERE clause
>
> Autogenerated by what, exactly? Does it run at all
> the way it is? That
> is quite a monster SELECT statement. In fact, if it
> doesn't run now,
> perhaps all it requires is a good jolt of
> electricity.
>
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'm not good a sql syntax, but I would love to
> > learn
>
> Are you reasonably certain that your SELECT requires
> all that? Could
> your application maybe be adjusted so that you could
> break that up a little?
>
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.
Thanks
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]
