3

The third line of each of these statements is exactly the same:

statement1

SELECT * FROM `uploads`
WHERE `uid` = :uid
AND `deleted` <> 1  AND `archived` <> 1

statement2

SELECT * FROM `uploads`
WHERE `folder_id` = :folder_id
AND `deleted` <> 1  AND `archived` <> 1

The third line is used in many other statements as well, lets say 30 different statements; in this contrived example it means "we get all files from an uploads table...that have not been deleted or archived". If in the future we need to add a third qualifier to it (e.g. AND uploadsuccess = 1) we would have to edit 30 different sql statements. Not DRY at all. How can we use DRY principles here, via SQL (MySQL in our case, if that is an important factor)?

Thanks for any help.

KayakinKoder
  • 3,243
  • 3
  • 23
  • 37

2 Answers2

3

I recommend using a VIEW:

CREATE OR REPLACE VIEW uploads_current AS
  SELECT * FROM `uploads
  WHERE `deleted` <> 1 AND `archived` <> 1;

Then you can query it with additional conditions:

  SELECT * FROM `uploads_current`
  WHERE `uid` = :uid;

You can redefine the view later:

CREATE OR REPLACE VIEW uploads_current AS
  SELECT * FROM `uploads
  WHERE `deleted` <> 1 AND `archived` <> 1 AND uploadsuccess = 1;

Then all code that queried uploads_current will implicitly include the same condition, with no code changes required.

Read more about VIEWs: https://dev.mysql.com/doc/refman/5.7/en/create-view.html

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
3

This is a common problem, we want all data to be stored, but 99% of cases we're filtering out a certain subset of data.

The solution I usually use is to create a view with the logic in, then query against that. For example:

CREATE VIEW `uploads_view` AS
SELECT * FROM `uploads`
WHERE `deleted` <> 1
AND `archived` <> `;

SELECT * FROM `uploads_view` WHERE `uid` = :uid;

SELECT * FROM `uploads_view` WHERE `folder_id` = :folder_id
e_i_pi
  • 4,590
  • 4
  • 27
  • 45