MySQL joining tables one-to-many relationship

Question

I am trying to achieve something in MySQL but even with all the answers here or on other sites, I still cannot accomplish the goal.

I have two tables, in one-to-many relationship.

TABLE Files
COLUMNS id, title, description, uploaded, size, extension, etc.

TABLE Files_Meta
COLUMNS id, parent_id, key, value

Obviously each file has multiple meta data represented as many rows in the Files_Meta table. For example File1 has meta Author, Place, Time, Tags, Camera -- if it's a photo.

I'm trying to select all rows from Files table including meta data.

Standard result

stdClass Object
(
[id] => 10
[title] => Hello world
[size] => 745198
[extension] => jpg
[user_id] => 0
[category_id] => 0
[date_uploaded] => 2012-06-08 13:37:55
[description] => 
[downloaded] => 0
[viewed] => 8
)

stdClass Object
(
[id] => 90
[parent_id] => 10
[key] => place
[value] => New York
)

What I want

stdClass Object
(
[id] => 10
[title] => Hello world
[size] => 745198
[extension] => jpg
[user_id] => 0
[category_id] => 0
[date_uploaded] => 2012-06-08 13:37:55
[description] => 
[downloaded] => 0
[viewed] => 8
[meta] => Array (
    place => New York
    author => John Doe
    time => March 2001
    camera => Canon EOS
    etc.
    )
)

Is it possible to achieve this in MySQL? Doesn't have to look like this, without that array.

stdClass Object
(
[id] => 10
.
.
[place] => New York
[author] => John Doe
[time] => March 2001
[camera] => Canon EOS
)

Thanks in advance for replies or tips.

`MySQL` can only return 'flat' rows, so no, not in strict sql, you'll have to build it in PHP, but it would require only 2 queries & 1 `foreach` loop. — Wrikken, Jun 20 '12 at 15:32
Is `Files_Meta.parent_id` self-referential or is that the foreign key to `Files`? @Wrikken, 2 queries? You can do it in 1 ;) — Jason McCreary, Jun 20 '12 at 15:33
Yes, I have done it easily in PHP but the queries are the reason I asked. For a large amount of files + associated meta data is the number of queries pretty big, although they are simple and very fast queries. — Hynek Zatloukal, Jun 20 '12 at 15:36
@JasonMcCreary: strictly speaking, you can do it in one if you want all data from `Files` do duplicate for every `Meta` entry yes.... Which would be terribly inefficient and needless load on both the database server & PHP. — Wrikken, Jun 20 '12 at 15:40
... EAV tables are often regarded as an anti-pattern, although you could probably make a case for them here. That said, I think you probably ought to _try_ doing a table-per-type approach. Are you looking for help with the SQL, or with your PHP code? The `id` column in `Files_Meta` (please name tables as singular, not plural) is superfluous - the tuple (`fileId`, `key`) should be unique to begin with (and is the actual lookup key of the table). If you continue down this route, you're probably going to want a separate `file_meta_key` table, so you don't end up keys like 'time' and 'TIME'. — Clockwork-Muse, Jun 20 '12 at 15:45
@Wrikken, I read this as a 1 to many relationship. Not many to many. In which case, indeed that's true. — Jason McCreary, Jun 20 '12 at 15:45
@JasonMcCreary: your solution duplicates data from `Files` for every `Meta` exactly as I stated... How is this better / different? — Wrikken, Jun 20 '12 at 15:54
@Wrikken, I understand. My solution referenced 1 or 2 queries. In addition to your initial comment. I would encourage you to post your own answer or comment on mine if you have an alternate solution. — Jason McCreary, Jun 20 '12 at 17:41

score 1 · Accepted Answer · edited May 23 '17 at 10:34

1

Doing this in a single query could be as follows:

SELECT * FROM Files INNER JOIN Files_Meta ON Files.id = Files_Meta.parent_id

Per the comments, you will need PHP to create the structure you desire as MySQL only returns results in a flat fashion.

I strongly advise writing your code to do this in a single query or with 2 queries - one for all Files and another for all Files_Meta. Then stitch the data together with PHP. Otherwise, you may create an N+1 Problem.

As an aside, I recommend changing parent_id to file_id. parent_id typical denotes of a self-referential key. Whereas file_id would denote a foreign key to the File table.

edited May 23 '17 at 10:34

Community

1
1

answered Jun 20 '12 at 15:39

Jason McCreary

71,546
23
135
174

I've chosen `parent_id` because I use similar structure in other tables (Categories, Users, etc.) I have to admit though, it's not very clear. – Hynek Zatloukal Jun 20 '12 at 15:46
Fair. But you may have trouble if you adopt an ORM. As they will likely have such conventions by default. – Jason McCreary Jun 20 '12 at 15:47
This way, I get many times more result -- depending on how many meta items each file has. Is there a way to merge it? – Hynek Zatloukal Jun 20 '12 at 15:49
Well, I am adapting an ORM now, so probably the solution is there :) – Hynek Zatloukal Jun 20 '12 at 15:51
You could query specific records by adding `WHERE`. But this could introduce inefficiency (i.e. N+1). However, if you are going with an ORM, it will likely solve all of this for you. – Jason McCreary Jun 20 '12 at 15:53

score 0 · Answer 2 · answered Jun 20 '12 at 15:35

As you also tagged PHP - I would recommend you to use an PHP ORM Framework like Doctrine or Propel which would give you a structure like that (which is very abstract):

class File {

/* members */

var id // int
var title // string
var description // string
var uploaded // boolean
var size // int
var extension // string
var metadata // array (Collection of FileMeta)

/* getters setters for each member */
...

}

class FileMeta {

var id // int
var fileId // the id to the File object
var file // the fileobject itself
var key // string
var value // string

}

The only thing you have to do is define your structure (maybe exported from your database) and thats it.

I hope this is not too far away from what you searched.

Thanks, I am already searching for a ORM solution. I've chosen DataMapper or Doctrine (I'm building on CodeIgniter). That would be probably easier :) — Hynek Zatloukal, Jun 20 '12 at 15:40
Fine, I am using doctrine (with symfony) which is very easy to learn and to work with. — Del Pedro, Jun 20 '12 at 15:43

EmmanuelG · Answer 3 · 2012-06-20T15:46:57.953

Sounds like you are looking for a join. This would be great if there was only one meta per file. More than one meta per file would result in numerous resulting rows with only the meta data being different. However, I assume since you are displaying photo data that this is not true and would only get one meta per file so I would suggest this SQL:

SELECT 
    Files.*,
    Files_Meta.place, Files_Meta.author, Files_Meta.time, Files_Meta.camera, ..etc 
FROM 
    Files
JOIN Files_Meta ON Files_Meta.parent_id = Files.id

This will give you a flat representation consisting of the data you were fetching in two separate queries and then combining via PHP. Relying on your database is always better than having to manually stitch stuff together when possible and as long as the parent_id and the id in files are indexed the query should be lightning fast.

There is no column File_Meta.place or File_Meta.author. Only File_Meta.key. — Hynek Zatloukal, Jun 20 '12 at 15:44
I have edited my answer since I left out the plural tenses in several of the table names in my answer. However the overall idea is the same. You would add more fields from Files_Meta in the query to get all the fields you woud want. — EmmanuelG, Jun 20 '12 at 15:48

MySQL joining tables one-to-many relationship

Standard result

What I want

3 Answers3