Hi,

I have the following sql request:

SELECT projects.`id` AS t0_r0, projects.`name` AS
t0_r1, projects.`abbreviated_name` AS t0_r2,
projects.`producer` AS t0_r3, projects.`tel_1` AS
t0_r4, projects.`tel_2` AS t0_r5, projects.`recital`
AS t0_r6, projects.`completed_flag` AS t0_r7,
projects.`completed_at` AS t0_r8,
projects.`created_at` AS t0_r9, projects.`update_at`
AS t0_r10, materials.`id` AS t1_r0,
materials.`created_at` AS t1_r1, materials.`work_id`
AS t1_r2, materials.`work_type` AS t1_r3,
materials.`comment` AS t1_r4, materials.`user_name` AS
t1_r5, materials.`user_id` AS t1_r6,
materials.`material_file_id` AS t1_r7,
materials.`tag_id` AS t1_r8, tags.`id` AS t2_r0,
tags.`name` AS t2_r1, tags.`project_id` AS t2_r2,
uploaded_files.`id` AS t3_r0, uploaded_files.`size` AS
t3_r1, uploaded_files.`content_type` AS t3_r2,
uploaded_files.`filename` AS t3_r3,
uploaded_files.`height` AS t3_r4,
uploaded_files.`width` AS t3_r5,
uploaded_files.`parent_id` AS t3_r6,
uploaded_files.`thumbnail` AS t3_r7, forums.`id` AS
t4_r0, forums.`name` AS t4_r1, forums.`description` AS
t4_r2, forums.`topics_count` AS t4_r3,
forums.`posts_count` AS t4_r4, forums.`position` AS
t4_r5, forums.`description_html` AS t4_r6,
forums.`work_id` AS t4_r7, forums.`work_type` AS
t4_r8, posts.`id` AS t5_r0, posts.`user_id` AS t5_r1,
posts.`topic_id` AS t5_r2, posts.`body` AS t5_r3,
posts.`created_at` AS t5_r4, posts.`updated_at` AS
t5_r5, posts.`forum_id` AS t5_r6, posts.`body_html` AS
t5_r7, posts.`material_file_id` AS t5_r8, topics.`id`
AS t6_r0, topics.`forum_id` AS t6_r1, topics.`user_id`
AS t6_r2, topics.`subject` AS t6_r3,
topics.`created_at` AS t6_r4, topics.`updated_at` AS
t6_r5, topics.`hits` AS t6_r6, topics.`sticky` AS
t6_r7, topics.`posts_count` AS t6_r8,
topics.`replied_at` AS t6_r9, topics.`replied_by` AS
t6_r10, topics.`last_post_id` AS t6_r11,
topics.`tag_id` AS t6_r12, tags_topics.`id` AS t7_r0,
tags_topics.`name` AS t7_r1, tags_topics.`project_id`
AS t7_r2, readerships.`id` AS t8_r0,
readerships.`user_id` AS t8_r1, readerships.`topic_id`
AS t8_r2, readerships.`read` AS t8_r3, roles.`id` AS
t9_r0, roles.`name` AS t9_r1,
roles.`authorizable_type` AS t9_r2,
roles.`authorizable_id` AS t9_r3, roles.`created_at`
AS t9_r4, roles.`updated_at` AS t9_r5, users.`id` AS
t10_r0, users.`login` AS t10_r1,
users.`crypted_password` AS t10_r2, users.`salt` AS
t10_r3, users.`family_name` AS t10_r4,
users.`first_name` AS t10_r5, users.`affiliation` AS
t10_r6, users.`tel` AS t10_r7, users.`email` AS
t10_r8, users.`note` AS t10_r9, users.`active` AS
t10_r10, users.`days_display_unit` AS t10_r11,
users.`user_icon_id` AS t10_r12,
users.`remember_token` AS t10_r13,
users.`remember_token_expires_at` AS t10_r14,
users.`position` AS t10_r15, users.`posts_count` AS
t10_r16, users.`last_seen_at` AS t10_r17,
users.`created_at` AS t10_r18, users.`updated_at` AS
t10_r19, titles.`id` AS t11_r0, titles.`project_id` AS
t11_r1, titles.`name` AS t11_r2, titles.`oa_date` AS
t11_r3, titles.`oa_hour` AS t11_r4, titles.`oa_minute`
AS t11_r5, titles.`slip_number` AS t11_r6,
titles.`note` AS t11_r7, titles.`director` AS t11_r8,
titles.`director_tel_1` AS t11_r9,
titles.`director_tel_2` AS t11_r10, titles.`in_charge`
AS t11_r11, titles.`in_charge_tel_1` AS t11_r12,
titles.`in_charge_tel_2` AS t11_r13, titles.`recital`
AS t11_r14, titles.`completed_flag` AS t11_r15,
titles.`completed_at` AS t11_r16, titles.`position` AS
t11_r17, titles.`created_at` AS t11_r18,
titles.`updated_at` AS t11_r19, materials_titles.`id`
AS t12_r0, materials_titles.`created_at` AS t12_r1,
materials_titles.`work_id` AS t12_r2,
materials_titles.`work_type` AS t12_r3,
materials_titles.`comment` AS t12_r4,
materials_titles.`user_name` AS t12_r5,
materials_titles.`user_id` AS t12_r6,
materials_titles.`material_file_id` AS t12_r7,
materials_titles.`tag_id` AS t12_r8,
tags_materials.`id` AS t13_r0, tags_materials.`name`
AS t13_r1, tags_materials.`project_id` AS t13_r2,
material_files_materials.`id` AS t14_r0,
material_files_materials.`size` AS t14_r1,
material_files_materials.`content_type` AS t14_r2,
material_files_materials.`filename` AS t14_r3,
material_files_materials.`height` AS t14_r4,
material_files_materials.`width` AS t14_r5,
material_files_materials.`parent_id` AS t14_r6,
material_files_materials.`thumbnail` AS t14_r7,
forums_titles.`id` AS t15_r0, forums_titles.`name` AS
t15_r1, forums_titles.`description` AS t15_r2,
forums_titles.`topics_count` AS t15_r3,
forums_titles.`posts_count` AS t15_r4,
forums_titles.`position` AS t15_r5,
forums_titles.`description_html` AS t15_r6,
forums_titles.`work_id` AS t15_r7,
forums_titles.`work_type` AS t15_r8, posts_forums.`id`
AS t16_r0, posts_forums.`user_id` AS t16_r1,
posts_forums.`topic_id` AS t16_r2, posts_forums.`body`
AS t16_r3, posts_forums.`created_at` AS t16_r4,
posts_forums.`updated_at` AS t16_r5,
posts_forums.`forum_id` AS t16_r6,
posts_forums.`body_html` AS t16_r7,
posts_forums.`material_file_id` AS t16_r8,
topics_posts.`id` AS t17_r0, topics_posts.`forum_id`
AS t17_r1, topics_posts.`user_id` AS t17_r2,
topics_posts.`subject` AS t17_r3,
topics_posts.`created_at` AS t17_r4,
topics_posts.`updated_at` AS t17_r5,
topics_posts.`hits` AS t17_r6, topics_posts.`sticky`
AS t17_r7, topics_posts.`posts_count` AS t17_r8,
topics_posts.`replied_at` AS t17_r9,
topics_posts.`replied_by` AS t17_r10,
topics_posts.`last_post_id` AS t17_r11,
topics_posts.`tag_id` AS t17_r12, tags_topics_2.`id`
AS t18_r0, tags_topics_2.`name` AS t18_r1,
tags_topics_2.`project_id` AS t18_r2,
readerships_topics.`id` AS t19_r0,
readerships_topics.`user_id` AS t19_r1,
readerships_topics.`topic_id` AS t19_r2,
readerships_topics.`read` AS t19_r3,
accepted_roles_titles.`id` AS t20_r0,
accepted_roles_titles.`name` AS t20_r1,
accepted_roles_titles.`authorizable_type` AS t20_r2,
accepted_roles_titles.`authorizable_id` AS t20_r3,
accepted_roles_titles.`created_at` AS t20_r4,
accepted_roles_titles.`updated_at` AS t20_r5,
users_roles.`id` AS t21_r0, users_roles.`login` AS
t21_r1, users_roles.`crypted_password` AS t21_r2,
users_roles.`salt` AS t21_r3,
users_roles.`family_name` AS t21_r4,
users_roles.`first_name` AS t21_r5,
users_roles.`affiliation` AS t21_r6, users_roles.`tel`
AS t21_r7, users_roles.`email` AS t21_r8,
users_roles.`note` AS t21_r9, users_roles.`active` AS
t21_r10, users_roles.`days_display_unit` AS t21_r11,
users_roles.`user_icon_id` AS t21_r12,
users_roles.`remember_token` AS t21_r13,
users_roles.`remember_token_expires_at` AS t21_r14,
users_roles.`position` AS t21_r15,
users_roles.`posts_count` AS t21_r16,
users_roles.`last_seen_at` AS t21_r17,
users_roles.`created_at` AS t21_r18,
users_roles.`updated_at` AS t21_r19,
materials_projects.`id` AS t22_r0,
materials_projects.`created_at` AS t22_r1,
materials_projects.`work_id` AS t22_r2,
materials_projects.`work_type` AS t22_r3,
materials_projects.`comment` AS t22_r4,
materials_projects.`user_name` AS t22_r5,
materials_projects.`user_id` AS t22_r6,
materials_projects.`material_file_id` AS t22_r7,
materials_projects.`tag_id` AS t22_r8,
forums_projects.`id` AS t23_r0, forums_projects.`name`
AS t23_r1, forums_projects.`description` AS t23_r2,
forums_projects.`topics_count` AS t23_r3,
forums_projects.`posts_count` AS t23_r4,
forums_projects.`position` AS t23_r5,
forums_projects.`description_html` AS t23_r6,
forums_projects.`work_id` AS t23_r7,
forums_projects.`work_type` AS t23_r8,
accepted_roles_projects.`id` AS t24_r0,
accepted_roles_projects.`name` AS t24_r1,
accepted_roles_projects.`authorizable_type` AS t24_r2,
accepted_roles_projects.`authorizable_id` AS t24_r3,
accepted_roles_projects.`created_at` AS t24_r4,
accepted_roles_projects.`updated_at` AS t24_r5,
titles_projects.`id` AS t25_r0,
titles_projects.`project_id` AS t25_r1,
titles_projects.`name` AS t25_r2,
titles_projects.`oa_date` AS t25_r3,
titles_projects.`oa_hour` AS t25_r4,
titles_projects.`oa_minute` AS t25_r5,
titles_projects.`slip_number` AS t25_r6,
titles_projects.`note` AS t25_r7,
titles_projects.`director` AS t25_r8,
titles_projects.`director_tel_1` AS t25_r9,
titles_projects.`director_tel_2` AS t25_r10,
titles_projects.`in_charge` AS t25_r11,
titles_projects.`in_charge_tel_1` AS t25_r12,
titles_projects.`in_charge_tel_2` AS t25_r13,
titles_projects.`recital` AS t25_r14,
titles_projects.`completed_flag` AS t25_r15,
titles_projects.`completed_at` AS t25_r16,
titles_projects.`position` AS t25_r17,
titles_projects.`created_at` AS t25_r18,
titles_projects.`updated_at` AS t25_r19 FROM projects
LEFT OUTER JOIN materials ON materials.work_id =
projects.id AND materials.work_type = 'Project' LEFT
OUTER JOIN tags ON tags.id = materials.tag_id LEFT
OUTER JOIN uploaded_files ON uploaded_files.id =
materials.material_file_id 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 tags tags_topics ON
tags_topics.id = topics.tag_id LEFT OUTER JOIN
readerships ON readerships.topic_id = topics.id LEFT
OUTER JOIN roles ON roles.authorizable_id =
projects.id AND roles.authorizable_type = 'Project'
LEFT OUTER JOIN roles_users ON roles_users.role_id =
roles.id LEFT OUTER JOIN users ON users.id =
roles_users.user_id LEFT OUTER JOIN titles ON
titles.project_id = projects.id LEFT OUTER JOIN
materials materials_titles ON materials_titles.work_id
= titles.id AND materials_titles.work_type = 'Title'
LEFT OUTER JOIN tags tags_materials ON
tags_materials.id = materials_titles.tag_id LEFT OUTER
JOIN uploaded_files material_files_materials ON
material_files_materials.id =
materials_titles.material_file_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 tags tags_topics_2 ON tags_topics_2.id =
topics_posts.tag_id LEFT OUTER JOIN readerships
readerships_topics ON readerships_topics.topic_id =
topics_posts.id LEFT OUTER JOIN roles
accepted_roles_titles ON
accepted_roles_titles.authorizable_id = titles.id AND
accepted_roles_titles.authorizable_type = 'Title' LEFT
OUTER JOIN roles_users users_roles_join ON
users_roles_join.role_id = accepted_roles_titles.id
LEFT OUTER JOIN users users_roles ON users_roles.id =
users_roles_join.user_id LEFT OUTER JOIN materials
materials_projects ON materials_projects.work_id =
projects.id AND materials_projects.work_type =
'Project' LEFT OUTER JOIN forums forums_projects ON
forums_projects.work_id = projects.id AND
forums_projects.work_type = 'Project' LEFT OUTER JOIN
roles accepted_roles_projects ON
accepted_roles_projects.authorizable_id = projects.id
AND accepted_roles_projects.authorizable_type =
'Project' LEFT OUTER JOIN titles titles_projects ON
titles_projects.project_id = projects.id WHERE
((readerships.read != '1' OR readerships_topics.read
!= '1') AND (roles_users.user_id = '6' OR
users_roles_join.user_id = '6'))


As you can see I have two left outerjoins involving
the readerships table:
LEFT OUTER JOIN readerships ON readerships.topic_id =
topics.id
and
LEFT OUTER JOIN readerships readerships_topics ON
readerships_topics.topic_id = topics_posts.id

and I have a condition on both of those tables in my
where clause:

WHERE (readerships.read != '1' OR
readerships_topics.read != '1')

Now what I was wondering is: is there a way to write
something like :

WHERE *.read != '1'

Which would match all tables with a read column?

If not is there a way to match all readerships table
in my where clause?
so for example
WHERE readerships.read != '1'

where readerships.read would match both readerships
and readerships_topic?

I can't just the left outer join part as it's
autogenerated but I can change the WHERE clause

I'm not good a sql syntax, but I would love to
learn....

Thanks


__________________________________________________ ___________________________
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail

tom wang wrote:
> Hi,
>
> I have the following sql request:


[snipped, for the sake of the children]

> As you can see I have two left outerjoins involving
> the readerships table:
> LEFT OUTER JOIN readerships ON readerships.topic_id =
> topics.id
> and
> LEFT OUTER JOIN readerships readerships_topics ON
> readerships_topics.topic_id = topics_posts.id


I'll take your word for it!

> and I have a condition on both of those tables in my
> where clause:
>
> WHERE (readerships.read != '1' OR
> readerships_topics.read != '1')


Not that I'm going to pretend to understand the goal of the query, but
couldn't you do:

LEFT OUTER JOIN readerships ON readerships.topic_id =
topics.id AND readerships.read != '1'
AND
LEFT OUTER JOIN readerships readerships_topics ON
readerships_topics.topic_id = topics_posts.id
AND readerships_topics.read != '1'


> Now what I was wondering is: is there a way to write
> something like :
>
> WHERE *.read != '1'
>
> Which would match all tables with a read column?

No, you cannott use the asterisk selector in a WHERE condition.

> If not is there a way to match all readerships table
> in my where clause?
> so for example
> WHERE readerships.read != '1'
>
> where readerships.read would match both readerships
> and readerships_topic?

readerships.read matches readerships.read, nothing more.

> 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 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?

brian

tom wang schrieb:
> Hi,
>
> I have the following sql request:
>
> SELECT projects.`id` AS t0_r0, projects.`name` AS
[..endless sql..]

Hi Tom,

did you understand that query (in lets say 3 months) if you need to fix
a bug? If not it maybe better to simplify that.

regards
-ralf

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')"], rder=>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

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')"], rder=>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 . 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@subtropolix.org 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')"],
> rder=>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
> . 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
Thomas


__________________________________________________ ___________________________
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail