How do I use rows-as-fields in a SQL database

Question

I've got a SQL related question regarding a general database structure that seems to be somewhat common. I came up with it one day while trying to solve a problem and (later on) I've seen other people do the same thing (or something remarkably similar) so I think the structure itself makes sense. I just have trouble trying to form certain queries against it.

The idea is that you've got a table with "items" in it and you want to store a set of fields and their values for each item. Normally this would be done by simply adding columns to the items table, the problem is that the field(s) themselves (not just the values) vary from item to item. For example, I might have two items:

Article 1
  product_id = aproductid
  hidden_key = ahiddenkeyvalue
Article 2
  product_id = anotherproductid
  address = anaddress

You can see that both items have a product_id field (with different values) but the data stored for each item is different.

The structure in the database ends up something like this:

ItemsTable
  id
  itemdata_1
  itemdata_2
  ...

FieldsTable
  id
  field_name
  ...

And the table that relates them and makes it work

FieldsItemRelationsTable
  field_id
  item_id
  value

---

Well when I'm trying to do something that involves just one "dynamic field" value there's no problem. I usually do something similar to:

SELECT i.* FROM ItemsTable i
INNER JOIN FieldsItemRelationsTable v ON v.item_id = i.id
INNER JOIN FieldsTable f ON f.id = v.field_id
WHERE v.value = 50 AND f.name = 'product_id';

Which selects all items where product_id=50

The problem arises when I need to do something involving multiple "dynamic field" values. Say I want to select all items where product_id = 50 AND hidden_key = 30. Is it possible with a single SQL statement? I've tried:

SELECT i.* FROM ItemsTable i
INNER JOIN FieldsItemRelationsTable v ON v.item_id = i.id
INNER JOIN FieldsTable f ON f.id = v.field_id
WHERE (v.value = 50 AND f.name = 'product_id')
AND (v.value = 30 AND f.name = 'hidden_key');

But it just returns zero rows.

Answer 1

• It's called EAV

• Some people hate it

• There are alternatives (SO)

Sorry to be vague, but I would investigate your options more.

Answer 2

You'll need to do a seperate join for each value you are bringing back...

SELECT i.* FROM ItemsTable i 
INNER JOIN FieldsItemRelationsTable v ON v.item_id = i.id 
INNER JOIN FieldsTable f ON f.id = v.field_id 
INNER JOIN FieldsItemRelationsTable v2 ON v2.item_id = i.id 
INNER JOIN FieldsTable f2 ON f2.id = v2.field_id 
WHERE v.value = 50 AND f.name = 'product_id'
AND (v2.value = 30 AND f2.name = 'hidden_key'); 

er...that query might not function (a bit of a copy/paste sludge job on my part), but you get the idea...you'll need the second value held in a second instance of the table(s) (v2 and f2 in my example here) that is seperate than the first instance. v1.value = 30 and v2.value = 50. v1.value = 50 and v1.value = 30 should never return rows as nothing will equal 30 and 50 at the same time

As an after thought...the query will probably read easier had you put the where clause in the join statement

SELECT i.* FROM ItemsTable i 
INNER JOIN FieldsItemRelationsTable v ON v.item_id = i.id and v.value = 50 
INNER JOIN FieldsTable f ON f.id = v.field_id and f.name = 'product_id'
INNER JOIN FieldsItemRelationsTable v2 ON v2.item_id = i.id and v2.value = 30 
INNER JOIN FieldsTable f2 ON f2.id = v2.field_id and f2.name = 'hidden_key'

Functionally both queries should operate the same though. I'm not sure if there's a logical limit...in scheduling systems you'll often see a setup for 'exceptions'...I've got a report query that's joining like this 28 times...one for each exception type returned.

Answer 3

Try doing some left or right joins to see if you get any results. inner joins will not return results sometimes if there are null fields.

its a start.

Dont forget though, outer join = cartesian product