Please take a look at the attached screenshot.
At note #0: can we just write Select * or Select posts.ID instead of posts.* ?
At note #1: Can we remove this statement without breaking the query ?
At note #2.1 & #2.2: they are the same, why do they duplicate the same code and put Or between them? I don't understand.
At note #3: I don't know why the last condition statement are put between (( and )) but the upper one are not.
Thank you.
It looks like this query has been modified many times and therefore some of it is redundant. You could do SELECT * but that will return all fields and not just the posts field. You can remove WHERE 1=1 and make it WHERE (".table....) and you only need 2.1 and not 2.2. Lastly, you don't need 2 sets of brackets for #3.
The OR between 2.1 and 2.2 was probably there because they probably originally had different things in them but the differences were then removed thus making it no longer required. That's why you can get rid of one.
Originally the WHERE 1=1 was probably a different thing and instead was replace to WHERE 1=1 to ensure the query still runs without having to modify the structure. The whole query really does look like it's been changed a lot, so it's best to just simplify it so it's easier to change going forward. All those changes you suggested are correct.
Answer to your queries:
note #0:
If you want to get all the columns from the tables you are joining then you should use select * and if you just want the columns of particular table then select post.* or select postmeta.* or you can also select specific columns if required as select post.col1,postmeta.col1 ....etc
note #1:
Yeah..you can remove the statement where 1=1 because this statement always return true, so no need to put it there.
note #2.1 & #2.2:
There is no need of OR between notes 2.1 and 2.2 as both conditions are exactly same, so if one of them gets true or false other will be the same. so finally it will return same value if you just use single condition without using OR between them.
note #3:
There is no need of those brackets(note 3) as you don't have multiple conditions to put there. you just simply remove it.
Note #0: * will get you all the columns, but it is recommended to write the required columns only. This may lead to better performance, as in most cases you do not require all the columns. Also, when table schema is changed (e.g. a column is added), there is chance that it is fetched uselessly.
Note #1: Yes, it can be removed. However, some programmers put it (especially when using dynamic queries) just to be able to have all conditions defined like this: AND {condition}. This looks better, as all conditions are defined homogeneously and it should have no impact on performance.
Note #2: X OR X equals X, so it is recommended to remove it (DRY).
Note #3: The last condition can be defined without the parenthesis. Some programmers put parenthesis for conditions, just to be sure they do not face precedence problems. It is recommended to use parenthesis homogeneously (either use them for all conditions or no conditions). However, keep in mind that AND has higher precedence over OR and OR conditions must use parenthesis when combined with AND ones.
About other aspects of the query:
FROM and INNER JOIN are usually put on separate lines for increased readability (all keywords like SELECT, FROM, JOIN, WHERE etc. tend to be on the left part of the query).
PHP variable interpolation - PHP has a nice feature that allows to integrate variables directly in the string to avoid concatenation (and lots of double quotes). So, instead of
"FROM " . $table . "posts INNER JOIN " . $table . "postmeta"
you can write
"FROM {$table}posts INNER JOIN {$table}postmeta"
or something similar.
