2

I need a way to search across dynamically generated fields. A user will search for 3 required fields and n number of optional fields.

This is for search functionality.

Said user might want to look for any issue that is on the West elevation (field_id->1, field_value->West), on the 6th Floor (field_id->4, field_value->6), and in room #52 (field_id->2, field_value-> 52).

Here is what I have so far:

$query = "SELECT inspection_finding_id
      FROM if_field_values
      WHERE field_id IN (1, 2, 4) AND field_value IN ('West', 6, 52)
      GROUP BY field_id, field_value, inspection_finding_id";
$result = mysql_query($query);

I only need the id's that match ALL of the criteria... With the above I get some orphan records that are also contain data on the 6th floor.

I dont have enought rep to show an image of my table so here is a sad attempt to display my table

| id | field_id | field_value |
+----+----------+-------------+
|161 |     1    |    West     |
|161 |     4    |    6        |
|161 |     2    |    52       |
|163 |     4    |    6        |
user1328264
  • 23
  • 4
  • +1 for including text sample data. If you include a link to a web-accessable image then other users can follow the link to the image, and higher-rep users can edit your answer for you to add the image to the body of the question. –  May 28 '13 at 06:18

2 Answers2

1

A general solution for finding entities satisfying all attribute conditions in EAV model is to separate all conditions by OR, GROUP BY entity and filter results with HAVING count(*) equal to number of conditions.

SELECT inspection_finding_id
FROM if_field_values
WHERE field_id = 1 AND field_value = 'West'
   OR field_id = 4 AND field_value = '6'
   OR field_id = 2 AND field_value = '52'
GROUP BY inspection_finding_id
HAVING COUNT(DISTINCT field_id) = 3;
piotrm
  • 12,038
  • 4
  • 31
  • 28
0

You need to join the table with itself, a similar problem to yours is archived.

If you're trying to use IN you'll get multiple rows that you'll need to count afterwards to see if they match and it'll be a mess with your optional fields.

Community
  • 1
  • 1
Patrice Levesque
  • 2,079
  • 17
  • 16
  • Won't that leave me with doing countless INNER JOINS for as many options as I have? – user1328264 May 28 '13 at 05:13
  • Yes. But you're not really using MySQL as intended, are you? create a schema where you have a single row per ID and queries will be much simpler and performant. – Patrice Levesque May 28 '13 at 05:15
  • I suggest you read this past post about pitfalls caused by schemas like yours. http://stackoverflow.com/questions/126271/key-value-pairs-in-relational-database — that'll explain a lot better than my previous comment what I mean by “MySQL as intended” :) – Patrice Levesque May 28 '13 at 05:26
  • OP explicitly stated fields are being dynamic. It's not a fast model but still better than having lots of columns, most of them empty for many rows and using `ALTER` every time a new attribute is required. – piotrm May 28 '13 at 06:08