Reputation: 7696
MySQL: Select row from table which name is specified in a field of another table
I have a feed table with say fields:
id- unique feed idcreated- the date the feed was createdtable- the name of the table the rest of the feed info resides
Then I have say 2 tables: feed_image and feed_text. Now these 2 tables contain different information about a feed, different fields.
How is it possible (in MySQL) to extract the information for the feed from the appropriate table which name is specified in feed.table?
Here is how my schema looks like:
+------------------+
| table_a |
+---------------------+ |------------------|
| feed | | id |
|---------------------| +------+ feed_id |
| id <-------------------+-+ | field_in_a |
| created | | | ... |
| table | | | |
| | | | |
| | | | |
| | | +------------------+
+---------------------+ |
|
|
| +-------------------+
| | table_b |
| |-------------------|
| | id |
+--------+ feed_id |
| field_in_b |
| ... |
| |
| |
| |
| |
+-------------------+
Each feed exists either in table_a or table_b or table_c or ... (I have like 30 of them). How can I specify which table to extract the info from (each table has a different structure).
Or, if I add indexes on each table_*.feed_id and map it to feed.id, would InnoDB do some magic, so when I JOIN them all it would look in just one of them, not all 30?
My latest idea is to have just one table feed with a field feed.content where I would store a serialized PHP object of a different PHP class representing the different feed type and its individual contents.
What is the best way to go regarding performance?
P.S.: No records would need to be selected / searched / ordered by individual parameters, just by created. The idea should be able to work well with 1 000 000+ records.
UPDATE:
To clarify about the 30+ table_a/b/c..
Each feed can be of too many different types (new ones will also be added with time):
- An image feed would have VARCHAR(255)
urlfield - A text feed would have LONGTEXT
textfield - A youtube.com feed would have VARCHAR(255)
title, VARCHAR(255)video_idfields - A *.com feed would have *
x1, *x2, *x3... fields
Each of these feeds will be then displayed with PHP according to type:
- An image will be displayed as na image from the given URL
- A text will be displayed as a pure text
- A youtube.com feed would display a video player with the given title from the given video id
- A *.com feed would display... :)
Upvotes: 2
Views: 412
Answers (3)
Reputation: 5701
I would use a LEFT JOIN and alias my columns in the select and alias my tables in the join allowing you to return any and all information you need.
The with whatever language your pulling the results you can group and perform logic as necessary.
UPDATE:
Why do you have 30 tables exactly? Maybe one "meta" table with the feed creation date url it came from etc... and another table that contains a unique record id, feed id, content, content type.
That way you can join on one table where feed id's match as well as group by or filter by content type.
Visualization: Feed table
--------------------------------------------------------------
| feed_id | feed_name | feed_created | Feed_url |
--------------------------------------------------------------
| 1 | Feed 1 | 03/28/2012 | www.go.com |
--------------------------------------------------------------
| 2 | Feed 1 | 03/28/2012 | www.be.com |
--------------------------------------------------------------
| 3 | Feed 2 | 03/28/2012 | www.hi.com |
--------------------------------------------------------------
| 4 | Feed 3 | 03/28/2012 | www.ex.com |
--------------------------------------------------------------
Visualization: Feed Resources table
------------------------------------------------------------------------------------------------
| rec_id | feed_id | content | type |
------------------------------------------------------------------------------------------------
| 1 | 1 | 'hello world! | text |
--------------------------------------------------------------------------------------
| 2 | 3 | 'http://me.com/my-image | img |
------------------------------------------------------------------------------------------------
| 3 | 2 |{\'title\':\'VIDEO\',\'url\':\'http://me.com/1.mov\'}| vid |
------------------------------------------------------------------------------------------------
| 4 | 1 | 'Wow that was easy!' | text |
------------------------------------------------------------------------------------------------
Upvotes: 3
Reputation: 26
You should create a normalized layout as d_inevitable suggested.
You haven't told us exactly how you're displaying this data. But you can get a list of ALL feeds with select * from feed;
Then you can get additional data for the feed by searching the other tables. For your example of URLs, if table_a = URLs and field_in_a = URL
Whichever feed you're on, you'd search for URLs with the ID for that feed. select * from URLs where feed_id = "id" This would allow each feed to have 1 to many URLs associated with it. You could do this for each type of data you'd have associated with a feed. The "feed_id" is your Foreign Key that you use to reference which feed it is.
The key is going to come down to how you're displaying this. You're going to need to loop through all the Feeds, and then build a table (?) appropriately.
If a feed has two URLs, how do you want it to look?
Should it display
------------------------------------------------- | Feed Name | Feed Created | URL | ------------------------------------------------- | Feed 1 | 03/28/2012 | www.go.com | ------------------------------------------------- | Feed 1 | 03/28/2012 | www.be.com | ------------------------------------------------- | Feed 2 | 03/28/2012 | www.hi.com | ------------------------------------------------- | Feed 3 | 03/28/2012 | | -------------------------------------------------
or
------------------------------------------------- | Feed Name | Feed Created | URL | ------------------------------------------------- | Feed 1 | 03/28/2012 | www.go.com | | | | www.be.com | ------------------------------------------------- | Feed 2 | 03/28/2012 | www.hi.com | ------------------------------------------------- | Feed 3 | 03/28/2012 | | -------------------------------------------------
I think the data layout should be as d_inevitable suggested, and then you need to determine how you're going to display the data, and that will determine how you query it.
Upvotes: 1
Reputation: 4461
Can't you do something like this:
+------------------+
| table_a |
+---------------------+ |------------------|
| feed | | id |
|---------------------| +------+ feed_id |
| id <-------------------+-+ | field_in_a |
| created | | | ... |
| | | | |
| | | | |
| | | | |
| | | +------------------+
+---------------------+ |
|
|
| +-------------------+
| | table_b |
| |-------------------|
| | id |
+--------+ feed_id |
| field_in_b |
| ... |
| |
| |
| |
| |
+-------------------+
And then join the records from table_a and table_b? MySQL is pretty efficient at that.
Upvotes: 2
Related Questions
- How to SELECT one field form one table which is part of string contained in one field of another table?
- Select COLUMN name from one table based on VALUE from another in mySQL?
- Select column based on column name stored in another table
- Query one table, to get name from another table
- get name from one table with match id in another table?
- Selecting table rows from one table where value of table column in another table is x
- selecting a record from a table based on column value of another table
- Selecting a name from another table using a foreign key
- how to select a column from one table according to another table column?
- Selecting rows from a table by One a field from other table