Databases
StackOverflow2013
Postsdbo
POProgramming pattern for filtering SQL database contents in several different ways
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POProgramming pattern for filtering SQL database contents in several different ways
    primarykey
    data
    text
    <p>I'm working on a project where we have a table in a SQL database which will potentially contain a relatively large number (hundreds of thousands) of rows.</p> <p>This project is written in PHP, and is using PDO to access my database. In this project, to implement "reports" and various things, I need to be able to select rows out of this table, filtering the data by different properties, depending on what context I'm going to be displaying the data in.</p> <p>I'm curious what a very well-performing design for this type of situation would be. I've thought of the following options for this, but I don't really like any of them:</p> <ul> <li>Write a separate query (i.e. API function) for each different filtering technique. This seems undesirable because I'll be duplicating 90% of the SQL in each function, and it's hard to maintain (e.g., if I need to alter / fix one of my queries, I now need to alter / fix ALL of them, instead of just one).</li> <li>Provide a single API function which retrieves an unfiltered list of rows, and then filter them in my application, instead of using SQL. This is undesirable because it's wasting a lot of effort processing rows my application doesn't care about.</li> <li>Write a single function with a single query, with various optional parameters which, if provided would alter the <code>WHERE</code> clause of the query. This is probably the best option I have so far, but I'm still not sure I like it because it would mean having a function with a really weird signature, and having a bunch of ternary statements to alter the <code>WHERE</code> clause depending on whether or not the arguments were given.</li> </ul> <p>Are there one or more generally accepted programming patterns for designing an API which provides this type of functionality?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCmdrMoozy
    1. USCmdrMoozy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POProgramming pattern for filtering SQL database contents in several different ways
      1. This table or related slice is empty.
      1. VTUpMod
    1. COwhy not write your filters as views. that way you simply point your app at the views, and can change the back-end filtering any way you want without affecting the consumers of the data.
      singulars
      1. POProgramming pattern for filtering SQL database contents in several different ways
      1. USMarc B
    2. COI'm not sure how that is functionally different from writing a separate API function for each filtering technique, since at the end of the day I have a totally separate query for each different case, which seems to not be very maintainable.
      singulars
      1. POProgramming pattern for filtering SQL database contents in several different ways
      1. USCmdrMoozy
    3. COMaybe some example queries so we can see the similarities / differences, since 90% is the same?
      singulars
      1. POProgramming pattern for filtering SQL database contents in several different ways
      1. USAbraCadaver
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload