Query matching join to the right table

Databases
1

I’m trying to match comments to resources in different tables.

Each comment has a resource_type and resource_id
The resource type is the name of table that the resource is part of, and the id is the id from that table.

So it might look like:

[FONT=“Courier New”]

Comments
resource_type | resource_id | content

photos | 21 | blah blah blah
documents |63 | blah blah blah

photos
id | title

documents
id | title
[/FONT]

Is it possible to write a query that would select the title from the right resource table for each comment?

Thanks E

2

r937 it is interesting to hear your recommendation, but honestly it sounds like half dozen of one, six dozen of the other. I don’t see a benefit of having 3 tables containing basically identical information over writing the outer join query. Which I got working by the way …Thanks!

3

in that case i would have a comments table for documents, another comments table for articles, etc.

4

I really wouldn’t consider this a Jam. Just use two joins as r937 pointed out. I don’t think its necessary to redesign.

5

if you wanted to design a comment system for photos like a blog…

you would do the following

photos
id | title | photo
2 hi | 123.jpg
3 bye | 1234.jpg
4 hello | ***.jpg
5 yay | **jpg

comments
id | comment | photo_id
1 | abc | 2
2 | abc | 2
3 | abc | 3

select * from comment where photo_id=‘2’

it’ll show you all the comments posted for photo 2 (123.jpg)

so what yo’ll do is loops php foreach for all the comments with photo_id 2

6

Oh, I probably wasn’t clear enough for you to answer that. This is comments like on a blog where members can comment on documents, articles, etc… So there needs to be support for multiple comments with different authors, dates, etc…

Thanks E

7

What would be a better way of constructing this?
How would you recommend pairing a comment with different objects?

Thanks
E

8

how would i do it?

put the comments into the same tables as the things they are commenting

photos
id | title | comments

documents
id | title | comments

9

yes, but this query would need a separate LEFT OUTER JOIN for each table

the design, in my opinion, is faulty

any time you find yourself storing metadata (such as a table name), you will find yourself in jams like this

10

that’s exactly what i’m sayin.