So yeah, I've tried to wrap my head arround this but I cant think of a solution. First of all it works fine nothing broken, yet I'm not satisfied the way I did it and I thought there might be a better way to accomplish this.
Ok what I want is this:
["id"] => string(1) "1"
["name"] => string(4) "name"
["type"] => string(4) "text"
["entity"] => string(4) "user"
["required"] => string(1) "1"
["sorting"] => string(2) "99"
["validator_id"] => string(1) "1"
["validator_name"] => string(12) "StringLength"
["filter_id"] => string(1) "1"
["filter_name"] => string(10) "StringTrim"
["form_field_id"] => string(1) "1"
["form_validator_id"] => string(1) "1"
["validator_options"] => string(20) "a:1:{s:3:"min";i:2;}"
What I do:
$q = 'SELECT
ff.*, fhff.sorting
FROM
form_field ff
INNER JOIN form_has_form_field fhff ON fhff.form_field_id = ff.id
WHERE
ff.id IN (
SELECT
fhff.form_field_id
FROM
form_has_form_field fhff
WHERE
fhff.form_id = "'.$id.'"
)
ORDER BY fhff.sorting ';
Then I do a select on the form table to get the name
Now I do some checks if i have results, merge clean it, append the formname, etc...
the result then looks like this:
["id"] => string(1) "2"
["name"] => string(4) "test"
["type"] => string(8) "textarea"
["entity"] => string(4) "user"
["required"] => string(1) "0"
["sorting"] => string(2) "70"
And loop over this query with the results:
$q = '
SELECT
form_validator.id as validator_id,
form_validator.`name` as validator_name,
form_filter.id as filter_id,
form_filter.`name` as filter_name,
form_field_has_form_validator.form_field_id,
form_field_has_form_validator.form_validator_id,
form_field_has_form_validator.`options` as validator_options
FROM
form_field
INNER JOIN form_field_has_form_filter ON form_field_has_form_filter.form_field_id = form_field.id
INNER JOIN form_field_has_form_validator ON form_field_has_form_validator.form_field_id = form_field.id
INNER JOIN form_filter ON form_field_has_form_filter.form_filter_id = form_filter.id
INNER JOIN form_validator ON form_field_has_form_validator.form_validator_id = form_validator.id
HAVING form_field_id = "'.$r['id'].'"
';
where $r['id']
equals the id from the first result array
When I'm done, I merge this arrays and they look like the very first array I showed you.
Now back to the Question:
Can I somehow merge theses 3 queries into 1 and would that even benefit in terms of performance?
Edit:
Heres a screen of how these tables are connected http://s14.directupload.net/images/140321/peailx3y.png http://i60.tinypic.com/nnmr9t.png http://s21.postimg.org/9bzr3f8dj/nnmr9t.png
And a piece of additional information: I only have the form.id in the beginning and try to get all the info from the rest of the tables which have a connection to the form_field_table
PS: OFC I'm using PDO
Okay I'm going to try to answer this as best as I can given the question.
First you probably don't need to loop that query over the results. You need to add those results to a comma separated string or something of that nature and input them into your SQL statement using IN
Note:I'm not even going to bother going into exists with this question so don't ask question viewers.
Second you DO NOT want to use a HAVING
clause in this situation as it applies the filter after gathering all the results. For performance this sucks on ice and is not something you want to do. So applying these two fixes makes you solution look like this.
SELECT
form_validator.id as validator_id,
form_validator.`name` as validator_name,
form_filter.id as filter_id,
form_filter.`name` as filter_name,
form_field_has_form_validator.form_field_id,
form_field_has_form_validator.form_validator_id,
form_field_has_form_validator.`options` as validator_options
FROM form_field
INNER JOIN form_field_has_form_filter
ON form_field_has_form_filter.form_field_id = form_field.id
INNER JOIN form_field_has_form_validator
ON form_field_has_form_validator.form_field_id = form_field.id
INNER JOIN form_filter
ON form_field_has_form_filter.form_filter_id = form_filter.id
INNER JOIN form_validator
ON form_field_has_form_validator.form_validator_id = form_validator.id
WHERE form_field.form_field_id IN (YourCommaSeperatedValueList)
Next lets talk formatting. Since the table names are a bit messy we are going to use aliasing to make it easier to read. This should help with you WHERE
clause error you mentioned in the comments as well.
SELECT
FV.id as validator_id,
FV.`name` as validator_name,
FF.id as filter_id,
FF.`name` as filter_name,
FFV.form_field_id,
FFV.form_validator_id,
FFV.`options` as validator_options
FROM form_field as F
INNER JOIN form_field_has_form_filter FH
ON FH.form_field_id = F.id
INNER JOIN form_field_has_form_validator FFV.
ON FFV..form_field_id = F.id
INNER JOIN form_filter FF
ON FH.form_filter_id = FF.id
INNER JOIN form_validator AS FV
ON FFV.form_validator_id = FV.id
WHERE F.form_field_id IN (YourCommaSeperatedValueList)
There that looks much better and now your WHERE
clause is easier to get right because you don't have overlapping column names.
Now last but not least find the person who name those tables and punch them directly in the face.