join a table based on a previously joined tables column value

bike5

I need to join a table thats name is a value in another table that was joined onto the query, here is my query:

SELECT * 
		FROM 
			flare_objects_obj
		LEFT JOIN 
			flare_objects_links ON (flare_objects_links.group_id = flare_objects_obj.id) 
		LEFT JOIN 
			flare_objects_attr ON (flare_objects_attr.id = flare_objects_links.attr_id) 
		LEFT JOIN
			flare_dt_map ON (flare_dt_map.id = flare_objects_attr.data_type_id) 
		LEFT JOIN 
			flare_dt_map.table ON (flare_dt_map.table.id = flare_objects_links.val_id) 
		WHERE
			flare_objects_obj.id = 1

The last join is the problem LEFT JOIN flare_dt_map.table ON (flare_dt_map.table.id = flare_objects_links.val_id) The 2 bolded names are the name of the table I need to join. Is something like this even possible? I think sub queries might be needed but I am still trying to figure out how to write one for this. If you have any input let me know, thank you.

Piranha

Why do you need to do that? It seems that if the database is designed correctly it should not be needed.

Please provide the relevant database structure of flare_objects_links and flare_dt_map to allow us to help you out better.

bike5

I am messing around with making my own ORM (yes I realize there are a few free php ones already built) for fun and to hopefully learn a few new things during the process. I want to do this with minimal redundant data in the database also.

flare_dt_bool
id int(10)
value tinyint(1)

flare_dt_char
id int(10)
value varchar(255)

flare_dt_decimal
id int(10)
value decimal(10,2)

flare_dt_int
id int(10)
value int(11)

flare_dt_longtext
id int(10)
value longtext

flare_dt_map
id int(10)
table varchar(64)

flare_dt_text
id int(10)
value text

flare_objects
id int(10)
name varchar(255)

flare_objects_attr
id int(10)
object_id int(10)
data_type_id smallint(2)
name varchar(255)

flare_objects_links
group_id int(10)
val_id int(10)
attr_id int(10)

flare_objects_obj
id int(10) No
object_id int(10) No
assoc_table varchar(64)
assoc_col varchar(64)
assoc_val varchar(255)

tomhath

I've seen various attempts at this. After fighting it for a while, designers tend to settle on one of two options, using one table for almost everything:

1) Put everything in a varchar column and convert as needed
2) Put a nullable column of each type in a the table and grab the column you want

BLOB and TEXT data are usually handled separately with another table or two that's LEFT JOINed .

bike5

I am not quite sure I understand #2. Do you mean have 1 table with say columns called:
int | char | decimal | ect.
and have them all nullable columns and whatever column doesn't have a null is the value you are looking for?

As far as putting text in another table, do you just always left join the table on the pointer id and if its found it must be text if it isn't then it must have been a field in the other table. If so, wouldn't you need to syncornize the ids in the text and "general" table so they are unique between both tables? (otherwise you might get text and another column values returned from one id value)

What kind of performance / diskspace issue would would have if you put everything including text into one table and everything was null except the column being used?

tomhath

That's the idea of #2, except you use better column names. For the TEXT table you store the data in the second table, then store its ID in the first.

As far as performance, it's hard to say. Since yours is a learning project it would be worth trying out a couple of ideas to get a feel for what happens.