I want to implement a filter-function in my PHP project. To implement a filter, I usually just add a WHERE clause in my query to show filtered results.
My Problem is: These filters require not only a smple added WHERE clause, but a huge Query including multiple JOINs. The resulting Query has > 30 lines.
Later, there should also be a search function which would then also require this huge query. I wonder if this is a good practice or if I should add a "redundant" Database column to my database table where I compute the attribute I need for filtering on every update. With this column, I wouldnt have my huge query on different places over my project, but have a redundant column.
What do you think?
Greetings
As questioned, here the table structure/code. This is not the exact code, because there is also a revision system which makes it even more complex, but for understanding this is enough:
table submissions:
ID (primary)
(additionalColumns)
table reports:
ID (primary)
submissionID (reference to submission table)
(additionalColumns)
table report_objects:
reportID (reference to reports table, multiple report_object for one report)
table accounting:
ID (primary)
reportID (reference to reports table, multiple accountings for one report)
(additionalColumns)
table accounting_objects:
ID
accountingID (reference to accounting table, multiple accounting_object for one accounting)
(additionalColumns)
For a submission, one or multiple reports are being create with multiple objects to account (report_objects). For each report, I can create multiple accountings, where each accounting is for a few objects of the report. The accounted report_objects are stored in accounting_object
My query/filter checks, if each report_object of a submissionID is accounted (accounting_object exists) for one submissionID.