使用codeigniter / php的实体属性值模型

SO I am trying to create a way to structure my database to be able customize forms.

I looked into EAV pattern and here is my db structure:

Table form - form_id - form_name - form_added_on - form_modified_at

Table: form_fields - field_id - form_id - field_type (TEXT, RADIO etc..) - field_default_value - field_required

Table: form_data - data_id - field_id - form_id - field_value

so now I can store any custom form into the database and if I want to get the values for an individual form I can simply join it by "form_id" ..

the problem:

I want to be able to search through all the forms for a specific field value.

How can I do that with EAV model?

Also, I thought about just storing the custom data as a serialized (JSON) object but then I am not sure how can I query that data.

Please note that I am using Codeigniter with MYSQL. So if conversation can use Codeigniter libraries if needed.

Disclaimer: I don't know PHP or CodeIgniter, but I'd willing to assert that neither has any sort of built in support for EAV. That noted, I know a lot about EAV so I'll answer in that light.

When you write about searching for a specific value, I'm assuming you mean in a particular field as well. So with that stated, put the data in a XML CLOB off of the form table (e.g. data) and use MySQL's XML functions to search against it. Seriously. Let's say the XML looks like:

<data>
    <field id="[field_id]">value</field>
</data>

Search it as as such:

SELECT f.form_id
FROM
  form f
WHERE 
  f.form_id = ?
  AND ExtractValue(data, '//field[@id="[field_id]"]') = ?

Why? The issue is searching multiple criteria against an EAV model is challenging. Consider this example:

SELECT f.form_id
FROM
  form f
    INNER JOIN form_fields f1 ON f1.form_id = f.form_id
    INNER JOIN form_fields f2 ON f2.form_id = f.form_id
WHERE 
  f.form_id = ?
  AND 
  (f1.field_id = ? AND f1.field_value = ?)
  AND 
  (f2.field_id = ? AND f2.field_value = ?)

This all seems well and good, now change the AND to OR and all hell breaks loose.

SELECT f.form_id
FROM
  form f
    INNER JOIN form_fields f1 ON f1.form_id = f.form_id
    INNER JOIN form_fields f2 ON f2.form_id = f.form_id
WHERE 
  f.form_id = ?
  OR 
  (f1.field_id = ? AND f1.field_value = ?)
  OR 
  (f2.field_id = ? AND f2.field_value = ?)

Do you see the issue? The INNER JOIN in the FROM clause means records no matter what, data is returned no matter the WHERE clause. So instead of JOIN in the FROM clause + WHERE clause, EAV demands an EXISTS in the WHERE:

SELECT f.form_id
FROM
  form f
WHERE 
  f.form_id = ?
  AND (
    EXISTS (
      SELECT f1.form_id FROM form_fields f1 
      WHERE f1.field_id = ? AND f1.field_value = ? AND f1.form_id = f.form_id
    )
    -- note OR search 
    EXISTS (
      SELECT f2.form_id FROM form_fields f2
      WHERE f2.field_id = ? AND f2.field_value = ? AND f2.form_id = f.form_id
    )
  )

Kinda ugly huh? That and MySQL subquery performance is not good. Add in the desire to search on specific data types, such as dates and integers, and you're either casting or working with multiple columns in the form_field table (e.g. field_date_value, field_int_value, etc...).

So the XML CLOB means only table to consider when querying and also the possibility of multivalued attributes as well (multiselects or checkboxes come to mind).

First of all, the form_id field in your form_data table seems to be redundant. Each form_data record references a form_fields record (via field_id), and each form_fields record references a form record (via form_id). So you shouldn't need to put form_id in the form_data table.

Your questions seems to be: How do you get the form_id value based on a specific field_value?

Here is a simple SQL query that should do the trick:

SELECT f.form_id FROM form_data d 
JOIN form_fields f USING(field_id) 
WHERE d.field_value = '??????'

From your question it looks like your requirement is to get the form id when you have form field type and field value -

For achieving this you can try out the below query -

First fire this query to get all form id's with specific field type =

    $sql = "SELECT ff.form_id from form_fields ff where ff.field_type = 'The field type you are searching for'";

    $result = $this->db->query($sql);

    $form_ids = $result->result_array();

Now you can use this array in second query to obtain your final result as below -

    $this->db->select('fd.form_id');
    $this->db->from('form_data fd');
    $this->db->where('fd.field_value','your field value');
    $this->db->where_in('fd.form_id',$form_ids);
    $required_form_id = $this->db->get();

so in variable $required_form_id you will be having required form id.

my suggestion is that you use enum for field_type column in form_fields table.

Hope this helps you out.